Trying to figure out how to have a popup warning box if the data I am about to submit has already been sent SQL

Ignition
1

Hi - I have been trying to think of the best way to do this:

I have an input form with a button that then runs a script and passes parameters to a named Query that then inserts data in to a SQL table.

I would like to add some code before the current script runs that does the following:

Lookup the SQL table and if two fields are present and a boolean value open up an error message saying record already submitted

if no boolean or NULL returned then open up Warning dialog box else continue the script.

I initially thought about using tags to store the result - but the vision client could be used on many stations and I want to keep it local to the client and not tied to a tag.

my current code before any lookup conformation (simply the insert data code) is below

params = {	'CUTTING_TICKET_INPUT'				:event.source.parent.getComponent('CUTTING_TICKET_SCAN_Form Text Field').getComponent('CUTTING_TICKET_Text Field').text,
			'LENGTH_ISSUED_INPUT'				:event.source.parent.getComponent('LENGTH_ISSUED_INPUT').text,
			'ROLL_NUMBER_INPUT'					:event.source.parent.getComponent('ROLL_Number_Scan_Text Field').text,
		    'DATE_INSERT'						:event.source.parent.getComponent('DATE_LABEL_').text,
		    'FAULTS_INSERT'						:event.source.parent.getComponent('FAULTS_LOOKUP').text,
			'PLANT_INSERT'						:event.source.parent.getComponent('PLANT_LABEL_YATE').text,
			'SUPPLIER_ITEM_INSERT'				:event.source.parent.getComponent('SUPPLIER_ITEM_NUMBER_LOOKUP').text,
			'USER_ID_INSERT'					:event.source.parent.getComponent('USER_ID_LABEL').text,
			'WIDTH_INSERT'						:event.source.parent.getComponent('WIDTH_LOOKUP').text
		
		
		
		
		}
print(params)
system.db.runNamedQuery("CREATE_KITTING_ISSUE", params)

My Named Query is below

INSERT INTO  CUTTING_TICKET_TABLE (

 [CUTTING_TICKET_NO],
	[LENGTH_ISSUED], 
	[ROLL_NUMBER],
	[DATE_INPUT], 
	[FAULTS],
	[PLANT], 
	[SUPPLIER_ITEM_NUMBER], 
	[USER_ID],
	[WIDTH],
	[LENGTH_CUT],
	[LENGTH_RETURNED],
	[DATE_ISSUED],
	[ISSUED]
	)
	
VALUES
(

:CUTTING_TICKET_INPUT,
:LENGTH_ISSUED_INPUT,
:ROLL_NUMBER_INPUT,
:DATE_INSERT,
:FAULTS_INSERT,
:PLANT_INSERT,
:SUPPLIER_ITEM_INSERT,
:USER_ID_INSERT,
:WIDTH_INSERT,
0,
0,
current_timestamp,
1)

I have done many google and forum searches & I am giving myself a bit of a headache — There may be a much easier way to do a verification that a record doesn’t already exist and notify user — but I just cant think of it.

2

No need to check ahead if your table has a proper unique constraint in place. Then the named query will simply fail. You can use try - except to catch that when it happens.

1 Like
3

Trouble is it has two be two fields that are when combined unique - example - Roll_number can be used across many Cutting ticket numbers - so I need to check that roll number is unique within the group of cutting ticket number.

do you think I should create another column and have a concatenated value of cutting ticket number and roll number in here then have this forced as unique?

or is there a better way?

4

Most databases can have a unique key based on more than one column in the table, no need to concatenate them into a single column.

4 Likes
5

With the brackets around your column names, I’m assuming SQLServer.

2 Likes