When I uplift the dashboard, it comes into Kinetic Application Studio
I think the issue is that the value is incorrect. The value needs to be one year ago from today’s date.
I can make it work with Calculated_CacLastUsedDate < ‘11/15/2023’
Now I am trying to replace the ‘11/15/2023’ with an expression instead. I have tried:
dateadd(year, -1, Constants.Today))
Date.getFullYear() - 1
Date.getDate() - 365
currentDate.getFullYear()-1
Please suggest the correct value for 1 year ago.
Thank you,
Heide Warren
#_'(new Date(new Date().getFullYear() - 1, new Date().getMonth(), new Date().getDate())).toLocaleDateString()'_#
** Not sure that will work in the value field, however.
You should put that expression in a TransView variable and then use that TransView variable in your where list.
@hmwillett
Thank you very much for the support. I have read many of your entries on Application Studio and they have helped me resolve my issues. This case is just one more example where your advice is helping me solve or work around issues.
You were correct and the expression did not work in the value field. Your screen shot and TransView suggestion was very valuable.
If I understand correctly, I created a TransView variable called OneYearAgo.
Then I filter on the TransView.OneYearAgo
If the value is TransView.OneYearAgo then it returns no records.
If the value is ??{TransView.OneYearAgo} then it returns all rows.
How do I check that I actually was able to set the TransView.OneYearAgo variable and identify its value? My best guess is that I have created the event incorrectly and I am not setting my date value.
I would be grateful for additional advice.
@josecgomez
This BAQ is used by multiple groups of people. Those controlling inventory, with the goal of purging inventory over a year old that has no foreseeable demand, want to see the data filtered to show only the old and unused parts. Finance wants to know about the current inventory being used and the value of that inventory. In the past we have used one common BAQ, and filtered the data in dashboards for the needs of the different users.
I appreciate all your help. Both of you are amazing Epicor users and programmers. I can only hope to keep gaining bits of your knowledge, a small fraction of your skills. And thank you for creating and maintaining this user group. It is great and hugely helpful.
If I set the value in the Where List to ‘11/18/2023’ (a string) then the filter works on the Where List.
I created a TransView.OneYearAgo and made the TransView a DateTime datatype. The BAQ has a data format. I used the expression to define the OneYearAgo variable and it is returning
That is a match for what was working. But the TransView variable returns no records.
I have tried to change the TransView variable to a string instead of DateTime. I have tried {} and or () brackets. Based on the OneYearAgo variable coming back as a match for what works, I am guessing that my error has something to do with format and syntax.
Anymore suggestions on formatting and syntax of the TransView variable?
I want to compare what is working to what is not working.
When I just use the date in the Where List then I get the correct rows returned.
This is the working Where List with ‘11/19/2023’
This is the payload that it returns
I realize you want to use this BAQ across multiple dashboards with one needing this one-year-ago filter. However, like Jose asked, why not just make a calculated field:
(CASE WHEN Calculated_CacLastUsedDate < (DATEADD(year, -1, GetDate())) THEN 1 ELSE 0 END)
in the BAQ and use that in the dashboard where you need it?