Populating a dropdown from a Query String

Ignition
1

I have a large table of Part Numbers, I have columns for machine models populated with Yes/No depending on whether they contain said Part Numbers. I would like to populate a dropdown with all the part numbers that a given model uses. I thought I could parameterize this with either a Value or QueryString but I’m missing something, mini table for example:

CREATE TABLE testModel (testModID INT IDENTITY NOT NULL, PartName varchar(10), Model1 varchar(5), Model2 varchar(5), Model3 varchar(5), Model4 varchar(5), Model5 varchar(5));

insert into testModel(PartName, Model1, Model2, Model3, Model4, Model5)
VALUES
('Pump 264', 'Yes', 'Yes', 'Yes', 'Yes', 'No'),
('Pump 300', 'No', 'No', 'No', 'No', 'Yes'),
('Valve 24', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes'),
('800 Lens', 'Yes', 'No', 'Yes', 'No', 'No'),
('Steel Fr', 'Yes', 'Yes', 'Yes', 'Yes', 'Yes'),
('Sil Seal', 'Yes', 'Yes', 'Yes', 'Yes', 'No');

Select PartName from testModel
WHERE Model1 = 'Yes'

I would like to replace Model1 from example with a parameter for any Model column. I have tried a few variations of setting a Value or QueryString parameter but I think it’s the WHERE that is tripping me up:

SELECT PartName   FROM testModel   WHERE :getModel = 'Yes'

SELECT PartName FROM testModel WHERE '{getModel}' = 'Yes'

getModel
getModel1232×491 43.4 KB

Appreciate any help, thanks

2

image
image1918×282 22 KB

you can use scripting like this

1 Like
3

thanks very much, I'll give that a try

4

With that table structure and a fixed number of models, you could do something like this:

SELECT
	PartName
FROM
	testModel
WHERE
	(:model = 1 AND Model1 = 'Yes') OR
	(:model = 2 AND Model2 = 'Yes') OR
	(:model = 3 AND Model3 = 'Yes') OR
	(:model = 4 AND Model4 = 'Yes') OR
	(:model = 5 AND Model5 = 'Yes')

If the number of models is going to change, I would consider restructuring this to separate Part and Model tables.

1 Like
5

Yeah there will be more models and parts in the future so i might be better reformatting the source tables, thank you very much

6

You’re almost there. Querystring is what you want. But remove the ‘ ‘ from the {getModel}. They show them in the example in the docs because they’re using the querystring call on the value side, and they’re looking at a string.

Basically, just get it to work without the parameter, and then replace the columnname with the querstring parameter.

1 Like
7

oh I was being too literal, thanks

1 Like