Ignition: search for a particular serial number from db

Ignition
1

Hi, I currently have a window that contains a table and a datetime slidebar object.

The table is populated with data using SELECT and the timeframe from the slidebar.
That part is working OK.

Next , I want to search for a particular serial number from the db. I know how to built the query ( SELECT * FROM mydb WHERE SerialNumber = ‘123456’ ), I just not sure what to do next.

Should I add/need a button to open another window? and a new window will contain input box and a table?

Thanks in advance.

2

You can simply put the text box on the same window as the table and your query can be something like:SELECT * FROM mydb WHERE SerialNumber LIKE '%{Root Container.Text Field.text}%'You could also do it through the use of multiple windows but it just means more work.

3

and just how or where do I put your SELECT statement into?

4

You bind the table’s data property to that SQL query.

5

The table already ‘bind’ to my SELECT statement, I am not sure how to bind another into it?

6

You just need to integrate the where clause into your SQL query that you already have.

7

This is my current SELECT statement:

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

how do I integrate yours into it? I think I would need an IF condition if the text box is not empty then run the second query?

8

You just add another condition to your where clause:SELECT * FROM mydb WHERE mydb.t_stamp BETWEEN '{Root Container.Date Range.startDate}' AND '{Root Container.Date Range.endDate}' AND SerialNumber LIKE '%{Root Container.Text Field.text}%'

9

If you’re looking to keep the table to display all the data when noting in entered, you can concatenate your query-- or portions of it into a string.

Next, add a dynamic property to the text field called where. This will become clear in a moment. :slight_smile: Bind where to the expression:

IF({Root Container.Text Field.text}="","",concat("AND SerialNumber LIKE '%",{Root Container.Text Field.text}%,"'")

This should give you the rest of the where clause to append to your query, if needed.

Next, make a dynamic property for the table called query (pretty creative, right?) and use the expression:

concat("SELECT * FROM mydb WHERE mydb.t_stamp BETWEEN '",{Root Container.Date Range.startDate},"' AND '",{Root Container.Date Range.endDate},"' ",{Root Container.Text Field.where})

Last bind the data property of the table to query, and you should be good to go.

Hope this helps!

Regards,

10

Yes, I do want to keep the table display all data when nothing is entered.
I tried to bind where to the expression but got syntax error on token ‘COMMA’ ( Line1, char 110)

11

Oops! Needed one more right-hand parentheses on the end to close up the IF staement. :blush:

12

I added another ) … but it’s not that, something else here. :unamused:

I think this is what it should be:

IF({Root Container.Text Field.text}="","",concat(“AND SerialNumber LIKE '%”,{Root Container.Text Field.text},"%’"))

13

Absolutely right. good catch. My eyes start to cross looking at all the single and double quotes.

I read somewhere a line that says “Make it possible for programmers to write in English, and you’ll find out that programmer’s can’t write in English”. :mrgreen:

14

Thanks for your help JC