How to update "field" element of the Table Object "column" property?

[Jeffrey_Thompson]

The main objective is to have a "table object" (in Perspective), update automatically to show various data tables (including column names and custom formatting), when selected.

I have been able to update the "data" property using a query binding with a transform, from a solution found online, but I want to update the "column" property "name" element, from the table header as well.

What I have found is that if I manually add the column names from my sample database, the data "content" presents as desired, BUT..., if I have a column binding from the same database, the order of the column property elements appear in the order expected, but the table now displays data which is out of order, or in some other way jumbled.

Manually configuring each column property, does not allow for any flexibility in displaying a different database table, so that is out of the question.

What I also found was that where I only wanted to update the column property, "field:" element, but the binding destroys all other elements that I defined, such as "dateFormat:", where the other properties I would like unchanged.

I had read that a binding destroys all parameter elements, so that's a problem.

I tried to copy all existing property elements in an attempt at using them within the code, to re-insert them into the property, but that seems unnecessarily complicated.

I may be approaching what I had assumed would be a no-brainer, but I would appreciate if I can get some guidance, as I have searched online for a solution for a few days, without any success and need to move forward.

Hopefully I have presented the problem sufficiently to allow someone to provide the needed advice.

[pascal.fragnoud]

Just to make sure we're clear on everything, I'll try a little recap to see if I got it right:

• you have a perspective table that displays data from several sources, which I assume are database tables
• you're using a script transform on the table's data binding to 'merge' the tables together
• you're stuck on the column configuration

Right so far ?
What options do you want from the columns configuration that you don't get if you leave the columns property empty ?

[Jeffrey_Thompson]

Hi Pascal,

I do not do any merging, but simply display a table on demand. The data I can update but the columns with defined configurations, I am unable to do successfully.

I need to set the render and format elements of the column property and ideally the column width to be set to the width of the largest width of the data contained in a column (autofit idea).

Below is showing what I'm doing in the Query Binding.

Maybe I need to create a named query just for the table header?

I hope that clears up my problem further.

Thanks for your time.

[pascal.fragnoud]

I'm not sure I understand, there's nothing dynamic here, so what's the issue ?

Also you probably don't need the transform at all, tables can handle datasets just fine.
But if you really wanted an array of objects instead of a dataset, you could just change the query return type from dataset to json, and still remove the transform.

[Transistor]

@pascal.fragnoud, that will get the OP a table of values with database table names as headers but doesn't sort out the rendering, format and width (which is going to be the trickiest part).

I'll have a think ...

[pascal.fragnoud]

But since it's not dynamic, the columns could just be configured statically, and there's no trickery involved in that.

[Jeffrey_Thompson]

You are correct stating that there is nothing dynamic here (currently), as I want to get a single database and table functioning (displaying) as I want before I start calling different tables. It would to me not make sense to run down a dead-end road if I'm approaching the problem incorrectly.

I never tried the JSON and just noticed the binding preview is different, which also means I need to look into more details of the query binding options.

I should have also stated that I am not too familiar with Perspective and have only used Vision, but neither have I used Query Bindings with transforms.

I appreciate any and all comments.

[pascal.fragnoud]

So this is a short example of how things are set up, with the difference that in the end the query will be dynamic to pull data from different tables, selected from a dropdown or something similar ?

First, you don't need another query or anything for the column configuration, unless you have a table somewhere with specific configurations stored (which can be a solution for tricky configurations that are hard to figure out with just scripting). All you need is to bind against the data property and get the column names from there.
Then, you'll need to generate an array of objects (or a list of dicts in python speak).
One thing that can make this a bit easier to navigate is that you don't need to configure everything: only what needs to be changed. For properties that are not set up, the defaults will be used.

something like this:

return [
	{
		'field': col,
		'sortable': True,
		'header': {
			'title': col.upper()
		}
	} for col in value.columnNames
]

Generating the configurations like rendering could be done with the help of the column types, which are available in a dataset - but I believe the table already use them to configure sensible defaults.
For the width, I'm afraid you'll have to get the max length in every column if you want to compute proper values... Which in itself is not very hard to do.
Note that you can use either pixels when strictWidth is true, or relative size (kinda like flex basis) when it's false.

Does this put you on the right track ? What would you need help with ?

edit:
Here's something simple for types that might work for you:

import java.lang
import java.util

render_map = {
	java.lang.String: "string",
	java.lang.Integer: "number",
	java.util.Date: "date"
}

return [
	{
		'field': col_name,
		'sortable': True,
		'render': render_map.get(col_type, "auto"),
		'header': {
			'title': col_name.replace('_', ' ').title()
		}
	} for col_name, col_type in zip(value.columnNames, value.columnTypes)
]

[Jeffrey_Thompson]

I just changed the Return Format to JSON in the Query Binding and bound it to the Table "Data" property and also removed all the preconfigured Column configurations.

Using JSON without a transform, the data appears just as I believe you suggested it would, but without predefined columns configured.

The Table now updates with the Database "Column Names" which is great, but the column names in the database contain underscore characters.

I think this should be easily corrected in a script by removing the underscore from the column property "field" element (value) and using that in the "header title" property, less the underscore.

