Handling NULLs from datasets for reports?

Ignition
1

I am trying to create a report from some alarm journal data.

Right now I have a script that generates the data to be used in the report that looks like this -

def generateAlarmDataForFullDay(day):
	"""
	Get's all alarms for a specified day and creates a report that is put into a mapped drive folder.
	Args:
		day: datetime object from a calendar component that will be used to determine what days to use in calculation
	Returns:
		DataSet - to be utilized by the reporting module
	"""
	from com.inductiveautomation.ignition.common.alarming.config import CommonAlarmProperties 
	beginningOfDay = system.date.midnight(day)
	endOfDay = system.date.midnight(system.date.addDays(day, 1))
	allAlarmData = system.alarm.queryJournal(startDate=beginningOfDay, endDate=endOfDay, includeSystem=False)
	headers = ['Alarm Occured At', 'Alarm Cleared At', 'Alarm Name', 'User Logged In']
	rows = []
	for alarmRecord in allAlarmData:
		alarmOccuredAt = alarmRecord.get(CommonAlarmProperties.EventTime)
		alarmClearedAt = alarmRecord.get(CommonAlarmProperties.ActiveTime)
		alarmName = alarmRecord.getName()
		if alarmClearedAt is None:
			username = None
		else:
			getUserQuery = "SELECT UserName, EventName, TimeStamp FROM event_log WHERE TimeStamp<? AND EventName IN ('Login','Logout') ORDER BY TimeStamp DESC LIMIT 1"
			userData = system.db.runPrepQuery(getUserQuery, [alarmClearedAt])
			for (username, eventName, TimeStamp) in userData:
				if eventName == 'Logout':
					userLoggedIn = "someone logged out before"
				else:
					userLoggedIn = username
#				userLoggedIn = username + " logged in at " + str(TimeStamp)
#		userLoggedIn = alarmRecord.get(CommonAlarmProperties.AckUserName)
		rows.append([alarmOccuredAt, alarmClearedAt, alarmName, userLoggedIn])
	dataset = system.dataset.toDataSet(headers, rows)
	return dataset

The issue is arising that sometimes my alarmClearedAt is sometimes a None/Null type. The raw rows data looks like

>>> for (start, clear, name, user) in data:
...     print start, type(start), clear, type(clear), name, type(name), user, type(user)
... 
Tue Jun 07 09:07:19 EDT 2022 <type 'java.util.Date'> Tue Jun 07 09:07:19 EDT 2022 <type 'java.util.Date'> Safety Relay Opened <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:16:50 EDT 2022 <type 'java.util.Date'> None <type 'NoneType'> Safety Relay Opened <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:17:00 EDT 2022 <type 'java.util.Date'> Tue Jun 07 09:17:00 EDT 2022 <type 'java.util.Date'> Right Door Open <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:17:00 EDT 2022 <type 'java.util.Date'> Tue Jun 07 09:17:00 EDT 2022 <type 'java.util.Date'> Safety Relay Opened <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:17:04 EDT 2022 <type 'java.util.Date'> None <type 'NoneType'> Right Door Open <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:17:04 EDT 2022 <type 'java.util.Date'> None <type 'NoneType'> Safety Relay Opened <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:24:24 EDT 2022 <type 'java.util.Date'> Tue Jun 07 09:24:24 EDT 2022 <type 'java.util.Date'> Safety Relay Opened <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:24:24 EDT 2022 <type 'java.util.Date'> Tue Jun 07 09:24:24 EDT 2022 <type 'java.util.Date'> HMI E-Stop Pressed <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:24:39 EDT 2022 <type 'java.util.Date'> Tue Jun 07 09:24:39 EDT 2022 <type 'java.util.Date'> Left Door Open <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:25:17 EDT 2022 <type 'java.util.Date'> None <type 'NoneType'> HMI E-Stop Pressed <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:25:25 EDT 2022 <type 'java.util.Date'> None <type 'NoneType'> Safety Relay Opened <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:25:25 EDT 2022 <type 'java.util.Date'> None <type 'NoneType'> Left Door Open <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:26:30 EDT 2022 <type 'java.util.Date'> Tue Jun 07 09:26:30 EDT 2022 <type 'java.util.Date'> Safety Relay Opened <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:26:30 EDT 2022 <type 'java.util.Date'> Tue Jun 07 09:26:30 EDT 2022 <type 'java.util.Date'> HMI E-Stop Pressed <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:26:37 EDT 2022 <type 'java.util.Date'> None <type 'NoneType'> HMI E-Stop Pressed <type 'unicode'> Administrator <type 'unicode'>
Tue Jun 07 09:26:38 EDT 2022 <type 'java.util.Date'> None <type 'NoneType'> Safety Relay Opened <type 'unicode'> Administrator <type 'unicode'>

But apparently the dataset does not like None/Nulls. I keep getting the error

WARN: Error invoking script.Traceback (most recent call last):
  File "<function:updateData>", line 2, in updateData
  File "<module:reporting.alarms>", line 38, in generateAlarmDataForFullDay
TypeError: Unable to convert row 1, column 1 to type class java.util.Date

Which you can see in row 1 -
Tue Jun 07 09:16:50 EDT 2022 <type 'java.util.Date'> None <type 'NoneType'> Safety Relay Opened <type 'unicode'> Administrator <type 'unicode'>

How should I handle the event where I need to allow a NULL in a dataset for a datetime column?

2

That’s weird, I tested in a script console and it worked.
image

1 Like
3

Disregard that, it is working for me now too, I think perhaps the report preview just didn’t grab the newest copy of the script. Weird. It definitely seemed to cause me issues for a bit.

I am looking at the report now though and the column looks like this
image

I am trying to use a KeyChain expression to write something else in the event of a <N/A>. Any ideas?

I tried
@Alarm Cleared At==None?:"Not Found":Alarm Cleared At@
which almost works, it’s clearly evaluating to true or false but not taking my string value or fallback to the actual value -
image

Any ideas?

4

I think you have an extra :
try

@Alarm Cleared At==None?"Not Found":Alarm Cleared At@
5

I needed to do this -
image

with no keychain expression required. Forgot about the Null Format property. Everytime I use the reporting module I have to relearn it, its so few and far between lol.

3 Likes
6

I didn’t even know that existed, neat :face_with_monocle: