# Handling null values

**URL:** <https://forum.inductiveautomation.com/t/handling-null-values/90094>\
**Category:** Ignition\
**Created:** [May 8, 2024, 12:38pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094 "2024-05-08T12:38:47Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Aisha\_Shiddat](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@Aisha\_Shiddat](https://forum.inductiveautomation.com/u/Aisha_Shiddat)\
**Post date:** [May 8, 2024, 12:38pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/1 "2024-05-08T12:38:47Z")

</div>

I am still getting null in certain text fields for the values, even though I wrote some code to handle this. Here is a snippet of the code. Sometimes the field will show N/A when there is a null value and at other times it shows "null". Should I use a try except block instead?

value = round(tableanalyses\_data[0])  
if value == 0 or None:  
return "NA"  
return value

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 8, 2024, 12:40pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/2 "2024-05-08T12:40:16Z")

</div>

Please see [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831) and then hit the pencil icon below your post to fix it. Thanks.

---

<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:** [May 8, 2024, 12:41pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/3 "2024-05-08T12:41:32Z")

</div>

> [@Aisha\_Shiddat](#):
>
> `value == 0 or None`

The second half of this expression will never be true. I suspect you want

```python
if value == 0 or value is None:

```

Or, more compactly:

```python
if not value:

```

---

<div class="post-metadata">

**Author:** ![Aisha\_Shiddat](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@Aisha\_Shiddat](https://forum.inductiveautomation.com/u/Aisha_Shiddat)\
**Post date:** [May 8, 2024, 1:03pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/4 "2024-05-08T13:03:46Z")

</div>

I tried that also and I am still getting an index out of bounds error, which is when the "null" shows up as my text value. The script is telling me on line 32 which is where it shows this line of code.  
(value = round(tableanalyses\_data[0])  
. I'm using range() and length() in my for loop so im not understanding why I keep getting this error.

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

#Create Params
	thermal_params = {"measure_loc":self.view.getChild("root").custom.location}
		
		
	#Read db and convert to PyDataSet
	thermalanalyses_table = system.db.runNamedQuery("LastAtasData_SI_Chill",thermal_params)
	thermal_tableOutput = system.dataset.toPyDataSet(thermalanalyses_table)
	
	
	#Create an empty list, to house our thermalanalyses data.
	tableanalyses_data = []
	for row in range(len(thermal_tableOutput)):
		equationResults = {
			"morphologytype": thermal_tableOutput[row]["morphologytype"],
			"Chill-Eut": thermal_tableOutput[row]["Chill-Eut"],
			"Chill-Hypo": thermal_tableOutput[row]["Chill-Hypo"]
			
		}
	
	#tableanalyses_data.append(equationResults["morphologytype"])
	#create case statement here that cross references Silicon-Hyper, Silicon-Eut and Silicon-Hypo:
		
		if equationResults["morphologytype"] == 3:
				tableanalyses_data.append(equationResults["Chill-Eut"])
			
		elif equationResults["morphologytype"] == 1:
				tableanalyses_data.append(equationResults["Chill-Hypo"])
		
	
	value = round(tableanalyses_data[0])
	if value == 0 or None:
	   return "NA"
	return value

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 8, 2024, 1:17pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/5 "2024-05-08T13:17:38Z")

</div>

Add line  
`return tableanalyses_data`  
in line 31 to see if you've got any actual data.

Fix line 33 as Phil recommended. Yours won't work.

Please fix your original post.

---

<div class="post-metadata">

**Author:** ![Aisha\_Shiddat](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@Aisha\_Shiddat](https://forum.inductiveautomation.com/u/Aisha_Shiddat)\
**Post date:** [May 8, 2024, 1:37pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/6 "2024-05-08T13:37:15Z")

</div>

There is no data being returned when I add that line. Just an empty list. Also when I run my Named Query there is a null at the moment for this particular value. So the text value should display "NA" not "null".

I updated the value in line 33 to -  
`if value == 0 or value is None:`

but still getting the null value

---

<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:** [May 8, 2024, 1:39pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/7 "2024-05-08T13:39:42Z")

</div>

Are you sure you don't have "null" as a string in there? Could some upstream transform have stringified your values?

---

<div class="post-metadata">

**Author:** ![Aisha\_Shiddat](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@Aisha\_Shiddat](https://forum.inductiveautomation.com/u/Aisha_Shiddat)\
**Post date:** [May 8, 2024, 2:04pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/8 "2024-05-08T14:04:41Z")

</div>

Not from what I see. What I noticed is that when I used the return statement above this block of code to test what was being returned from tableanalyses\_data list; I saw that the NA will show when there is a 0.0 float value, but not when there is null.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 8, 2024, 2:16pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/9 "2024-05-08T14:16:59Z")

</div>

> [@Aisha\_Shiddat](#):
>
> 0.0 float value, but not when there is null.

I find this odd, because you are using `round()` which if passed a string returns a type error, so in that event I wouldn't expect the script to run to completion.

In perspective, if a transform results in an error, the value of that property will read as 'null'.

Can you provide a screen shot of the binding with this transform in it? I strongly suspect that you're getting an error.

---

<div class="post-metadata">

**Author:** ![Aisha\_Shiddat](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@Aisha\_Shiddat](https://forum.inductiveautomation.com/u/Aisha_Shiddat)\
**Post date:** [May 8, 2024, 2:26pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/10 "2024-05-08T14:26:01Z")

</div>

![Screenshot_QueryBinding_2024-05-08](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/9/192808358c042837c130cb6b7348b99462527fdb.png)

below is the transform:

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

#Create Params
	thermal_params = {"measure_loc":self.view.getChild("root").custom.location}
		
		
	#Read db and convert to PyDataSet
	thermalanalyses_table = system.db.runNamedQuery("LastAtasData_SI_Chill",thermal_params)
	thermal_tableOutput = system.dataset.toPyDataSet(thermalanalyses_table)
	
	
	#Create an empty list, to house our thermalanalyses data.
	tableanalyses_data = []
	for row in range(len(thermal_tableOutput)):
		equationResults = {
			"morphologytype": thermal_tableOutput[row]["morphologytype"],
			"Chill-Eut": thermal_tableOutput[row]["Chill-Eut"],
			"Chill-Hypo": thermal_tableOutput[row]["Chill-Hypo"]
			
		}
	
	#tableanalyses_data.append(equationResults["morphologytype"])
	#create case statement here that cross references Silicon-Hyper, Silicon-Eut and Silicon-Hypo:
		
		if equationResults["morphologytype"] == 3:
				tableanalyses_data.append(equationResults["Chill-Eut"])
			
		elif equationResults["morphologytype"] == 1:
				tableanalyses_data.append(equationResults["Chill-Hypo"])
		
		
	value = round(tableanalyses_data[0])
	if value == 0 or value is None:
		return "NA"
	return value

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 8, 2024, 2:34pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/11 "2024-05-08T14:34:01Z")

</div>

Notice at the bottom, under the Binding Preview it shows the result of the Query, and then the result of the Script. The result of the script says `Error_ScriptEval`. If you hover over that it will show you a message informing you what the error is.

Most likely something along the lines of `IndexError: index out of range: 0`

You are not properly guarding your attempt to access the list. If the list is empty attempting to access the item at 0 will result in an Index Error.

Try this after the loop instead:

```python
if tableanalyses_data:
	return round(tableanalyses_data[0])
return "NA"

```

As an added bonus, you can change your loop to this:

```python
for row in thermal_tableOutput:
		if row["morphologytype"] == 3:
				tableanalyses_data.append(row["Chill-Eut"])
			
		elif row["morphologytype"] == 1:
				tableanalyses_data.append(row["Chill-Hypo"])

```

---

<div class="post-metadata">

**Author:** ![Aisha\_Shiddat](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@Aisha\_Shiddat](https://forum.inductiveautomation.com/u/Aisha_Shiddat)\
**Post date:** [May 14, 2024, 4:12pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/12 "2024-05-14T16:12:14Z")

</div>

I am now getting an error that states "Type Error: A float is required" if there is no value. In the last block of code where I am returning the table data results:

return round(tableanalyses\_data[0])

I have tried wrapping this in a float function with no luck....

```python
#Create Params
	thermal_params = {"measure_loc":self.view.getChild("root").custom.location}
		
		
	#Read db and convert to PyDataSet
	thermalanalyses_table = system.db.runNamedQuery("LastAtasData_SI_Chill",thermal_params)
	thermal_tableOutput = system.dataset.toPyDataSet(thermalanalyses_table)
	
	
	#Create an empty list, to house our thermalanalyses data.
	tableanalyses_data = []
	for row in range(len(thermal_tableOutput)):
		equationResults = {
			"morphologytype": thermal_tableOutput[row]["morphologytype"],
			"Chill-Eut": thermal_tableOutput[row]["Chill-Eut"],
			"Chill-Hypo": thermal_tableOutput[row]["Chill-Hypo"]
			
		}
	
	#tableanalyses_data.append(equationResults["morphologytype"])
	#create case statement here that cross references Silicon-Hyper, Silicon-Eut and Silicon-Hypo:
		
		if equationResults["morphologytype"] == 3:
				tableanalyses_data.append(equationResults["Chill-Eut"])
			
		elif equationResults["morphologytype"] == 1:
				tableanalyses_data.append(equationResults["Chill-Hypo"])
		
	if tableanalyses_data:
		return round(tableanalyses_data[0])
	elif tableanalyses_data == 0 or tableanalyses_data is None:
		return "NA"

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 14, 2024, 5:38pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/13 "2024-05-14T17:38:04Z")

</div>

What are the types of `"Chill-Eut"` and `"Chill-Hypo"` in the database?

Since this result is going to be displayed in a text field. This is the appropriate place to do the string formatting. So, perhaps rather than rounding it you do some type of Format operation. Really depends on how this data is being stored.

Hint: If it's being stored as a string and you expect it to be a number, then you need to change something.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 14, 2024, 5:49pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/14 "2024-05-14T17:49:51Z")

</div>

You're not using the results of the binding anywhere in that transform, which is HIGHLY suspicious.  
What are you trying to do ?

---

<div class="post-metadata">

**Author:** ![Aisha\_Shiddat](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@Aisha\_Shiddat](https://forum.inductiveautomation.com/u/Aisha_Shiddat)\
**Post date:** [May 14, 2024, 10:31pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/16 "2024-05-14T22:31:30Z")

</div>

The SQL query is posted above. I am cross-referencing columns that have integers in a specific position in one column, that coincide with a float value in another column.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 14, 2024, 10:36pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/17 "2024-05-14T22:36:28Z")

</div>

You're running the same named query twice, once as a query binding, and then in the script:

> [@Aisha\_Shiddat](#):
>
> ```python
> thermalanalyses_table = system.db.runNamedQuery("LastAtasData_SI_Chill",thermal_params)
> 
> ```

You should remove that call and use the results of the binding

```python
def transform(self, value, quality, timestamp):
	tableanalyses_data = []
	for row in system.dataset.toPyDataSet(value):
		if row["morphologytype"] == 3:
			tableanalyses_data.append(row["Chill-Eut"])
		elif row["morphologytype"] == 1:
			tableanalyses_data.append(row["Chill-Hypo"])

	if tableanalyses_data:
		return round(tableanalyses_data[0])
	return "NA"

```

Though I'm not quite sure what the whole thing is about if you're only using the first row in your return. Which is why I asked what you're trying to do.

---

<div class="post-metadata">

**Author:** ![Aisha\_Shiddat](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@Aisha\_Shiddat](https://forum.inductiveautomation.com/u/Aisha_Shiddat)\
**Post date:** [May 14, 2024, 10:42pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/18 "2024-05-14T22:42:32Z")

</div>

If the result from the query is 0 or null there should be "NA". I don't want the text box to show "null". I tried it this way also and I still get the null in the text component if there is a null value coming from the query result.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 14, 2024, 10:55pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/19 "2024-05-14T22:55:39Z")

</div>

Then I would say it means the value in the first row is a Null, but then `round` should raise an exception...

I suggest you put a table on your page, and bind it's data props to the same named query, without a transform, and check in that table that everything looks like what you expect.

---

<div class="post-metadata">

**Author:** ![Aisha\_Shiddat](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@Aisha\_Shiddat](https://forum.inductiveautomation.com/u/Aisha_Shiddat)\
**Post date:** [May 15, 2024, 6:45pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/20 "2024-05-15T18:45:21Z")

</div>

I just used a try except block and cast my value to an integer instead of using the round function.

```python
value = tableanalyses_data[0]
	try:
		number_value = int(value)
		if number_value <= 0:
			return "NA"
	except Exception:
			return "NA"
	return int(value)

```

This worked

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 15, 2024, 6:48pm UTC](https://forum.inductiveautomation.com/t/handling-null-values/90094/21 "2024-05-15T18:48:09Z")

</div>

I'll allow myself to insist and ask again: Why are you running a loop when you're only using the very first row ?

[Next page](https://forum.inductiveautomation.com/t/handling-null-values/90094.md?page=2)