# Ignition: search for a particular serial number from db

**URL:** <https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423>\
**Category:** Ignition\
**Created:** [October 12, 2011, 8:44pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423 "2011-10-12T20:44:10Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![bachphi](https://avatars.discourse-cdn.com/v4/letter/b/ccd318/32.png) [@bachphi](https://forum.inductiveautomation.com/u/bachphi)\
**Post date:** [October 12, 2011, 8:44pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/1 "2011-10-12T20:44:10Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [October 12, 2011, 8:59pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/2 "2011-10-12T20:59:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bachphi](https://avatars.discourse-cdn.com/v4/letter/b/ccd318/32.png) [@bachphi](https://forum.inductiveautomation.com/u/bachphi)\
**Post date:** [October 12, 2011, 9:09pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/3 "2011-10-12T21:09:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [October 12, 2011, 9:21pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/4 "2011-10-12T21:21:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bachphi](https://avatars.discourse-cdn.com/v4/letter/b/ccd318/32.png) [@bachphi](https://forum.inductiveautomation.com/u/bachphi)\
**Post date:** [October 12, 2011, 9:28pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/5 "2011-10-12T21:28:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [October 12, 2011, 9:56pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/6 "2011-10-12T21:56:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bachphi](https://avatars.discourse-cdn.com/v4/letter/b/ccd318/32.png) [@bachphi](https://forum.inductiveautomation.com/u/bachphi)\
**Post date:** [October 12, 2011, 10:20pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/7 "2011-10-12T22:20:47Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [October 12, 2011, 10:25pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/8 "2011-10-12T22:25:01Z")

</div>

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}%'`

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 13, 2011, 9:34am UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/9 "2011-10-13T09:34:31Z")

</div>

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. 🙂 Bind where to the expression:

```auto
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:

```auto
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,

---

<div class="post-metadata">

**Author:** ![bachphi](https://avatars.discourse-cdn.com/v4/letter/b/ccd318/32.png) [@bachphi](https://forum.inductiveautomation.com/u/bachphi)\
**Post date:** [October 13, 2011, 1:01pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/10 "2011-10-13T13:01:17Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 13, 2011, 1:55pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/11 "2011-10-13T13:55:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bachphi](https://avatars.discourse-cdn.com/v4/letter/b/ccd318/32.png) [@bachphi](https://forum.inductiveautomation.com/u/bachphi)\
**Post date:** [October 13, 2011, 5:07pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/12 "2011-10-13T17:07:13Z")

</div>

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

I think this is what it should be:

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 13, 2011, 6:25pm UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/13 "2011-10-13T18:25:34Z")

</div>

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:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/d23659b203d2d24d394be983a52adb2fa6fe23d4.gif "Mr. Green")

---

<div class="post-metadata">

**Author:** ![bachphi](https://avatars.discourse-cdn.com/v4/letter/b/ccd318/32.png) [@bachphi](https://forum.inductiveautomation.com/u/bachphi)\
**Post date:** [October 14, 2011, 10:21am UTC](https://forum.inductiveautomation.com/t/ignition-search-for-a-particular-serial-number-from-db/3423/14 "2011-10-14T10:21:28Z")

</div>

Thanks for your help JC