Use Text Box and Action tool to change date to extract data using SQL server

[sureslala]

Hello 
I have created a workflow that connects to my works database using Microsoft SQL Server OleDB. 

It retrieves data for a particular file name and then multiple different columns as shown below. Currently I have to change the date 22 times (or copy and paste) to run each time. I believe by using a 'Text Box' and 'Action Tool', I can enter a date in the 'Text Box' and it will update all the 'Dates' (highlighted yellow) in my SQL Editor so that my workflow runs for the date specified. 

Here's my workflow

I need help please configurating the Text box and or Action box as I can't get this to run with different dates..

Thanks

Sures

[binu_acs]

@sureslala in the action tool, only replace the date 20251125

[caltang]

Click on the action tool and configure there by selecting the line of code, then click on replace text, and key in the text of the date in your code. Please note that the action tool will literally find what you write and replace it with whatever you input.

Also, your Text Box's display box is more for you to put it like a question -> "What date would you like...?" etc., then the real change happens when it becomes an analytic app / macro.

[sureslala]

Thank you Binu, Calvin, I  now have a better idea now on how the 'Action Tool' works but I still cannot get it to update the date part of my SQL formula to work.  

I've done the following

1. Highlighted 'File-value=*odb'

2. At bottom of box, ticked 'Replace a specific string:'

3. Typed in [MxGTSPlatoPLA_LN_20251125] (the text stays the same, I just want to be able to change the date)

4. What's the 'wand' symbol next to run do? I've run this, then 'RUN' but it doesn't make a difference?  

Can you advise what I'm doing wrong?
Thanks

Sures

[sureslala]

Thank you Binu, Calvin, I  now have a better idea now on how the 'Action Tool' works but I still cannot get it to update the date part of my SQL formula to work.  

I've done the following

1. Highlighted 'File-value=*odb'

2. At bottom of box, ticked 'Replace a specific string:'

3. Typed in [MxGTSPlatoPLA_LN_20251125] (the text stays the same, I just want to be able to change the date)

4. What's the 'wand' symbol next to run do? I've run this, then 'RUN' but it doesn't make a difference?  

Can you advise what I'm doing wrong?
Thanks

Sures

[binu_acs]

@sureslala in the replace string section put only the date  20251125 and remove the rest of the string , because only the change is in your query is the date part , rest remains same, once you you update the workflow try to run in debug mode then you can see how the text tool entry reflecting in the workflow

[sureslala]

Hi Binu
Its worked on the debug workflow but I'm not entirely sure how I update or make it work on my original workflow? Data date still hasn't changed on original workflow. Any advise yo can give? i'll continue to investigate also, althugh tomor is buys day so will be later in week.

Thanks

Sures

[binu_acs]

@sureslala If it is working in debug mode, then the workflow should work with this change. If you are still facing the issue, could you please share a few more screenshots of what you did? This will help me better understand the problem.

[sureslala]

Hi Binu

Apologies for the delay in getting back. I believe you have to run the Analytics button ( next to the run button) first?

My original SQL code has date '20251125' which I'm trying to change to '20251121'. You can see below it's changed in the output log after running Analytics. 

My Text Box

My Acton Tool

My SQL Config

 

After pressing 'Run' the SQL still brings back data for 20251125 and not 20251121

Thanks

Sures

[binu_acs]

@sureslala , your changes are correct. You should run your workflow as an analytical app, not hit the run button. If you hit the run button, it will take the actual date