I think this is heading in the right direction, with more digging.

I will also add to the previous lack of knowledge statement that Python is also new to me

edit:

Sorry to ask what now seems too fundamental, but where do you place the Java code you just presented?

[pascal.fragnoud]

@Jeffrey_Thompson

but the column names in the database contain underscore characters.

The last script I provided takes care of that. I anticipated this, because I do exactly that in my own projects.
You might not want the .title() part though. I just threw that in to show an example of how the title might be configured to be different from the column name.

@Jeffrey_Thompson

Sorry to ask what now seems too fundamental, but where do you place the Java code you just presented?

It's not java, it's python - or rather, Jython, which runs on the JVM so is able to import java classes.
So just use it in a script transform.

[Jeffrey_Thompson]

Ya..., I really need to step way back and look at the Python further in Ignition. Hopefully there is some good documentation specific to Perspective.

The first line in the Query Binding Script is "def transform(self, value, quality, timestamp):

Pasting your code below that "fixed" code, is not liked

Thank you very much for taking the time for all the examples and I believe I'm on the path but as stated, need way more work on getting more familiar with Ignition.

[lrose]

@Jeffrey_Thompson

Pasting your code below that "fixed" code, is not liked

Make sure that it is indented correctly. That is the number one cause of pasted code not "working" as is.

@Jeffrey_Thompson

def transform(self, value, quality, timestamp):

This is the function definition (thus the def), everything should be at a minimum indented once from that. This is how python/jython determine scope.

Also make sure that you use the same indention through out the script, you can not mix both spaces and tabs as a style of indention in a script.

[pascal.fragnoud]

@lrose

Also make sure that you use the same indention through out the script, you can not mix both spaces and tabs as a style of indention in a script.

You actually can, as long as they're defining different scopes. ie:

def foo(x):
  if x == 3:
  	print "threeeeee !"
  else:
	print "not three :("

Yep, if and else are indented with 2 spaces, and the prints are indented with one tab.

It's absolutely disgusting, but it works.

[lrose]

@pascal.fragnoud

It's absolutely disgusting, but it works.

Didn't know that, it makes since why, but, talk about hard to remember. So much easier to just use one or the other everywhere.

[pascal.fragnoud]

You should just forget it works and never use it, anyway.

[Jeffrey_Thompson]

Ok..., one last time so I can move along

I tried various indents, but I still get the Error_Configuration.

I'm not used to not having any diagnostic messages or highlights for syntax, or any other feedback.

Again, thanks to everyone for the supportive feedback!

[pascal.fragnoud]

Everything under def needs to be indented once:

And please remove that ; after the return, this is not C !

edit: You can put your cursor over the red Error_Configuration on the bottom to get a little popup with some details.

[Jeffrey_Thompson]

Ok, my bad with the semicolon.

When in the Property Binding it can be seen that the order of table elements does not align with the order of the "data", which is being bound in a Query Binding as initially addressed (and is correct).

There is still an error being presented as shown here.

When I check the dot property in the "zip", it appears that the format is incorrect.

Thoughts?

I sure do love the power and flexibility available within the product, but the documentation is not awesome. Are there any PDF's of the manuals by chance? I would love an offline version but haven't found one.

The Core certification does not address anything I have dealt with in this thread.

Anyway..., I think I have to move on and hard code this table as this exercise in doing something that to me should be straight forward, is yet to feel so. Maybe I am way more dense that I gave myself credit for

[pascal.fragnoud]

You get this error because you're using json as your query return format in the data binding.
Those are dataset properties. Change the return format to dataset and you should be fine.

Why dataset ? Because the json version doesn't know what data types are used in the columns.
You could use isinstance on the first row to built your own types list, but frankly... when you can do something with a dataset, do it with a dataset.

Also, the render_map in this script is only accounting for a few types. You may want to

1. add more if you need different render formats
2. use render_map.get(col_type, "auto") instead of render_map[col_type], so it gets a default value of "auto" if the type is not found in the render_map

[Jeffrey_Thompson]

Hi Guys,

To hopefully completely end this discussion here, I have one more question and apologize if they are all appearing so fundamental.

I do tend to read and try as much as possible before I inquire publicly, but as this is still not successful in this trial-and-error exercise, I am back asking again.

The last question below, which will hopefully have an answer that I can carry forward elsewhere, is that I cannot seem to find a way to implement a conditional statement in the script that can:

• check the 'field': col_name = "Time"
• based on the check being TRUE - change the 'render' field to "date" from "auto" (or whatever).

I tried the two statements below in the Expression Binding with the results shown but could not determine the syntax to implement any conditional evaluation, assuming this is the correct direction.

• {this.props.columns[0].field} #current value is 'Time'
• {this.props.columns[0].render} #current value is 'date'

The above statements did not appear to work in other Binding areas, where if (for example) I added a second transform below the original one discussed above, it didn't work at all.

Some samples on this site show to use "self" instead of "this" which had errors stating the variable was undefined, or some other list of errors.

On a side note, what/where is the best location for a full document set which would allow for me to read about all the coding infrastructure of Ignition?

Thank you all again for the assistance.