# Checking against a table

**URL:** <https://forum.inductiveautomation.com/t/checking-against-a-table/86682>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [March 1, 2024, 4:07pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682 "2024-03-01T16:07:58Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [March 1, 2024, 4:07pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/1 "2024-03-01T16:07:59Z")

</div>

I have a table that, right now, contains only 1 row with 2 columns and I am using only one of the columns "Buggy\_ID" to let me know if it is in the table using this query

```auto
	Outfeed_Gate_Buggy_Tag_ID = system.tag.readBlocking("[default]Buggy Corral/Outfeed Gate Buggy ID")[0].value
		
	pydata = system.db.runPrepQuery("SELECT Buggy_ID FROM Buggy_Corral_Contents WHERE Buggy_ID = ?", [Outfeed_Gate_Buggy_Tag_ID], 'IgnitionHud')
	columnHeaders = system.dataset.getColumnHeaders(pydata.getUnderlyingDataset())
	x = dict(zip(columnHeaders, pydata[0]))
	BuggyID = x.get("Buggy_ID")
	if BuggyID == Outfeed_Gate_Buggy_Tag_ID:
		self.getSibling("Button_0").props.enabled = True

```

The only Buggy\_ID in the table is 101056.

Does this look like a good start to tell if Buggy 101055 isnt in the table?

---

<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:** [March 1, 2024, 4:20pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/2 "2024-03-01T16:20:52Z")

</div>

It can be simplifed a bit:

```python
	Outfeed_Gate_Buggy_Tag_ID = system.tag.readBlocking("[default]Buggy Corral/Outfeed Gate Buggy ID")[0].value
		
	pydata = system.db.runPrepQuery("SELECT Buggy_ID FROM Buggy_Corral_Contents WHERE Buggy_ID = ?", [Outfeed_Gate_Buggy_Tag_ID], 'IgnitionHud')
	if pydata.rowCount > 0:
		self.getSibling("Button_0").props.enabled = True

```

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [March 1, 2024, 4:23pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/3 "2024-03-01T16:23:35Z")

</div>

Ah ok. Also, when I do see 101056, I will press an on screen button. I want that button to remove the row from that table that has 101056 in it. Would that be an APPEND query?

---

<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:** [March 1, 2024, 4:25pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/4 "2024-03-01T16:25:29Z")

</div>

That would be a DELETE query.

```auto
system.db.runPrepUpdate("DELETE FROM Buggy_Corral_Contents WHERE Buggy_ID = ?", [Outfeed_Gate_Buggy_Tag_ID], 'IgnitionHud')

```

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [March 1, 2024, 6:43pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/5 "2024-03-01T18:43:23Z")

</div>

Thanks. I will have, 1 more question shortly. Great help so far.

---

<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:** [March 1, 2024, 6:54pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/6 "2024-03-01T18:54:22Z")

</div>

I might even have an answer. How scary would THAT be? 😱

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [March 1, 2024, 9:22pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/7 "2024-03-01T21:22:36Z")

</div>

OK, what I am looking at now is this:

I will select a machine via a dropdown prop.value, that value will populate a table like this:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/f/bfe8ac98c620545abed10692f9f67b26aa0bb66f.png)  
I am curious how I can search this tables Lot\_Num:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/a/0a17a6af1c81de8830dcbb7f44c859061069afd0.png)  
By the LotNumber of that table?

---

<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:** [March 1, 2024, 9:41pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/8 "2024-03-01T21:41:47Z")

</div>

I think that's a substring function

What DB are you using?

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [March 1, 2024, 10:12pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/9 "2024-03-01T22:12:27Z")

</div>

Its a SQL DB.

I did, however, get this:

