[IGN-2200]Replace values displayed as "null" in the perspective table

[mazeyrat]

Ignition 8.1.3
When we query audit table for example, NULL value are displayed as "null" in the perspective table

Is there an effective way to display empty value instead of null ?

[witman]

If those columns are supposed to be numbers, a script transform like this to set the correct column types should work:

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

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…

If they are strings, you could do a script transform that replaces the null with "".

[mazeyrat]

I would like to avoid checking all dataset values @PGriffith
IMO it will be better that None value in a dataset would be displayed as empty string in the table component ?

[jpark]

How are you querying the audit table?

[PGriffith]

This is an option we’ve had a feature ticket for for a while. I kicked it up in prioritization.

[Gaurav.Shitole]

Removing the column name from columns.[X].field property worked for me. Doing this, it shows blank for null.

[ingraham]

Any update on this? I still see null in 8.1.11. (Also, I don’t think the removing columns.[X].field is the right solution, as it breaks everything else I’m doing with columns.)

[sanzio]

We too would use this feature. We have noticed that the word ‘null’ is a bit distracting and causes clutter on particularly large tables. Granted, another solution would be to shrink the table…

[jkirchman]

I had luck changing the data return format to json and all null values from the database show blank instead of ‘null’.

[JoeF]

I updated the columns.[x].render value from auto to number in my project and it removed the nulls. You may experiment with this for other data types.

[mazeyrat]

Thanks, but in my use case, columns are string, and with auto or string i obtain “null” (Ignition 8.1.14)

[Apurba_Roy]

I had similar problem but I managed to remove the nulls from the display. In case of integer columns (if associated with a table), it defaults to blank but in case of other data types, I explicitly used NVL() function in the SQL query itself to determine whether bounded column value is NULL or not and then, if NULL, replaced it will a blank space and it seems to work fine for me. The situation may not be the same for everyone but incase if it is associated with a table, the SQL query can be modified to present the formatted data to be displayed in the table.

[AlThePal]

I would just edit the SQL to use a COALESCE function with each value that could be a null. This allows you to replace a null value with whatever you want.

[jasoncoope]

Been waiting for this feature for a long time, where the table column can be configured to not display anything if the value is null.

Obviously there are ways around this and I've used them all.

1. Put an ISNULL() in the SQL Query and return empty string - But I use this SQL query to pull data that might then get written back to the database. now I'll have to replace all empty strings with nulls.

2. Use python script to iterate through returned dataset and replace nulls with empty string. Trouble is with a large dataset, this becomes really slow.

Would really like to see this feature in the table component soon!

[pturmel]

You missed:

3. Bind the query to a custom property for use with edits, and use a transform from that on the table to replace the nulls with empty strings.

[PGriffith]

I bumped the internal ticket a bit in priority, but it's sitting in a large pile of work, so no timeline on implementation.

[jasoncoope]

I see your back working on your next award!

[ynejati]

FYI, this is being implemented now and should be available very soon.

[witman]

Thanks! Will update address this buggy behavior, or something else?

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

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 col…

[ynejati]

When render is set to "auto", the table uses the contents of the first row to determine the type (performance reasons). The exception is when it is being fed a dataset, since it can derive the column types from the dataset object. Potentially we could do the first few rows, but then we eventually run into the same problem.

With the work we've done, you'll be able to specify how you want null values to display, and whether you also want to include "null" strings (case independent). You can define this globally, and also override it at the column level.