I am trying to make a BAQ to get the latest quote that was entered for each part for a certain customer, and I am having trouble getting it working. I searched this site, and I found some threads that seemed similar, but none of the solutions there solved my issue.
I managed to get the baq to throw out all the parts for the customer, along with the date that they were quoted, but it is showing all quotes, not just the latest. At first, I tried to just do result set row distinct, then top, then top distinct, but none of those removed the old quotes.
Then I tried making a subquery, and that did not seem to work either. The query has QuoteHed.QuoteNum, .Company, .CustNum, and .DateQuoted all as group by.
You are on the right track. Create a SubQuery with QuoteDtl and QuoteHed with the fields, QuoteDtl.Company, QuoteHed.CustNum, QuoteDtl.QuoteNum, QuoteDtl.PartNum, and QuoteHed.DateQuoted. For your Top Level, use your SubQuery as the data source and GroupBy fields, Company, Cust Num, and Part Number, and create a Calculated field that is max(SubQuery1.QuoteHed_DateQuoted). This should give you what you need.
I am trying this, and it does not seem to be working. I am grouping by only the fields you say, but I get the error saying that quotenum is not contained in either an aggregate function or the group by clause. If I add that one to the group by, then it says the same thing for DateQuoted. Grouping by both of those does allow me to make the calculated field max(SubQuery2.QuoteHed_DateQuoted) and add it to my baq. However, this still shows all of the old quotes that we have.
You can’t show the field that you are trying to get the max of.
Take a second to breathe and think about what you are doing here.
The group by will group all of the rows that have the same values in all of the rows that are shown. If you show every date, then that row is unique, and it won’t be grouped, hence why you are getting all of them.
Also, another technique, depending on what you are actually trying to do here, is to use a windowing function, which will not require a subquery.
max( quote.date) over (partition by PartNum, Customer, whatever else you want to chop it up by, order by quote.date)
What this will do is if you have grid of all of your quotes, will look at the windows and give you an aggregate value. It will still keep all of the rows, but you can get the max.