```auto
	LotNumber = self.getSibling("Table").props.selection.data[0].LotNumber
	#'O52LBQ07;O52LBR07;O52LBS07;O52LBT07'
	
	# Split the LotNumber string into individual lot numbers
	lot_numbers = LotNumber.split(';')
	
	# Initialize an empty list to store BuggyIDs
	buggy_ids = []
	
	# Iterate over each lot number
	for lot_number in lot_numbers:
	    # Add '%' wildcards to the lot_number variable
	    lot_number = '%' + lot_number + '%'
	    
	    # Execute the query for the current lot number
	    pydata = system.db.runPrepQuery("SELECT Buggy_ID FROM Buggy_Corral_Contents WHERE Lot_Num LIKE ?", [lot_number], 'IgnitionHud')
	    
	    # Check if there are any results
	    if pydata:
	        columnHeaders = system.dataset.getColumnHeaders(pydata.getUnderlyingDataset())
	        x = dict(zip(columnHeaders, pydata[0]))
	        BuggyID = x.get("Buggy_ID")
	        # Add the BuggyID to the list
	        buggy_ids.append(BuggyID)
	        # Remove None elements from the list
	        buggy_ids_cleaned = [x for x in buggy_ids if x is not None][0]
	        self.getSibling("LotLabel").props.text = buggy_ids_cleaned
	        
	    else:
	        # Add a placeholder if no BuggyID found for the current lot number
			buggy_ids.append(None)
			self.getSibling("LotLabel").props.text = ''

```

But im getting an error that states, Line 2 - which is this:

```auto
LotNumber = self.getSibling("Table").props.selection.data[0].LotNumber

```

is index out of range:0

I was thinking I could select the row and the LotNumber in the row is how I could filter the other table by having that table look at the LotLabel component.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 1, 2024, 11:07pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/10 "2024-03-01T23:07:23Z")

</div>

> [@mjohnson0238](#):
>
> Its a SQL DB.

Almost anything you can connect to Ignition as a database is a "SQL DB". MariaDB, MySQL, PostgreSQL, Oracle, and even Microsoft SQL Server are "SQL DBs". Though that last is a stretch, considering its poor support for the SQL **standard**.

Which do you mean?

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [March 2, 2024, 1:20pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/11 "2024-03-02T13:20:19Z")

</div>

My mistake, Microsoft SQL server.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 2, 2024, 2:27pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/12 "2024-03-02T14:27:50Z")

</div>

Is that Twister table with the concatenated lot number actually a view that is generating the lot list with GROUP\_CONCAT() ? If so, consider querying the underlying joined tables, and adding a join to the buggy table.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 2, 2024, 2:48pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/13 "2024-03-02T14:48:51Z")

</div>

Are we trying to make this not a buggy application?

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [March 2, 2024, 3:24pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/14 "2024-03-02T15:24:46Z")

</div>

Phil, it's a single table that I am querying. It doesn't have a concat(). Transitior, I am trying to use the multiple lots on the table and return the buggyID with matching lot of the other table. Most of the time the table with multiple lots will have only one lot but sometimes it will have multiple per row.

---

<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:** [March 4, 2024, 12:13pm UTC](https://forum.inductiveautomation.com/t/checking-against-a-table/86682/15 "2024-03-04T12:13:20Z")

</div>

~~Can you show how the first table is populated?~~

Some observations:

- It appears that you are trying to run the script with nothing selected.
- You can use the IN keword in your query so you only have to hit the DB once.
- You can check for null values in you query as well.

```python
	selectedData = self.getSibling("Table").props.selection.data
	if len(selectedData) > 0:
		LotNumber = selectedData[0].LotNumber
		#'O52LBQ07;O52LBR07;O52LBS07;O52LBT07'

		# Split the LotNumber string into individual lot numbers
		lot_numbers = LotNumber.split(';')
		
		# Make question marks for query
		lot_number_q_marks = ','.join(['?'] * len(lotnumbers))
		
		query = "SELECT Buggy_ID FROM Buggy_Corral_Contents WHERE Lot_Num IN ({}) and Buggy_ID IS NOT NULL".format(lot_number_q_marks)
		
		pydata = system.db.runPrepQuery(query, [lot_numbers], 'IgnitionHud')

		if pyData:
			buggy_ids = pyData.getColumnAsList(0)
		else:
			buggy_ids = ''
			
		self.getSibling("LotLabel").props.text

```