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
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!