Help with data entry form

Hi,
I have entry screen. I inserting data into database. after insertion, if I select any line from drop down which is already there in database I am showing error message and after clicking OK button on message box , it should get all its corresponding data automatically on the components.


on Assign button , inserting.

after clicking on OK, I am not getting its value/ not able filter the data.

I am not using any table in screen.
please help me out in this.

Thank you

Find that code that is showing that error. That is not a native Ignition error. Fix the code you find. (Or post all relevant code here for us to help. Do not post screen shots. Post pre-formatted text.)

here its code on Assign BUTTON.

line = event.source.parent.getComponent('select line').selectedStringValue
sapordernum = event.source.parent.getComponent('sapordernumber').selectedStringValue
partnumber = event.source.parent.getComponent('select partnum').selectedStringValue
status = event.source.parent.getComponent('status').selectedStringValue
starttime = event.source.parent.getComponent('startdate ').text
machineid = event.source.parent.getComponent('machineID').text
endtime = event.source.parent.getComponent('end date').text

if event.source.parent.getComponent('select line').selectedStringValue == "":
	system.gui.messageBox("Please Select Line")
    
table = system.db.runQuery("select LINE,Status from dbo.[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE='"+line+"'")
if len(table)>0:
	for row in table:
	    status = row["Status"]
	    print status	    
	    if status == 'Completed':
	    	system.gui.messageBox("Already Line is Assigned.")	
	    	break    	
	    else:
	     try:
	       system.gui.messageBox("after try message box.")
	       print "abcds"
	       system.db.runQuery("update RAPTOR_LINECHANGE_ASSIGNMENT set Status ='Completed', EndTime='"+endtime+"' where LINE='"+line+"'")
	       system.gui.messageBox("Line Updated Successfully.")
	       break
	     except :
	   		system.gui.messageBox("Line is not present.")	
	   	
#else:       
elif event.source.parent.getComponent('status').selectedStringValue == "In Progress":
    #event.source.parent.getComponent('status').selectedLabel == "In Progress":
	event.source.parent.getComponent('end date').text == "NULL"
	system.db.runNamedQuery("Insert_Line_Assignment",{"LINE":line, "MACHINEID":machineid, "SAPOrderNum":sapordernum, "PartNumber":partnumber, "Status":status, "StartTime":starttime})
	l = system.db.runScalarQuery("SELECT LINE FROM dbo.[RAPTOR_LINECHANGE_ASSIGNMENT] WHERE SAPOrderNum='"+sapordernum+"'")
	system.gui.messageBox("The selected line is: %s" %l)
	
	
	
	
#	count = system.db.runQuery("SELECT COUNT(*) FROM [dbo].[RAPTOR_LINECHANGE_ASSIGNMENT] WHERE SAPOrderNum='"+sapordernum+"'")
#	system.gui.messageBox("'%sapordernum',count") 
#	print count
	system.gui.messageBox("Order Assigned Successfully.")  
#if len(table)>0:
#    system.gui.messageBox("Order Assigned Successfully.")
    #break
event.source.parent.getComponent('sapordernumber').selectedValue = -1
event.source.parent.getComponent('select line').selectedValue = -1
event.source.parent.getComponent('select partnum').selectedValue = -1
event.source.parent.getComponent('status').selectedValue = -1
system.gui.messageBox("Order Assigned Successfully.")

here is the code on Selected Line dropdown, on property Change event

