Date Value of 1 year ago in Application Studio

We are a Public Cloud user and on version 2024.2.3 in Pilot

Goal: Uplift dashboard and filter date less than one year ago

The Classic dashboard filtered on Calculated_CacLastUsedDate < Calculated_OneYearAge


When I uplift the dashboard, it comes into Kinetic Application Studio
image

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
image

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.

image

2 Likes

While this is possible seems like a hard coded filter just add it to the baq

4 Likes

Always Sunny Shut Up GIF

3 Likes

@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 created a New Event under General. The new event has a hook before the Form_OnLoad. After the event trigger, I have the row update.


Then I filter on the TransView.OneYearAgo
image
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.

Heide Warren

All of that looks copasetic.

Check out this for how to debug: How To: Debugging Kinetic (Browser) - Experts’ Corner - Epicor User Help Forum

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?

Look at the payload on the network tab of Developer Tools so you can see exactly what it’s sending to the BAQ.

You should see something like this, but with a Where Clause in there.

1 Like

The where list in application studio looks like this:
image

The payload for that where list results in:


This gives me no records returned.

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’
image
This is the payload that it returns

I have tried to get rid of the ’ in the TransView expression, but it results in a decimal value for OneYearAgo and fails to return rows.

Hmmm. I am at a lose. This seems so simple a task (filter a dashboard on a date one year ago) but yet I can not reach the goal. Humbling.

Change it to ??{TransView.OneYearAgo} and look at that same network call.

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?

In the Where List I made the value ??{TransView.OneYearAgo}
It returns all rows and this is the Payload


I can’t find the Where statement any longer in the Payload. The screen shot is the entire Payload.

Try it with single quotes wrapping it:

'??{TransView.OneYearAgo}'

I put single quotes wrapping it.
image

It worked. It returns the correct rows and the where statement is back in the Payload.


Notice that the working Payload has replaced transview variable with the date.

Way to go!!! High five!

3 Likes