Perspective Table Update

pkhoshroo · September 9, 2021, 1:35pm

Hello everyone,

For an application, I am using the table component in perspective.

So basically every time the OK button is pressed, a new row should appear with new values.
The table has 23 columns whereas I want to update only 10 columns as the operator enters only these 10 values.

Originally, what I wanted to do was to bind each field to one memory tag and then use those tags in a table on another view, then at the same time I wanted to update the database based on the new values entered into the fields, but I thought this would not be the easiest way to do (I am not sure tho).

Now what I am doing is to use a query dataset tag to read from the database and by entering numbers into the fields, the query tag has to be updated, or at least I want the table to be updated.

I would appreciate any help or information you could give me. Thank you!

victordcq · September 9, 2021, 1:52pm

its usualy not a good idea to bind those kinda input fields to tags, as multiple clients could be running and that is going to be very messy with tags if multiple people are working on it…

however the table can be bound with a dataset tag.
and then on the button onaction script you can write to set dataset which will update the table for all users.
you’ll jsut have to add in default values for the empty columns for a dataset to work i guess.
You can also just directly write to the database and do a refreshBinding('props.data') on the table for it to pull the new data.

pkhoshroo · September 9, 2021, 3:14pm

Thank you for your reply, Victor!
So this is what I did:

created a query tag dataset from my database
bound the dataset to the table’s data

Now the next step is to write a script on the button to set the fields data to each of the dataset tags, correct?

System.tag.writeblocking("[default]ms/metal/value[Cub]",'self.parent.parent.getChild("FlexContainer_0").getChild("TextField").props.text')

I get error for the above script.

Where should I add “refreshBinding(‘props.data’)” ? On the table data? or on the button?

victordcq · September 9, 2021, 4:16pm

no that does not look correct.

in the button script you should :
first read the dataset from the tag,
then do an system.dataset.addRow() with all the values in
and then write it back to the tag.
after that you should put the refreshBding in the button but point to the table
like

self.parent.parent.getChild("table").refreshBinding(‘props.data’)

leonardo.godoi · September 9, 2021, 4:26pm

I don’t think you can update a database using a dataset query tag but i might be wrong since i don’t use it much.

If you’re looking for info, you can take a look here, it came in handy when i just needed a quick solution to a similar problem:
https://docs.inductiveautomation.com/display/DOC81/Simple+Database+Editor
Unfortunately i’m not proficient enough with SQL to just give a quick answer

pkhoshroo · September 9, 2021, 6:00pm

This is the first time I am using this script in perspective as I am new to using perspective so I am not sure how to use addRow in a database and could not find anything similar to get a good understanding of it as well.
So I wrote this which I am 100% sure is not correct.

# 1. Read the dataset from the tag (this should returns 23 columns)
	newrow = system.tag.readAsync("[default]ms/Test/QueryTag.value")

# 2. addRow to dataset
	dataset=system.dataset.addRow(dataset, newrow)

#3. Writing back to the tag
	System.tag.writeAsync("[default]ms/Test/QueryTag.value",'dataset')

#4. Refresh Binding on the table
self.parent.parent.parent.parent.parent.getSibling("Melt").getChild.getchild("Table").props.data

I am sure the script is not correct at all, but I am not able to understand the process very well, as this is a dataset and is not a single number I am kind of confused about how to update the dataset.

josborn · September 9, 2021, 6:02pm

You can do this by creating a named query with an SQL insert statement, then executing the named query on your button event. You would need to pass each one of the values of the popup to the named query as a parameter. You may need to refresh your table after aswell.

pkhoshroo · September 9, 2021, 6:14pm

Thank you for your reply!

So it is going to be:

Creating a named query from my database
Writing the script below in the query section:
INSERT INTO table_name ( column1 , column2 , column3 , …)
VALUES ( value1 , value2 , value3 , …)
the values should be parameters? Not sure what to put here as values as named querries does not accept tags.
executing named queries from the button script.
refresh binding

pkhoshroo · September 9, 2021, 6:16pm

The link is going to be very helpful for me. Thank you, Leonardo!

josborn · September 9, 2021, 6:16pm

Heres an example that may help. I created an online form to submit request to our prox system.
Named Query:

The script to execute the query:

	if self.custom.formComplete:
		params = {"action": 'do something',
			"datePar":self.getChild("Date").props.value,
			"department":self.getChild("Department").props.value,
			"division":self.getChild("Division").props.text,
			"name":self.getChild("FullName").props.text,
			"phone":self.getChild("Phone").props.text,
			"title":self.getChild("Title").props.text,
			"FName":self.getChild("FName").props.text,
			"LName":self.getChild("LName").props.text
			}
		temp = system.db.runNamedQuery(system.project.getProjectName(),"CONTINUUM/FORM_InsertForm", params, getKey=1)	
		self.custom.formPK = temp

I have some stuff in there to check if the form is complete that you probably dont need.

pkhoshroo · September 9, 2021, 6:22pm