if event.propertyName == "selectedStringValue":
	selNewValue = event.newValue
	print selNewValue
	
	selectedLine = event.source.selectedStringValue
	selectedStatus = event.source.parent.getComponent('status').selectedStringValue
	#print selectedLine
	#table = system.db.runQuery("select top 1 MACHINEID,SAPOrderNum,PartNumber,Status,StartTime from [dbo].[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE = '"+selectedLine+".strip()' order by StartTime desc")
	table = system.db.runQuery("select top 1 MACHINEID,SAPOrderNum,PartNumber,Status,StartTime from [dbo].[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE ='"+selectedLine+"' order by StartTime desc")
	#table = system.db.runQuery("select top 1 MACHINEID,SAPOrderNum,PartNumber,Status,StartTime from [dbo].[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE='"+selectedIndex+"' order by StartTime desc")
	for row in table:
	    machineid = row["MACHINEID"]
	    sapordernumber = row["SAPOrderNum"]
	    partnum = row["PartNumber"]
	    status = row["Status"]
	    starttime = row["StartTime"]
	     #date = dateFormat(starttime, 'yyyy-MM-dd 00:00:00')
	    print starttime
	    print status
	
	    if status == 'Completed' and selectedLine == row["LINE"]:
	   		system.gui.messageBox("Line is Comlpleted")
	   		break
	    else:
			system.gui.messageBox("The Line is in Progress,Please Comlplete")
			break
			event.source.parent.getComponent('machineID').text = machineid
			break
			event.source.parent.getComponent('select partnum').selectedStringValue = partnum
			break
			event.source.parent.getComponent('status').selectedLabel = status
			#event.source.parent.getComponent('status').selectedStringValue = status
			break
			print status
			break
			event.source.parent.getComponent('startdate ').text = "2022-12-29 01:00:00"
			break
			
							

Thank you

Even I have doubt on this.

I have first dropdown and text fields in my screen.
I using all these input fields to insert data into database.

image

after selecting line from dropdown, example : R22
since it is already in database, user is unable to insert same line. so I am showing message box.
after clicking on OK on message box,
I want all column values to be auto populated from database table.

A few questions:
• What kind of message box are you using for this? Is it a system.gui.confirm, a JOptionPane, or an ignition popup window?

• Where are you firing the script from that is supposed to populate everything from the database?

• What code are you using to accomplish this task?

image

oh ok . I am using just giu.messageBox.
Shall I use confirm message box.Then how I can write my code in confirm OK button,.
please tell me the steps.

here for first dropdown, on property change Event, i have written the code like this:

if event.propertyName == "selectedStringValue":
	selNewValue = event.newValue
	print selNewValue
	
	selectedLine = event.source.selectedStringValue
	selectedStatus = event.source.parent.getComponent('status').selectedStringValue
	#print selectedLine
	#table = system.db.runQuery("select top 1 MACHINEID,SAPOrderNum,PartNumber,Status,StartTime from [dbo].[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE = '"+selectedLine+".strip()' order by StartTime desc")
	table = system.db.runQuery("select top 1 MACHINEID,SAPOrderNum,PartNumber,Status,StartTime from [dbo].[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE ='"+selectedLine+"' order by StartTime desc")
	
	for row in table:
	    machineid = row["MACHINEID"]
	    sapordernumber = row["SAPOrderNum"]
	    partnum = row["PartNumber"]
	    status = row["Status"]
	    starttime = row["StartTime"]
	     #date = dateFormat(starttime, 'yyyy-MM-dd 00:00:00')
	    print starttime
	    print status
	
	    if status == 'Completed' and selectedLine == row["LINE"]:
	   		system.gui.messageBox("Line is Comlpleted")
	   		break
	    else:
			system.gui.messageBox("The Line is in Progress,Please Comlplete")
			break
			event.source.parent.getComponent('machineID').text = machineid
			break
			event.source.parent.getComponent('select partnum').selectedStringValue = partnum
			break
			event.source.parent.getComponent('status').selectedLabel = status
			#event.source.parent.getComponent('status').selectedStringValue = status
			break
			print status
			break
			event.source.parent.getComponent('startdate ').text = "2022-12-29 01:00:00"
			break
			
							

Some people shy away from any kind of system.gui messaging because it locks the gui thread, but even if that doesn't matter to you, switching to system.gui.confirm is probably not necessary for your usage case. Try adjusting the break lines in your code. Once your thread hits one of those, nothing more will happen. In the above code example, the messagebox shows, and the thread terminates at the first break before any string values get populated. Furthermore, there is no possibility of the loop running for more than one iteration.

