Automatically modify SQL statment daily to find specific ID#s

[bleu]

I have a problem and I've seen some solutions in community but I can't seem to follow them.

It's best to use a simple example:

I have another workflow that outputs data into an Excel file. This data will change every single day. (The sheet gets overwritten) Some days there could be 3 ID numbers, other days it could be 10.

ID #

12345678

91011121

14151617

I have two database with millions of transactions that will contain ID numbers on different days. The database puts a 0 before each ID, which can easily be fixed with a little formula so the data matches exactly.

12/1/20  012345678

1/19/20  012345678

3/2/21    012345678

9/5/20     091011121

6/25/20   091011121

2/9/21     014151617

3/6/21     014151617

I only want to find the transactions that have ID numbers from the Excel spreadsheet.

My current workflow takes too long because the workflow is finding every single transaction in both databases and then it finds the transactions with the ID numbers. 

Current workflow example:

I want the SQL statement to immediatlely know which ID numbers to search for first. It will run much faster. And I want to create an automated process that updates the SQL statement daily based on the output ID numbers.

I'm sure I have to use the Dynamic input tool but I'm stuck. How do I get the SQL statement to change automatically based on the ID number and to realize that each day there will be a different number of ID's listed?

Hope this makes sense.

[kaosrules88]

I would use the Dynamic Input to load your SQL and modify the where clause so your IDNumber = "XXXXXXXXXXX" or some string that is unique to the query.

After you add your query you check the modify SQL button and add a "Replace a specific string".

If you have a formula tool ahead of the Dynamic Input that determines the ID number you want based on the day of the week, it will update the query with the correct ID Number each day.

-J

[mbarone]

Hi @bleu , you are correct, the Dynamic Input tool is the way to go.  You will "feed" your list of IDs "into" the Dynamic Input tool (you'll need 2 of them - one for each database).  You'll want to use the "Modify SQL Query" option.  I'll put together a quick sample for you.

[mbarone · Accepted solution]

Here you go.  You'll have to modify the dynamic input tool obviously,  but this should give you the idea.

[bleu · Accepted solution]

You've got to be kidding me. That was so simple!

Thank you mbarone!

[mbarone · Accepted solution]

No problem!