# BAQ help with summarized tables **Category:** [Vista/Vantage 8](https://www.epiusers.help/c/vantage-8/8) **Created:** 2016-09-20 17:16 UTC **Views:** 3545 **Replies:** 17 **URL:** https://www.epiusers.help/t/baq-help-with-summarized-tables/35442 --- ## Post #1 by @ckrusen _Sorry for the Bump - looks like this got orphaned on the old board_ 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 --- ## Post #2 by @josecgomez 4GL has the MAXIMUM aggregate function you can use... can you try to use that? It has been a while since I tried this in 8.03 --- ## Post #3 by @ckrusen I'll get right on it and let you know ... --- ## Post #4 by @Vic Sure does, Jose. Calvin, see here: http://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dvref/maximum-function.html --- ## Post #5 by @Vic Oh, and keep the OrderHed table Summarized. --- ## Post #6 by @ckrusen The expression: Count(OrderHed.OrderNum) result type: Integer, Format: >>>,>>9 **tests okay.** Changing the expression MAXIMUM(OrderHed.OrderNum) gives me **"There is an error with the Calculated Filed OrderDate"** * ignore that the field is named OrderDate - I'm testing with OrderHed.OrderNum, tying just MAX(OrderHed.OrderNum) doesn't work either. Did I mention this is in a BAQ? Thanks for your help so far. Calvin --- ## Post #7 by @ckrusen Vic - That provides the maximum of a set of values. I want maximum value from a set of records. And that help appears to be for OE 11. I should have stated that we're on OE 10.1B And yes I have the OrderHed table summarized. Else the COUNT(OrderHed.OrderNum) wouldn't have worked. Calvin --- ## Post #8 by @josecgomez If MAX / MAXIMUM doesn’t help I think you are stuck for now with BAQ’s… you might be able to hack something together using a BPM. I’ve done something like this before where we “summarize” some data using a BPM or an external process and put it in a UD Table. Have you looked at executive queries? I can’t remember if these are available in 8.03 but they have better “summarization” capabilities -Jose --- ## Post #9 by @Vic Yes, my mistake. In Vantage 8.03 BAQ's, in the Calculated Fields you are limited to TOTAL, AVG and COUNT as you first stated. You cannot achieve what you want to do in a BAQ query alone in version 8.03. My mind was in BPM land with somewhat full access to 4GL functions. In thinking more about it, you may have to just suck it up and make the BAQ with non-summarized data and pump that into a Crystal Report and use CR's Max() function. If they don't want to print the report, they can always preview it and it may suffice as a quasi-dashboard for you. --- ## Post #10 by @tmat We are on V8.03 and have this query working for the latest invoice for a customer: for each Customer no-lock , last InvcHead where (Customer.Company = InvcHead.Company and Customer.CustNum = InvcHead.CustNum) no-lock . Can you explain why your query doesn't work (what record is it retrieving as opposed to the one you actually want)? I know it wouldn't in our system as I would want the furthest OrderRel.DueDate and that wouldn't necessarily be the latest OrderHed raised. --- ## Post #11 by @ckrusen Here's any example: (I added the condition of CustID='WIL96' just to limit the results for this example. The same results happen w/o the CustID limit) BAQ with Phrase: `for each Customer where ( Customer.CustID = 'WIL96') no-lock , each OrderHed where (Customer.Company = OrderHed.Company and Customer.CustNum = OrderHed.CustNum) no-lock .` returns: BAQ with Phrase: `for each Customer where ( Customer.CustID = 'WIL96') no-lock , last OrderHed where (Customer.Company = OrderHed.Company and Customer.CustNum = OrderHed.CustNum) no-lock .` Returns: OrderNum 2120133 is obviously not the last by this customer And the reason for going by orderNum or OrderHed.OrderDate, was to show when the last time an order was entered for the customer. All of this was to try and find only the customers with recent activity, so that we could port them to E10. But now I'm mostly curious as to why Order 2120133 is the "last" record (for that customer) and not the 2120133. Is it because 2120133 might have been the last one edited? Calvin --- ## Post #12 by @bw2868bond Can you include the ChangeDate in your results to see if it is using the ChangeDate Index……. --- ## Post #13 by @ckrusen Here's the BAQ output (for EACH) using LAST, returns 2120133 - the last in the list but not the last Order created or changed using FIRST, returns 2120170 - the first in the list but not the first Order created or changed I'll just chalk it up as a learning experience, and not to rely on FIRST and LAST in BAQ's. --- ## Post #14 by @ckrusen FWIW ... I removed the Customer table and just select from the OrderHed table (with CustNum set to limit data) Returns OrderHed records in the same wrong/unexpected order. --- ## Post #15 by @svareschi FWIW I have had the same unexpected / inaccurate results with using FIRST and LAST in version 9.05. I have found I cannot reliably use those aggregates as they rarely return what I need. --- ## Post #16 by @bw2868bond Well – it is probably using one of these indexes ☺ Indexes Index name Flags Fields Datatype OrderNum P U Company (+) character OrderNum (+) integer BTConNum Company (+) character CustNum (+) integer ShipToNum (+) character BTConNum (+) integer BTCustNum Company (+) character BTCustNum (+) integer ChangeIdx Company (+) character ChangeDate (+) date ChangeTime (+) integer ChangedBy (+) character CreditCard Company (+) character CardNumber (+) character CurrencyCode Company (+) character CurrencyCode (+) character CustOpen Company (+) character CustNum (+) integer OpenOrder (+) logical OrderNum (+) integer CustOrd Company (+) character CustNum (+) integer OrderNum (+) integer CustPO Company (+) character CustNum (+) integer OpenOrder (+) logical PONum (+) character HDCaseNum Company (+) character HDCaseNum (+) integer OrderNum (+) integer ICPOIdx Company (+) character ExtCompany (+) character ICPONum (+) integer Linked (+) logical OpenOrder Company (+) character OpenOrder (+) logical OrderNum (+) integer OpenPONum Company (+) character OpenOrder (+) logical PONum (+) character OrdDateordNum Company (+) character OrderDate (+) date OrderNum (+) integer OrderDate Company (+) character OrderDate (+) date PONum Company (+) character PONum (+) character PrcConNum Company (+) character CustNum (+) integer ShipToNum (+) character PrcConNum (+) integer SalesRepList W SalesRepList (+) character ShpConNum Company (+) character CustNum (+) integer ShipToNum (+) character ShpConNum (+) integer + = Ascending - = Descending P = Primary U = Unique I = Inactive W = Wordindex --- ## Post #17 by @Manasa Calvin, I know if I were in your shoes I would want to just try to figure this out, but is there a reason why you could not just do a data dump and use excel to fix your MAX and be done with it? Manasa --- ## Post #18 by @ckrusen A solution was found days ago (and marked as such). I'll do it in either Excel or CR. I'm just to damn curious to let things go. Especially if I thought it was me that was doing something wrong. The thread evolved into "Why doesn't LAST give you the last record expected?". If you have mod powers, please "close" this thread and put it to rest :slight_smile: --- ## Post #19 by @josecgomez --- **Canonical:** https://www.epiusers.help/t/baq-help-with-summarized-tables/35442 **Original content:** https://www.epiusers.help/t/baq-help-with-summarized-tables/35442