# \[IGN-2200\]Replace values displayed as "null" in the perspective table **URL:** \ **Category:** Ignition\ **Tags:** ignition80, perspective\ **Created:** [April 1, 2021, 9:27am UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161 "2021-04-01T09:27:28Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\ **Post date:** [April 1, 2021, 9:27am UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/1 "2021-04-01T09:27:28Z")
**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 ?** ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/a/ea852f3bd83e9178c814da64e44c1f17480216d3.png) ---
**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\ **Post date:** [April 1, 2021, 4:10pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/2 "2021-04-01T16:10:29Z")
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](http://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897/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… If they are strings, you could do a script transform that replaces the `null` with `""`. ---
**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\ **Post date:** [April 1, 2021, 7:38pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/3 "2021-04-01T19:38:35Z")
I would like to avoid checking all dataset values 😒 @paul-griffith IMO it will be better that None value in a dataset would be displayed as empty string in the table component ? ---
**Author:** ![jpark](https://avatars.discourse-cdn.com/v4/letter/j/a88e57/32.png) [@jpark](https://forum.inductiveautomation.com/u/jpark)\ **Post date:** [April 1, 2021, 10:06pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/4 "2021-04-01T22:06:27Z")
How are you querying the audit table? ---
**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:** [April 1, 2021, 10:42pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/5 "2021-04-01T22:42:45Z")
This is an option we’ve had a feature ticket for for a while. I kicked it up in prioritization. ---
**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\ **Post date:** [May 25, 2021, 9:15am UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/6 "2021-05-25T09:15:29Z")
Removing the column name from **columns.[X].field** property worked for me. Doing this, it shows blank for null. ---
**Author:** ![ingraham](https://avatars.discourse-cdn.com/v4/letter/i/3ab097/32.png) [@ingraham](https://forum.inductiveautomation.com/u/ingraham)\ **Post date:** [November 5, 2021, 3:41pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/7 "2021-11-05T15:41:13Z")
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.) ---
**Author:** ![sanzio](https://avatars.discourse-cdn.com/v4/letter/s/8c91f0/32.png) [@sanzio](https://forum.inductiveautomation.com/u/sanzio)\ **Post date:** [November 16, 2021, 4:36pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/8 "2021-11-16T16:36:35Z")
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… ---
**Author:** ![jkirchman](https://avatars.discourse-cdn.com/v4/letter/j/278dde/32.png) [@jkirchman](https://forum.inductiveautomation.com/u/jkirchman)\ **Post date:** [December 9, 2021, 4:43pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/9 "2021-12-09T16:43:32Z")
I had luck changing the data return format to json and all null values from the database show blank instead of ‘null’. ---
**Author:** ![JoeF](https://avatars.discourse-cdn.com/v4/letter/j/4da419/32.png) [@JoeF](https://forum.inductiveautomation.com/u/JoeF)\ **Post date:** [January 31, 2022, 3:06pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/10 "2022-01-31T15:06:30Z")
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. ---
**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\ **Post date:** [January 31, 2022, 3:25pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/11 "2022-01-31T15:25:44Z")
Thanks, but in my use case, columns are string, and with auto or string i obtain **“null”** (Ignition 8.1.14) ---
**Author:** ![Apurba\_Roy](https://avatars.discourse-cdn.com/v4/letter/a/e0b2c6/32.png) [@Apurba\_Roy](https://forum.inductiveautomation.com/u/Apurba_Roy)\ **Post date:** [May 17, 2022, 2:56pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/12 "2022-05-17T14:56:47Z")
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. ---
**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\ **Post date:** [June 30, 2022, 5:54pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/13 "2022-06-30T17:54:51Z")
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. ---
**Author:** ![jasoncoope](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jasoncoope/32/18674_2.png) [@jasoncoope](https://forum.inductiveautomation.com/u/jasoncoope)\ **Post date:** [October 14, 2022, 2:56pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/14 "2022-10-14T14:56:01Z")
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! ---
**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:** [October 14, 2022, 3:42pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/15 "2022-10-14T15:42:55Z")
You missed: 1. 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. ---
**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:** [October 14, 2022, 4:18pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/16 "2022-10-14T16:18:57Z")
I bumped the internal ticket a bit in priority, but it's sitting in a _large_ pile of work, so no timeline on implementation. ---
**Author:** ![jasoncoope](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jasoncoope/32/18674_2.png) [@jasoncoope](https://forum.inductiveautomation.com/u/jasoncoope)\ **Post date:** [October 15, 2022, 11:54pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/17 "2022-10-15T23:54:06Z")
I see your back working on your next award! ---
**Author:** ![ynejati](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ynejati/32/6093_2.png) [@ynejati](https://forum.inductiveautomation.com/u/ynejati)\ **Post date:** [January 10, 2023, 4:29pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/18 "2023-01-10T16:29:03Z")
FYI, this is being implemented now and should be available very soon. ---
**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\ **Post date:** [January 10, 2023, 6:12pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/19 "2023-01-10T18:12:34Z")
Thanks! Will update address this buggy behavior, or something else? > [@\[BUG\] Perspective Table number format breaks if first row has null value](https://forum.inductiveautomation.com/t/bug-perspective-table-number-format-breaks-if-first-row-has-null-value/44897): > > 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… ---
**Author:** ![ynejati](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ynejati/32/6093_2.png) [@ynejati](https://forum.inductiveautomation.com/u/ynejati)\ **Post date:** [January 10, 2023, 9:17pm UTC](https://forum.inductiveautomation.com/t/ign-2200-replace-values-displayed-as-null-in-the-perspective-table/45161/20 "2023-01-10T21:17:53Z")
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.