Another approach would be to use warning labels next to the improperly populated fields instead of using a popup.

ya I just removed the break. after checking message box its working . all fields are getting auto populating.
and In same way to disable all fields after auto populating.
Thank you, Thank you so much

1 Like

There's absolutely no chance this does what you think it does.

To make it simple, break stops the execution of the current loop.
Which means that once a break is encountered, nothing that comes immediately afterward on the same level of indentation will NOT be executed.
Here, in your else clause, only the first line will EVER be executed, as it is followed by a break.
Not only that, but only the first iteration of the loop will ever be executed, since both if and else have an unconditional break in them. Which makes the whole loop be... not a loop.

If you tell us what you want this code to do, I'll rewrite it for you. And explain along the way how it works.

If you describe precisely (throw in an example so we can be sure we're understanding each other) what you're trying to achieve, we might even figure out a better way than a change script, and remove that code entirely.

edit: Here's a quick simplification of the code you posted above.

if event.propertyName == "selectedStringValue":
	selNewValue = event.newValue
	selectedLine = event.source.selectedStringValue
	selectedStatus = event.source.parent.getComponent('status').selectedStringValue

	query = """
	select top 1 MACHINEID, SAPOrderNum, PartNumber, Status, StartTime
	from [dbo].[RAPTOR_LINECHANGE_ASSIGNMENT]
	where LINE = '{}'
	order by StartTime desc
	limit 1
	""".format(selectedLine)
	table = system.db.runQuery(query)
	
	machineid, sapordernumber, partnum, status, starttime = table[0]

	if status == 'Completed':
		system.gui.messageBox("Line is Comlpleted")
	else:
		system.gui.messageBox("The Line is in Progress,Please Comlplete")
		event.source.parent.getComponent('machineID').text = machineid
		event.source.parent.getComponent('select partnum').selectedStringValue = partnum
		event.source.parent.getComponent('status').selectedLabel = status
		event.source.parent.getComponent('startdate ').text = "2022-12-29 01:00:00"

note: No logic has been changed, it's only a simplification
So, what changed ?

  • since only the first iteration of the loop was ever executed, I removed it. Which means the breaks are gone too.
  • since only the first row of the table was used, I added limit 1 to the query.
  • took the query text out of the function call and used .format instead of string concatenation, so it was easier to see that the only change there was the addition of limit 1. Put it back into the function call if you want it to be just one line.
  • grouped together the variable assignations and let table[0] unpack itself.
  • removed and selectedLine == row["LINE"] from the condition since it can only be true:
    The query makes sure only the rows where LINE == selectedLine are included.
1 Like

Thank you so much, I learnt many things.

I just copy pasted your code. its giving following error.

On Assign Button, I am inserting if line is not there in database.
if line is there and its status is "In Progress", I am updated to "Completed"
please check this also.
if I finish this screen, it will be a great help .
on Assign Button Code:

line = event.source.parent.getComponent('select line').selectedStringValue
sapordernum = event.source.parent.getComponent('sapordernumber').selectedStringValue
partnumber = event.source.parent.getComponent('select partnum').selectedStringValue
status = event.source.parent.getComponent('status').selectedStringValue
starttime = event.source.parent.getComponent('startdate ').text
machineid = event.source.parent.getComponent('machineID').text
endtime = event.source.parent.getComponent('end date').text

if event.source.parent.getComponent('select line').selectedStringValue == "":
	system.gui.messageBox("Please Select Line")
    
table = system.db.runQuery("select LINE,Status from dbo.[RAPTOR_LINECHANGE_ASSIGNMENT] where LINE='"+line+"'")
if len(table)>0:
	for row in table:
	    status = row["Status"]
	    print status	    
	    if status == 'Completed':
	    	system.gui.messageBox("Already Line is Assigned.")	
	    	break    	
	    else:
	     try:
	       system.gui.messageBox("after try  block, just checking try blocking is working .")
	       system.db.runUpdateQuery("update RAPTOR_LINECHANGE_ASSIGNMENT set Status ='Completed', EndTime='"+endtime+"' where LINE='"+line+"'")
	       #system.db.runQuery("update RAPTOR_LINECHANGE_ASSIGNMENT set Status ='Completed', EndTime='"+endtime+"' where LINE='"+line+"'")
	       system.gui.messageBox("Line Updated Successfully.")
	       break
	     except :
	   		system.gui.messageBox("Line is not present.")	
	   	
#else:       
elif event.source.parent.getComponent('status').selectedStringValue == "In Progress":
    #event.source.parent.getComponent('status').selectedLabel == "In Progress":
	event.source.parent.getComponent('end date').text == "NULL"
	system.db.runNamedQuery("Insert_Line_Assignment",{"LINE":line, "MACHINEID":machineid, "SAPOrderNum":sapordernum, "PartNumber":partnumber, "Status":status, "StartTime":starttime})
#	l = system.db.runScalarQuery("SELECT LINE FROM dbo.[RAPTOR_LINECHANGE_ASSIGNMENT] WHERE SAPOrderNum='"+sapordernum+"'")
#	system.gui.messageBox("The selected line is: %s" %l)
	system.gui.messageBox("Order Assigned Successfully.")  

event.source.parent.getComponent('sapordernumber').selectedValue = -1
event.source.parent.getComponent('select line').selectedValue = -1
event.source.parent.getComponent('select partnum').selectedValue = -1
event.source.parent.getComponent('status').selectedValue = -1

after selected same line, If line , Status Completed also message showing message box Line is in progress,
it should show line completed.

please check where I am missing

One more thing:
the variables selNewValue and selectedStatus declared at the beginning are not used. You can remove them if you don't plan on using them.

So, the unexpected message is in your propertyChange event. A brief review suggests you have way too much scripting--scripting that should be a few bindings. Also, I strongly recommend NOT using message boxes--they freeze your UI. I also strongly recommend you stop constructing SQL for .runQuery() by concatenating values into quotes strings. Use a Named Query, or if you must, use .runPrepQuery() with proper substitutions.

Anyways, I recommend the following:

  • Make a non-caching Named Query with
Select tTop 1 MACHINEID, SAPOrderNum, PartNumber, Status, StartTime
From db.RAPTOR_LINECHANGE_ASSIGNMENT
Where LINE=:lineParam
Order By StartTime Desc
  • On the dropdown component, add a custom dataset property LineAssignment and use the named query to make a binding to the above. Pass the dropdown's .selectedStringValue to the named query as the lineParam parameter.

  • Make additional custom properties with datatypes matching the columnTypes of the above dataset. Bind to the row 0 value, wrapped in a try(), like so for LineStatus:

try({Root Container.Dropdown.LineAssignment}[0,'Status'], "")
  • Add a label adjacent to the dropdown and bind its text to the dropdown's LineStatus property. This shows the use the current condition of that line. Style it according to the display value, if you like.

  • Use the desired values in LineStatus in a binding to enable/disable the assignment button. That button will no longer need to check for a valid line in its actionPerformed event. After updating the line, use system.db.refresh() on the named query property to make it pick up the new values.

  • Use additional bindings to provide initial values to the data entry fields.

4 Likes

I just replied on your other topic, @kavya_shree.

https://forum.inductiveautomation.com/t/dataset-filtering-on-dropdowns/61052/11?u=pturmel

You should know that it is against this forum's rules to post the same question in multiple places. Your help is now fragmented. ):

1 Like

My bad, I didn't see the select top 1 in your query. Remove the limit 1 I added.

That being said, you should do as Phil said in the other thread.
As I stated in a previous post, "There might be a better way, which would make the whole script obsolete". Phil provided that better way.

1 Like

I've moved all the myriad posts into this one new thread.

2 Likes

So sorry When I was searching for different issues, I added same issues because all it was related with each other.

Sorry for inconvenience. Thank you