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
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?
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?
I will select a machine via a dropdown prop.value, that value will populate a table like this:
I am curious how I can search this tables Lot_Num:
By the LotNumber of that table?
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:
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.
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.
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.
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.
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.
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
