Column to Parameter

1

Hi,

Is it possible to use a column as a list parameter?

We use 2 ERP systems that do not talk to each other but the data is the same.

I have Query1 from ERP1 that returns a list of items. I would like to pass a param to ERP2 in the SQL WHERE clause to only return detailed data relating to the items in Query1.

Something like the below.

Query 1 -
SELECT Item FROM ERP1 WHERE Sold = True

Query2 -
SELECT * FROM ERP2 WHERE Item IN ({ListOfItemsFromQuery1})

2

Hello @jsaunders and welcome to the community!

If Query2 is actually as simple as in your post then you can just use the “Select matching database rows” action.

Otherwise, if you have to use the Query builder to specify other parts of the query, you can create an expressions in the Query builder with the IN condition and a parameter and then use that expression in the visual mode of the condition builder.

In order to assign column values to a parameter, you have to:

  • convert values in the column to a valid SQL literals
  • aggregate that column in the concatenate mode using comma as a separator
  • move your “Import from database” action to another module and use the “Iterate” action to call that module passing aggregated column to the parameter of the called module.

Here is an example project:
InConditionWithColumnValues.zip (238.3 KB)

image
image.png1470×712 78.8 KB

It executes both queries on the same table, but workflow for different tables is the same.