Ignition sql update issue

i have create a job use ignition work with my company’s erp to print label for the production.
i use the scanner to input the data upside picture number 1 EHC800-375791-2020-06
Then the red number 2’s polling rate is off, if the scan input change , this value will use sql also change

there are red number 3,4,5 that data is link to red number 2, C00127024, all of them polling is off

my problem is , most of the case, it will work perfect.
But i have twice meet the problem , when i scan the data in number 1, the number 2 will change correctly, but number 3,4,5 will keep the previous data(almost 1000 cycle i will meet 1 accident)
i assume the data refresh have some issue at that time, but how to avoid these kind of unexpected issue , if these sql actually not change

Are each of those fields database queries? It would probably be more efficient (and robust) to take care if it through scripting.

yes, they are database queries.

Here is the current structure, i scan the data 1, in table 1, then i use sql query know 2, then i use sql query know 3,4,5 in table 2, all the data is in my ERP
I have no idea how to use scripting to run the sql query, read the data from the ERP , Ignition have some function like system.data… for scripting??

Read up on system.db.runPrepQuery()

Use a JOIN in your query to get data from both tables at the same time.

Example to get you started, using the propertyChange event of the scan text field:

if event.propertyName == 'text':
	SerNum = event.newValue

	query = """SELECT Table1.col1, Table1.col2, Table2.col3, Table2.col4, Table2.col5
			   FROM Table1
			   INNER JOIN Table2 ON Table1.col2=Table2.col2	
			   WHERE Table1.col1 = ?

	result = system.db.runPrepQuery(query, [SerNum], 'dbConnectionName')

	# In this example 'col1', etc. correlates to the columns in the query.
	value1 = row[0]['col1']
	value2 = row[0]['col2']
	value3 = row[0]['col3']
	value4 = row[0]['col4']
	value5 = row[0]['col5']
1 Like

it works, thanks a lot.