Search for SerialNumber using the same window

Ignition
1

I have a window that currently display a table and a date-range slide bar.
Data is displayed from Data properties with SELECT query:

SELECT *
FROM MyDB
WHERE t_stamp BETWEEN ‘{Root Container.Date Range.startDate}’ AND ‘{Root Container.Date Range.endDate}’

Now I want to add a text box in the same window to search for a particular serial number from the db and display that in the table in the same window.
If the text box is empty, then display data as normal from time-range. If it’s not empty then search for the serial number.

Thanks in advance.

2

Ok you can do the following:

  1. Add a custom property to the text field called “where” that is a string (right click on component and select Customizers > Custom Properties)
  2. Bind the “where” the following expression:if({Root Container.Text Field.text} = "", "1=1", "Col = '" + escapeSQL({Root Container.Text Field.text}) + "'"3) Change your query to look like this:SELECT * FROM MyDB WHERE t_stamp BETWEEN '{Root Container.Date Range.startDate}' AND '{Root Container.Date Range.endDate}' AND {Root Container.Text Field.where}Hope that helps.
3

at step 2, binding the “where” , I needed to add a parenthesis at the end

if ({Root Container.Text Field.text} = “”, “1=1”, “Column2 = '” + escapeSQL({Root Container.Text Field.text}) + “’” )

Not sure what 1=1 is, but it does work!

I added another text box and binded to “where2”, and it works too!.

Thanks much!!

4

That's a way of bypassing the where clause if it's not needed. Since 1 always equals 1 it will bring back everything in that table (if that was the only where clause of course). So, if you need to make a conditional where clause then putting in Where 1=1 if a condition is not met will be a good way of bypassing that where clause, you can't have an empty where clause.

So, in your example if the string is empty then you would make the where clause 1=1 to bring back every row, otherwise you would only bring back rows that had an empty string, which would probably be no rows. Otherwise it will put the contents of your text box in the where clause. Hopefully that makes sense?