Transferring E9 BAQ's to E10

eberliron · October 22, 2018, 2:57pm

We’re in the process of upgrading from E9 to E10 and I have a question about BAQ’s.

In E9, on the phrase build > diagram view tab of the BAQ Designer you could right click on the table and select “Query Item” with the default option of “each” already set, or choose “first” or “last” record.

For instance, I have a BAQ that includes the Customer and InvcHead tables and on the InvcHead table the query item was set to last, so we would only see the last (most recent) invoice from each customer.

I don’t see a way to do that in E10. Can anyone explain how I would replicate this functionality in E10?

josecgomez · October 22, 2018, 2:59pm

in E10 BAQ’s is just SQL Queries. You need to figure out how to do this in plain SQL. Probably a sub-query with some criteria

eberliron · October 22, 2018, 3:51pm

that’s what i’m here to figure out

josephmoeller · October 22, 2018, 7:10pm

Hello, Liz. There are a few ways to implement the first/last type of query logic seen in progress. I believe the most popular is to do use a subquery. Here’s a simple example of “LAST” implemented to find the LAST OrderNum in the OrderHed table in each company:

SELECT h.Company, h.OrderNum FROM Erp.OrderHed h WHERE h.OrderNum = (SELECT MAX(OrderNum) FROM Erp.OrderHed WHERE Company = h.Company)

EDIT:
For advanced SQL users looking to get every oz of performance out of their systems, a few years ago at PASS Summit, I learned that in most cases CROSS APPLY outperforms MAX subqueries in the SQL Engine. for instance, this query should be quite superior to get the max ordernum from each company:
SELECT c.Company, x.OrderNum FROM Erp.Company c CROSS APPLY (SELECT TOP 1 Company, OrderNum FROM Erp.OrderHed WHERE Company = c.Company ORDER BY Company, OrderNum DESC) x
I’m thinking I might make a PSA on this topic at some point.

Mark_Wonsil · October 23, 2018, 2:53am

Another way is from @Banderson and @knash:

First create an inner-subquery called CustomerInvoices. Start by dragging Invhead to the canvas. Then select your fields:

add a calculated field called RowNumber:

This will create an integer where the row_number goes from 1 to X for each customer in descending invoice date sequence. You could use Invoice Number if you want chronological sequence but if you trust your dates…

Create a new Top Level Query. Drag the Customer table to the canvas and then click on the subquery button and drag the CustomerInvoices query to the canvas.

And link by Company and CustNum. Now, you have all of the invoices for each customer sorted by desc Invoice Date and you only want the first one. Great. The first one will always have the RowNumber = 1. Set a criteria on that table:

Add your Display fields and you have your BAQ. Like Joseph said, there are other ways that are faster. But this should get you going. Enjoy E10!

Mark W.