Hi all, I am trying to get the sum of lines from multiple quotes in a BAQ. I have a column that shows the sum of quotes entered per user between two dates. Next to that, I want to show the sum of all the lines in those quotes. I used a calculated field to get the sum of quotes, but I am struggling to do the same with the lines. Can anyone offer any help / advice?
Try a subquery where you sum the lines, then group by quote and user. Then join it at the top level, presumably on UserID or whatever is appropriate.
Hi @Matthew_Morgan Thanks for your reply. I got the number of quotes per user using a subquery, I tried the same method to get the total quantity of lines but as soon as I join the QuoteDtl to the QuoteHead and group by quote, I get a row for each quote. I cannot find a field for userID on the QuoteDtl to group by.
@Adrian_Mepham It appears that I misunderstood: you want the count of lines per user, correct? If so, then you’ll probably have to stack a couple of subqueries. First, get the count of lines for each quote (or even use MAX(QuoteLine) per Quote Number. Then join to QuoteHead and take the sum of the MAX values grouped by UserID.
Hi Mathew, it took some head scratching and a bit of swearing but I finally got it working. Thanks for your help with this.