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

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
system.db.runNamedQuery("CREATE_KITTING_ISSUE", params)	

My Named Query is below




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.

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

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?

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.


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