Thank you!
In this example all the columns need to be filled, correct? As I only care about 10 out of 23 columns I have.

leonardo.godoi · September 9, 2021, 6:23pm

Should probably look something like this:

#1 define your row values into a list:
newRow = [Var1,Var2,Var3,VarX,VarY...] 
 #If your dataset has 23 columns, this should have 23 values,
#even if they're empty.

# 2. Get your dataset:
dataset= system.tag.readAsync("[default]ms/Test/QueryTag.value")

#3. Alter your dataset:
dataset = system.dataset.addRow(dataset,dataset.getRowCount()+1,newRow)
#The second argument  is to make sure that your new row goes to the bottom
#You can change that if you want.

#4. Write it back to your tag:
System.tag.writeAsync("[default]ms/Test/QueryTag.value",dataset)

#5. Refresh Binding on the table
self.parent.parent.parent.parent.parent.getSibling("Melt").getChild.getchild("Table").props.data

But, if it’s not possible to write to a database by altering a dataset tag, this should’t work then.

pkhoshroo · September 9, 2021, 6:28pm

Thank you very much for your valuable time.
In this example, Var1…VarY are values from the text field or the dataset column names?
It should be the text field values, but I have only 10 fields, which means that I have to put an empty string or 0 instead of the remained 13 columns, correct?

josborn · September 9, 2021, 6:31pm

That would depend on if your table can accept nulls, in my example they are all required. I believe it would execute with no input on fields that allow null.

leonardo.godoi · September 9, 2021, 6:36pm

They’re values from the text field on the order that correspond to the order of the columns on the dataset.
If you have a dataset with four columns named “Time”, “PostalCode”, “Number” and “Place” and you don’t want to populate the last one your newRow should look like this:
newRow = [“26/09/1998 17:57”, Null,47,“BR”]
I’m not sure if Ignition would accept a straight up Null value, but if the column type it’s a string you could use “”

leonardo.godoi · September 9, 2021, 6:39pm

Just to be clear, if you have more 13 empty columns, you should keep adding Nulls, like this:

newRow = [“26/09/1998 17:57”, Null,47,“BR”,Null,Null,Null,Null,
          Null,Null,Null,Null,Null,Null,Null,Null,Null]

Also to make a clear distinction:

josborn is right when editing the table directly, i was referring to specifically when you're working with datasets, then you should pass the Nulls yourself.

pkhoshroo · September 9, 2021, 6:39pm

Thank you very much, Leonardo. I am going to try it and see if it is working. I do really appreciate your help.

pkhoshroo · September 9, 2021, 7:38pm

I tried the mentioned method but I got this error:

TypeError: readAsync(): missing required argument 'callback’

This is the script I used (it did not accept Null):

    # define values:
	Var1='self.parent.parent.getChild("Melt").getChild("TextField").props.text'
	Var2=...
	Var3=...
	Var4=...
	Var5=...
	Var6=...	
	Var7=...
	Var8=...
	Var9=...
	Var10=...
	
	
	#1 define the row values into a list:
	newRow = [Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10, "", "", "", "", "", "", "", "", "", "", "", "", ""]
	
	# 2. Get the dataset:
	dataset= system.tag.readAsync("[default]ms/Test/QueryTag.value")
	
	#3. Alter the dataset:
	dataset = system.dataset.addRow(dataset,dataset.getRowCount()+1,newRow)
	#The second argument  is to make sure that your new row goes to the bottom
	#You can change that if you want.
	
	#4. Write it back to your tag:
	System.tag.writeAsync("[default]ms/Test/QueryTag.value",dataset)
	
	#5. Refresh Binding on the table
	self.parent.parent.parent.parent.parent.getSibling("Melt").getChild.getchild("Table").props.data

leonardo.godoi · September 9, 2021, 7:41pm

Oh, try readBlocking on this one:

    # define values:
	Var1='self.parent.parent.getChild("Melt").getChild("TextField").props.text'
	Var2=...
	Var3=...
	Var4=...
	Var5=...
	Var6=...	
	Var7=...
	Var8=...
	Var9=...
	Var10=...
	
	
	#1 define the row values into a list:
	newRow = [Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10, "", "", "", "", "", "", "", "", "", "", "", "", ""]
	
	# 2. Get the dataset:
	dataset= system.tag.readBlocking("[default]ms/Test/QueryTag.value")
	
	#3. Alter the dataset:
	dataset = system.dataset.addRow(dataset,dataset.getRowCount()+1,newRow)
	#The second argument  is to make sure that your new row goes to the bottom
	#You can change that if you want.
	
	#4. Write it back to your tag:
	System.tag.writeAsync("[default]ms/Test/QueryTag.value",dataset)
	
	#5. Refresh Binding on the table
	self.parent.parent.parent.parent.parent.getSibling("Melt").getChild.getchild("Table").props.data

pkhoshroo · September 9, 2021, 8:35pm

Thank you! Now I get this error:

AttributeError: ‘java.util.ArrayList’ object has no attribute 'getRowCount’