BAQ Help- Summarized tables (V8.03)


(system) #1

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