I need to create a BAQ that will return only the last (most recent) sales order for each customer and create a formula field that tells me how many days ago that was.
In E9 there was the first/last criteria. I’ve been trying to play around with subqueries w/o much success.
Anyone know how to do this in E10?
Best answer by mwilson
View original
This is off the top of my head. Formulas may not be accurate
Add OrderHed. Group by CustNum, add a calculated field for Max (OrderHed.OrderNum). Test to make sure you get the right data. Then make this a subquery.
On a new top-level query, add your subquery, join to Customer (I assume you’ll need customer info) and OrderHed. Add a calculated field that is DateDiff (‘d’, OrderHed.OrderDate, GetDate()) for your number of days since last order.
Just as a quick reply as I’m not in front of a system:
Create a sub query with Max function on the Sales Order Num*, add in the Customer ID/Name, Order Date and group by Customer ID/Name, Order Date
In the top query add a calculated field on Order Date to calculate number of days.
* this will only work if Order Numbers are created in one range. Alternatively use Top 1 and Max on Date in the sub query and then order by Date (there are some limitations on sorting sub queries in SQL so this may not work)
I will monitor the post let me know if you need anymore helps/suggestions
Tim
dumb question - how do I “group by”?
While I’m waiting on something to run. . .
Here’s an example for your subquery:
And hook it up something like this:
See if that helps.
Joe
not sure how I missed that big, bold group by checkbox… thanks!
Welcome!
For anyone after who needs this info - here’s the calculated field for how many days since last order
Here’s another approach:
Create a subquery using OrderHed.
Add a calculated field called (say) Latest as string
and deineit as:
Case
when Row_Number() OVER (Partition By OrderHed.CustNum
ORDER BY OrderHed.OrderDate DESC)=1
then 'YES'
else 'NO'
end
And also include Company, CustNum and OrderDate and OrderNum
Then in Top level add Customer table , bring in Subqery, link on Company and CustNum
And in the subquery filter the results where Latest=’YES’
This works well Ascending or descending to find the first or last value
While I’m waiting on something to run. . .
Here’s an example for your subquery:
And hook it up something like this:
See if that helps.
Joe
Is there a way to get the OrderHed.OrderNum field onto the SubQuery w/o breaking everything when you do it this way? I put this in a Dash for my users and would like them to be able to right click in to the order.
I suppose I could add a second BAQ to the Dash that is linked to the CustID and shows all the sales orders for them when that row is clicked as a work around. That might even be better.
You’ve probably solved this problem already but allow me to refer you to this thread where I describe the steps to make a single-level BAQ that does a synthetic first or last. I just constructed one to give a list of all customers who have made an order in the last three years, and the most recent order for each customer. This is the table construction:
I did a presentation on how this works at the Texas EUG, and that file is linked to this posting. In the early days of Epicor 10.0, before we learned how to do multi-level queries, it was a quick and dirty way of getting Epicor to give us first or last.
Customer Table Criterion
OrderHed and OrderHedNext Criteria
Relations, Customer to OrderHed
Relations, OrderHed to OrderHedNext
Subquery Criterion
Results
The three-year limit was only to get it working in less than 30 seconds in the designer; YMMV. For a dashboard, you could increase the history period, or remove the two criteria altogether to go back to the beginning of time.
HTH
…….Monty.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.