[BUG] Perspective Table number format breaks if first row has null value

Ignition
,
1

This is in Ignition 8.1.2–I don’t have a later version installed yet to confirm whether it applies to them as well.

All of these columns have the same format settings:
image
The null values in October and November render as blank (great) and the number format is applied with one decimal place as expected. In the December column, the first row has a null value and it renders as null (as well as later nulls in this column) and the number formatting doesn’t get applied to any values in the column. This issues consistently occurs in any column with a null value in the first row (tested with varying datasets).

Column settings:
image image image

I’m guessing this could be worked around via a script transform to replace nulls in first row with something else, but haven’t tried yet. Any other suggestions, or is that the way to go?

2

Constructing datasets in scripts with system.dataset.toDataSet() ? It uses the first row’s datatype to set the datatypes of the columns. Nulls yield the string datatype by default. You should use the DatasetBuilder helper class instead, which requires/allows you to specify the column types explicitly.

1 Like
3

Thanks @pturmel. I’ve used DatasetBuilder–I think after a suggestion from you in the past (thanks!). It works great for scripted dataset creation. This is a SQL query binding. It makes sense it’d be relying on default behavior without anything else to go by. This led me to look for any column settings that might be relevant. Changing render from auto as above to number
image
switched back to displaying nulls as blanks, but numberFormat is still ignored for columns with a null in first row:
image
Is there a better way to specify column datatype in this case, or is post-processing the data into a new dataset via DatasetBuilder the way to go?

And should I remove the “BUG” prefix on this post, or would it be fair to expect giving the column a render type of number should result in numberFormat being applied to numbers in it in this case. It’s halfway there hiding the nulls!

4

Hmmm. I’d call it a bug. The Perspective table shouldn’t be making decisions about formatting for all rows from the contents of the first row.

1 Like
5

Well, in auto it probably does for performance reasons, but agreed that with an explicitly set render mode numberFormat should absolutely be respected.

2 Likes
6

Hmm, I wonder if this has been fixed since 8.1.2? I’m unable to replicate on 8.1.5, at least in the simplest case:
image

Even using a dataset, things work as expected:
image

7

Thanks for checking @PGriffith . If you want to throw it in version 8.1.5, here’s the same data I’m using (as returned by query binding and copied from dataset editor):

"#NAMES"
"Month 1","Month 2","Month 3"
"#TYPES"
"F","F","str"
"#ROWS","16"
"1278.5","926.5842",""
"2226.7104",,""
"2142.3948","3847.358","1870.9367"
"4018.0264","2740.6736",""
"5455.8687","4656.8735","1296.8262"
"4160.784","1936.6632","930.3579"
"5393.7686","4768.305","3934.0264"
"4315.542","3331.5156","2703.279"
"2277.4104","2507.2947","2575.4475"
,,"2124.8421"
"3126.6157","1718.7767","2695.4526"
"3222.6262","4043.2158","2266.5264"
"2814.2156","3720.937","2103.7211"
"2852.5894","3148.9844","2057.22627"
"9.336843",,""
"4121.726","3838.8687","2924.9895"

It looks like it is defining the column in dataset generated by query binding as string type based on null in first row in 8.1.2. Perhaps 8.1.5 does not do that. If that’s the case, pasting above dataset in won’t be relevant. Or perhaps 8.1.5 overrides the column type in dataset with column render setting. This would seem more likely, and if this is the case number formatting should apply to third column despite initial null.

8

Yeah, I'm guessing the column data type is playing into things. I'll take a closer look later today.

1 Like
9

Hopefully the underlying Perspective Table implementation will handle this in the future for columns with specified types. For now, this script transform does the trick:

	from java.lang import Float
	from com.inductiveautomation.ignition.common.util import DatasetBuilder
	
	if value is None:
		return value
	else:
		# Build new dataset with specified column types.
		colNames = ["Month 1", "Month 2", "Month 3"]
		colTypes = [Float, Float, Float]
		b = DatasetBuilder.newBuilder().colNames(colNames).colTypes(colTypes)
		# Copy original data into new dataset.
		for row in system.dataset.toPyDataSet(value):
			b.addRow(row[x] for x in range(len(row)))
		return b.build()