Trying to get the difference between two dates, one being a table filed, the other being today’s date. But the function Today is not recognized. I’ve tried Now, CDate and a few others. Any idea how to do this in Epicor 10?
E10 BAQ’s use SQL so to get “TODAY” in SQL you use GetDate() function
1 Like
Awesome! Thanks for the info!
@josecgomez if you don’t mind, I used the above funtion to get the date diference of how old a invoice was. Now, I am wanting to use those numebrs to add the dollars of the ones in a specifiec date diference range.
An example:
Inv Date Inv Due Date $$ DateDiff
7/31/2017 494381 8/30/2017 57.90 8
7/31/2017 494382 8/30/2017 86.85 8
Wanting to do something like IIF(datediff between <0 but <=30, Sum the $$, IIF(datediff between 31 but <60, Sum the $$,
So far I got the first half of that working, I just can’t get the adding the rows together for just those that have been identified to fit in that range.
You can pretty easily use subqueries for this. Add a new subquery that adds together all invoices where the Dates fall within a certain range, then put that subquery as a calculated field.
Like this
See attached BAQ
INvcBal.baq (24.5 KB)
3 Likes
So, I imported your BAQ and for fun added another Subquery for over 30 days. And it totals every line the exact same number. Like it is adding all the invoices together for that range and using that number all the way through.
So, what am i doing wrong? Starting to feel stupid…
INvcBal.baq (24.5 KB)
The one I gave you had 120 and 60-90 working make sure your new one has the same criteria / properties etc. Just change the date range. NOtice there is a subquery criteria on each subquery
I got it working! Thanks!
Hi Guys, I’m trying to use the BAQ supplier list for due date from APInvHead. I tried follow the BAQ that has been provided from here but it came out empty. can anyone help? Below is my BAQ
MW_Supplier_Summsry.baq (74.8 KB)
Could you provide some more details?
You might want to create a new thread.