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

**URL:** <https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897>\
**Category:** Ignition\
**Tags:** ignition80, perspective\
**Created:** [March 25, 2021, 8:41am UTC](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897 "2021-03-25T08:41:22Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\
**Post date:** [March 25, 2021, 8:41am UTC](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897/1 "2021-03-25T08:41:22Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/a/ca365b0b9521f9748ce632c84b9802b76c732852.png)  
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/7/670c1beb63604d022614de7264361252d0ff9585.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/9/49f683ec6ee754cd1282dd290a29dfb0f3ce59a1.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/b/6b6f9e09a80725c861859ad6294526e793dba0fc.png)

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?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 25, 2021, 11:25am UTC](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897/2 "2021-03-25T11:25:16Z")

</div>

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](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.0/com/inductiveautomation/ignition/common/util/DatasetBuilder.html) helper class instead, which requires/allows you to specify the column types explicitly.

---

<div class="post-metadata">

**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\
**Post date:** [March 25, 2021, 3:27pm UTC](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897/3 "2021-03-25T15:27:20Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/8/48bb4eed0f7f6d943a4dfb3a7860c0dc7d8a54d3.png)  
switched back to displaying nulls as blanks, but `numberFormat` is still ignored for columns with a null in first row:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/a/6a09c768be96d68d91142ada24e5cef1165ef4d3.png)  
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!

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 25, 2021, 3:33pm UTC](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897/4 "2021-03-25T15:33:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [March 25, 2021, 3:59pm UTC](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897/5 "2021-03-25T15:59:53Z")

</div>

> [@pturmel](#):
>
> The Perspective table shouldn’t be making decisions about formatting for all rows from the contents of the first row.

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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [March 25, 2021, 5:03pm UTC](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897/6 "2021-03-25T17:03:37Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/3/73412dc3490307a2fe3f8bb97ae76c4a7f0b53f3.png)

Even using a dataset, things work as expected:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/1/91f98448d78be62546a87ffa36a49c935ed3d16d.png)

---

<div class="post-metadata">

**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\
**Post date:** [March 25, 2021, 5:59pm UTC](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897/7 "2021-03-25T17:59:01Z")

</div>

Thanks for checking @paul-griffith . 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):

```auto
"#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.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [March 25, 2021, 6:27pm UTC](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897/8 "2021-03-25T18:27:28Z")

</div>

> [@witman](#):
>
> "F","F","str"

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

---

<div class="post-metadata">

**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\
**Post date:** [March 29, 2021, 4:40pm UTC](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897/9 "2021-03-29T16:40:30Z")

</div>

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:

```auto
	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()

```