Participating Frequently

Question

creating a unique list

Forum|Forum|4 years ago
November 5, 2021
10 replies
1 view

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!

expression rule

H

hrishikeshd997

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)

mikes0011

Brainy

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.

kimberlee.dayAuthor

Participating Frequently

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

joshl

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), {}))

mikes0011

Brainy

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)"...

peter.lewis

Employee

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

mikes0011

Brainy

This looks good, though I think you may have dropped an a!aggregationFields() somewhere.

peter.lewis

Employee

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

Sign up

Login with SSO

Login to the community

Login with SSO