Hello, I'm reading a record that contains a text field I'd like to have listed in an array. However, the array should only have unique values, so if that text is already in there, I don't need to add it again. I've written this.
a!localVariables( local!arrayOfMakes, a!forEach( items: a!queryRecordType( recordtype: recordType!KD VM Vehicle, fields: recordType!KD VM Vehicle.fields.make, pagingInfo: a!pagingInfo( startindex: 1, batchSize: 50) ).data[recordType!KD VM Vehicle.fields.make], expression: if( contains(local!arrayOfMakes, fv!item), null, append(local!arrayOfMakes, fv!item) ) ) )
It returns what I think I need, but it's not unique. Here's part of the output. Further down you would see that it has several entries for the same make.
All ideas are appreciated!
Discussion posts and replies are publicly visible
You can use the union function to get unique values from the array instead of using an expression in foreach.
i.e. save results of the a!queryRecordType in a local and use union() on this result.
https://docs.appian.com/suite/help/21.3/fnc_set_union.html
union(local!arrayOfMakes, local!arrayOfMakes)
Further, I always recommend making a generic high-level helper rule for your entire system to use that takes a value and unions it against itself. An example from the old common objects pack was rule!APN_Distinct(), which takes any array and returns a de-duplicated copy of that array.
Thanks for all the help! Here's the final code for anyone struggling in the future.
a!localVariables( local!arrayOfMakes: a!queryRecordType( recordtype: KD VM Vehicle, fields: KD VM Vehicle.fields.make, pagingInfo: a!pagingInfo( startindex: 1, batchSize: 50) ).data[KD VM Vehicle.make], union(local!arrayOfMakes, local!arrayOfMakes) )
A better way to do this than using the union() function is to group your data so that it only returns unique values in the query. The key difference is that currently you're still querying all the results and then having Appian remove duplicates after the query. However, if you use a grouping directly in your query, you ask the database to only return the unique values. Here's an example:
a!queryRecordType( recordtype: recordType!KD VM Vehicle, fields: { a!grouping( field: recordType!KD VM Vehicle.fields.make, alias: "make" ) }, pagingInfo: a!pagingInfo( startindex: 1, batchSize: 50 ) )
This looks good, though I think you may have dropped an a!aggregationFields() somewhere.
Ah thanks Mike, that's why I shouldn't write my code directly in the box :) Here's what it should look like:
a!queryRecordType( recordtype: recordType!KD VM Vehicle, fields: a!aggregationFields( groupings: { a!grouping( field: recordType!KD VM Vehicle.fields.make, alias: "make" ) } ), pagingInfo: a!pagingInfo( startindex: 1, batchSize: 50 ) )
this is great and way more efficient, thank you!
For anyone reading this thread, there is a known performance issue when the array is large (at least there was). If you stumble upon that issue, you can do something like
union(local!arrayOfMakes, cast(typeof(local!arrayOfMakes), {}))
Do you have any idea how large an array has to get before that gets to be an issue? I've seen utility expression rules follow your pattern, but I've never been able to find any test cases where that pattern works better than "union(ri!array, ri!array)"...
purely anecdotal, but 5k elements where each element is like 10+ characters. I haven't ever really done load testing to find a point where you start to see significant differences.