I'm trying to make a BAQ (eventual for a dashboard) of customers with columns for most recent activity (last: order date, quote date, and invoice date).
My BAQ consists of an outer join between the Customer and OrderHed tables. Using an outer join because I want records from Customer even if no OrderHed records are found.
I made the OrderHed table a summarized table, I so I won't get a row for every order. But fields from a summarized table have to be in a calculated field, and the there's no MAX() function. Only TOTAL(), AVG() and COUNT().
Next I tried making the OrderHed non-summarized and changing the selection to LAST. Query phrase:
for each Customer where ( Customer.EstDate >= 01/01/2015 OR not(Customer.ShortChar01 = '')) no-lock ,
last OrderHed outer-join
where (Customer.Company = OrderHed.Company and Customer.CustNum = OrderHed.CustNum) no-lock
by Customer.CustNum by OrderHed.OrderDate Desc.
But this doesn't retrieve the last OrderHed, Just for kicks and giggles, I changed it to FIRST, and that doesn't grab the first OrderHed either.
My end goal is to have it also return the last QuoteHed.DateQuoted, and InvcHead.InvoiceDate too. But I cant even get it to work with just the OrderHed.
Since this will end up in a Dashbord, is there a better way to make the dashboard do this summarization? Maybe 4 BAQ's (one for Customers, and 3 for last OrderDate by Custnum, DateQuoted by CustNum, etc ...) .
Calvin