Dynamic Named Queries

Ignition
, , ,
1

I have a table consisting of an ItemNumber and Quantity.
For example.

  1. ItemNumber 1234 and Qty 10.
  2. ItemNumber 1235 and Qty 5.

This table can be edited in Perspective. I wrote a script that moves the new ItemNumber into the same table.
For example.

  1. ItemNumber 1234 and Qty 10.
  2. ItemNumber 1256 and Qty 5.

I have another table that will show where these Items come from.

I know I can use the item number as the parameter for the Named Query. Can I take it further using variables in that table so it would search for both ItemNumbers in 1 trigger?

2

Yes, why not ?
Just make your named query take 2 parameters instead of one.

Or is there something Iā€™m missing ?

1 Like
3

Or if you want it dynamic, you could use a query string type param and use:

WHERE ItemNumber IN ({ItemNumberCSV})

where ItemNumberCSV is something like

'1234,2345,9374'
4

How do I get the ItemNumbers from the Table to the SQL parameter?

If I am grabbing this from a table that already exists, how do I get the ItemNumbers into the parameters for the Named Query? These need to be dynamic.

Sometimes the first table might have 2 items, sometimes it may have 15.

5

You need to use a script transform on the table's data, and produce the itemNumbersCsv to then pass into the named query queryString param

Eg something like

ds = value
ret = [] 
for row in range(ds.getRowCount()):
   ret.append(ds.getValueAt(row, "ItemNumber")) 

return ','.join(ret)
6

Nick, what's the benefit of ds = value? I would have tried to use range(value.getRowCount()) directly (but I only know enough Python to get by).

7

No benefit, other than understanding :slight_smile: especially on here where I can't show that value is coming from the binding of the table data. But I would normally just use value as well in my own code

1 Like