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

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.

SQL Editor.jpg

Here's my workflow

PLA Workflow.jpg

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

Developer Tools

Server

In Database

In-Database

Accepted answers

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

Text box.jpg

My Acton Tool

Acton Tool.jpg

My SQL Config

Input SQL Query.jpg

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

sureslala

Hi Binu

Thanks managed to crack it, could see my Analytic tool was generating the data however I couldn't extract it. After doing some research, noticed on 'Design Interface' i had to select 'Browse' I could then see my updated data and export it!

Thanks again for your assistance!

Sures

All comments

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?

Annotation 2025-11-30 163753.jpg

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

Text box.jpg

My Acton Tool

Acton Tool.jpg

My SQL Config

Input SQL Query.jpg

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

sureslala

Hi Binu
Cracked it! thank you. I could see the Analytical Action tool was changing the data however couldn't see/extract the data. I researched and saw that on 'Interface Designer' I had to select 'Browse. I then could see the changed data and export it to csv file.

Thank you very much for your help!

Sures

sureslala

Hi Binu

Thanks again for your assistance!

Sures

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335