Input a item number into a Workflow running from a ODBC Database Query

[Karl_Spratt]

Hi Community,

Looking for some help please, I have a Workflow which is running from an ODBC Database Query 

It's our R12 ERP back tables. I'm currently opening up the SQL Query an changing the item number / numbers one by one 

See attached, I wondering if there is a dynamic way to do this, where I don't keep having to open the table (fig2) and enter each item like in (fig3) 

If someone could be advise. 

TIA 

Karl  

[mbarone]

I can't tell by your screenshot - are you asking if you could "feed in" a list of values to your query and have the query run for each of those values (without having to manipulate the query)?

[ChrisTX]

Try the Dynamic Input tool.

Here's a link to the Tool Mastery article:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Dynamic-Input/ta-p/249...

Here's an example of how I use "#Placeholder" text in my SQL query, and use the Dynamic Input tool to replace the #Placeholder values with data from the incoming data stream:

Chris

[Karl_Spratt]

Hi Chris,

My Database is a ODBC D/base for R12, can I create this looked at Youtube videos saying it had to be OLED8 DB

See my setup, so what I'm trying to do is tell the Dynamic Input to search for say item B instead of Item A 

Can you assist here, I looked at this video but I'm lost 

Alteryx - Dynamic Input Tool - YouTube

Cheers,

Karl. 

[ChrisTX]

The Dynamic Input tool works just fine with an ODBC connection, updating a Where clause.

Start out with a regular Input Data tool.  Ensure that tool connects OK to your ODBC source and returns records when you use an IN clause in your Where clause (for your list of item numbers).

Then copy the data from your Input Data tool to the Dynamic Input tool, under the Edit button.

Here's an example where I used a Generic ODBC connection to connect to an Access database. The drop-down for "SQL Clause to Update" picks up the Where clause without any problem.

Chris

[Karl_Spratt]

rry Chris,

I can't get this I'm really sorry for being this stupid.

My SQL  is say for this item '39583752' and is working. but when I try to use dynamic imput to change the item to say Item 96719851 I'm getting an error, like the attached

Can you advise what I'm doing wrong, sorry for being a pain here. 

Cheers,

Karl.

MySQL is: 

select cnf_irebs_items.*,
cnf_irebs_items_attributes.*,
cnf_irebs_items.org as org1,
cnf_irebs_items.item_code as item_code1
from cnf.irebs_items cnf_irebs_items
inner join cnf.irebs_items_attributes cnf_irebs_items_attributes on cnf_irebs_items.organization_id = cnf_irebs_items_attributes.organization_id and cnf_irebs_items.inventory_item_id = cnf_irebs_items_attributes.inventory_item_id and cnf_irebs_items.organization_name = cnf_irebs_items_attributes.inventory_org_name
where cnf_irebs_items.item_code = '39583752'

[ChrisTX]

In the screenshot you sent, the Text to Replace should be 39583752

[rvenkatr7276]

My requirement is slightly different. I have a ODBC connection to a table which needs to query based on values available in my Excel. Could you suggest me a solution please. I can create a Dynamic input using the excel and brough to the workflow, not sure how to connect the Sql query to the dynamic input brought above.