Not sure how to go about generating a BAQ (or Dash consisteing of BAQ(s))to show only 1st time quotes for customers. Anyone have any ideas?
Best answer by fred
View original
Userlevel 3
If you’re looking for customers who have only been quoted one time, do a subquery for a count of quotes grouped by custnum, join that to customer where count of the subquery = 1.
If you’re looking for the 1st quote on every customer, do a subquery getting the min quote # group by custnum, join that to customer and quote on the cust num and quote num.
Let me know if you’re looking for something else.
I am looking for customers who’ve only been quoted once. I know how to create a subquery, but I do not know how to go about grouping by custnum or applying count parameters..
Userlevel 3
Cool. Hopefully this makes it somewhat easy.
Create a subquery for QuoteHead. Display CustNum and click the “group by” checkbox on the display fields. Now add a calculated field. For the formula, choose the “count” aggregate function.
This will get you the list of CustNums and the number of quotes each CustNum has in the system. You can then do a subquery criteria where the count = 1 and that will be customers who have only been quoted once. If you need customer information, you can join Customer to the subquery and pull back whatever you need.
I’m able to get the list of the CustNum and Count of QuoteNum’s, but I can’t bring any additional fields in or I get an error. I can’t filter with the subquery criteria or I get an error. I can join the customer table either directly or as an innersubq, but I can’t bring any of the fields in or I get an error. I must be doing something wrong.
Userlevel 3
Now that you have the one query, set it’s type to be a subquery. Then create a new top level query, put the customer table and your subquery on the canvas and join them. Then you can get the additional customer information and set the query criteria.
When you do a “group”, all the fields have to be part of the group, or be calculated fields. That’s why you get the error when you try to bring in other fields. By having a “parent” query, you get around that.
Ok - I thought I tried doing that already, with Customer as the TopLevel and QuoteHed the InnerSub, but I will double check. Are you also saying that if I want to include more fields off of the QuoteHead, I just have to bring turn them into calc fields?
Userlevel 3
If you want fields from QuoteHed, then in the inner subquery, add a calculated field for min (quotenum). Then in the TopLevel query, join the subquery to Customer on CustNum and join the subquery to QuoteHed on QuoteNum and you’ll have customer and quote information.
Great - thank you for all of your help! Much appreciated
Userlevel 4
Dear Liz,
Although the query/subquery method of finding first or last transaction works and is straightforward, there is another method that doesn’t require subqueries. In this discussion which links to another one, we give the steps to create a synthetic first or last query.
HTH
..Monty.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.