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.
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.
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?
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
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 ?
breaks are gone too.limit 1 to the query..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.table[0] unpack itself. and selectedLine == row["LINE"] from the condition since it can only be true:LINE == selectedLine are included.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:
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.
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. ):
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.
I've moved all the myriad posts into this one new thread.
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
Thank you all, This screen is completed with validation.
Morning Phil,
is there a performance gain or loss by coding the Sql Syntax into a String then passing the result to the .runQuery() v.s using named queries.
String conversions and manipulations are definitely slower, and prevent the JDBC driver from optimizing repeated queries. But that isn't the most important reason. Using string manipulation is how you get SQL injection vulnerabilities. Just don't. Use Named Queries, or in scripts, use "Prep" queries.
Named queries also get easy access to caching, so you can trade memory for network traffic and CPU.