Integration Toolkit Module - Dynamic Expressions

Ignition
, , ,
1

Hello, I am trying to build an expression using the functions provided by the Integration Toolkit module to filter large datasets, based on dynamic values in the "where()" function.

I have an array of words (split({../filterText.props.text},' '), and I need to return only rows where the dataset's "DESCRIPTION" contains all the words in the array. The array of keywords can be any length and can be any value. I understand the "where" function can have many conditions, so my thought was to build out as many conditions as there are words in the array using forEach()

The below syntax does not work, but it may convey what I am trying to do. It returns "No such column '0' in dataset"

where({value},
	forEach(split({../filterText.props.text},' ')
		,it()['DESCRIPTION'] like '%' + it()[1] + '%'
			)
	)

What might be a way to accomplish this?

2

This is because the it()['DESCRIPTION'] is trying to find a column named 'Description' in your list of split strings. Try changing that to it(1)['DESCRIPTION']

The number inside it() tells it what iterator depth to look at.

Your second it()[1] needs to be it()[0], otherwise you are attempting to grab a non-existent column.

Cleaned expression 
where(
	{value},
	forEach(
		split({../filterText.props.text}, ' '),
		it(1)['DESCRIPTION'] like '%' + it()[0] + '%'
	)
)

Aside from that, I'm not sure that like is a valid keyword in the expression language. Edit: Wow, didn't know that was a thing.

3

Unfortunately, the conditions must be explicit expressions--you cannot nest them in a list.

What you can do (though I haven't yet had a need) is use an inner where() with inverted conditions, and check the result for length==0. A somewhat convoluted way to make an analog to python's all() function.

1 Like
4

@ryan.white Thanks for the tip on getting the right depth. Those were confusing me. I did try the expression you posted and was getting "Cannot coerce value '[true]' into type: class Java.lang.Boolean", at which point I wrapped the forEach in a toBoolean() which got rid of the error but didn't yield any results. Based on @pturmel 's response it sounds like I will need to try a different approach. Thanks to you both!