User-Input Date for Reporting

I'm trying to create an analytic app where the user is able to select a date and then the data pulled from the database is "as of" that date. For example, if I go into the current JDE database we have, I can set the effective date as 12/31/2020 and then view the data as of that date.

A little background for the process, I am trying to generate an exception report for a specific indicator we have in a JDE datatable. We want to be able to run the workflow monthly in order to see if any of the exceptions have occurred, but we also want to be able to go to past months and see what the data looked like then, to ensure that any changes actually made sense.

In my workflow, I'm pulling from a database and then selecting only the most recent entry for a specific entity by using descending date + unique tool. I'm having issues figuring out how to let the user enter the date (12/31/2020 for example) and then telling the workflow to only incorporate data for 12/31/2020 or prior. From there I believe I can simply order all dates and then select unique values, but I'm stuck on updating an end date filter based on what the user selects.

Thanks in advance!

Date Time

Apps

Accepted answers

Joe_Lipski

Sorry for the slow response @jeffjose33

I've updated the workflow here and included screenshots of each part.

Filter: The date is a PLACEHOLDER and will be replaced by the action tool

Action Tool:

The date in the 'replace a specific string' box needs to match the date in the step above as this placeholder is what is being removed:

Output from Filter: You can see below the data is only showing me data after the 1st of November!

You could make it >= in the filter if you want

Please let me know if this helps?

Interface date filter.yxwz

All comments

Joe_Lipski

Hi @jeffjose33In your screenshot above you will either need to add a filter tool or an in database filter tool.

Your action tool after the date interface tool will then connect into the lightning bolt anchor of the filter/in-db filter tool and this should be pointing to a field which is 'end date'

I've attached an example. Make sure you check out the configuration of the action tool.

Please let me know if this helps, or if I've misinterpreted!

Interface date filter.yxwz

jeffjose33

Thanks @joe_lipski, I must've accidentally removed the filter tool before my screenshot.

My question is really revolving around what expression I put in the filter. So assuming I have a field called End_Date that I want to manipulate, and I set up the same interface tools you've shown to have the user select a date, what kind of expression would I use to say "Filter any end date value that is prior to 12/31/20 as TRUE and the remaining FALSE". So that way the records I have will only be related to those prior to that date. Thanks

jeffjose33

Thanks @joe_lipski, I must've accidentally removed the filter tool before my screenshot.

Joe_Lipski

Sorry for the slow response @jeffjose33

I've updated the workflow here and included screenshots of each part.

Filter: The date is a PLACEHOLDER and will be replaced by the action tool

Action Tool:

The date in the 'replace a specific string' box needs to match the date in the step above as this placeholder is what is being removed:

Output from Filter: You can see below the data is only showing me data after the 1st of November!

You could make it >= in the filter if you want

Please let me know if this helps?

Interface date filter.yxwz

jeffjose33

Joe, thank you so much!! I wasn't understanding that the filter was acting like a placeholder and so I was trying to somehow reference the user input without understanding it was being replaced by the Action tool.

Joe_Lipski

Pleased that I can help!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703