Filtering using array and "not in" operator

What is the issue? What did you try? What are the results? What is the goal?

What is the issue? What did you try? What are the results? What is the goal?

stuaradr

I too have the same question as Stefan. But if the problem is with the code, then this might help

a!localVariables(
  /*
  * Run the "queryEntity()" function on "cons!TDW_CHANGE_REQ_DETAILS_DS" to retrieve data for the
  * first 50 data store entities and store this in a local variable named
  * "local!entities".
  */
  local!entities: a!queryEntity(
    entity: cons!TDW_CHANGE_REQ_DETAILS_DS,
    query: a!query(
      filter: a!queryFilter(
        field: "schemeId",
        operator: "not in",
        value: {
          70011,
          70012,
          70019,
          70306,
          70317,
          70326,
          70328,
          70359,
          70752,
          70924,
          71086,
          71241,
          71243,
          71245,
          71247,
          71249,
          71251,
          71257,
          71262,
          71263,
          71266
        }
      ),
      pagingInfo: a!pagingInfo(startIndex: 1, batchSize: - 1)
    ),
    fetchTotalCount: true
  ).data,
  /*
  * Construct an HTTP response that contains the information that we just stored
  * in "local!entities".
  */
  a!httpResponse(
    /*
    * Set an HTTP header that tells the client that the body of the response
    * will be JSON-encoded.
    */
    headers: {
      a!httpHeader(
        name: "Content-Type",
        value: "application/json"
      )
    },
    /*
    * JSON-encode the value of "local!entities" and place it in the response body.
    */
    body: a!toJson(value: local!entities)
  )
)

I have tried changing the operators and checking the brackets and I have verified the syntax of a!queryfilter. I am new to this so I am adapting existing code a little bit blind to be honest.

The aim is to remove those records which appear more than once in the columns of the cons!TDW_CHANGE_REQ_DETAILS_DS store. I have identified manually that those listed appear more than once so I was seeking to filter them out, however if there is a way to do so automatically that would be even better as I will not have to update the query going forward.

Thanks

This seems to have done the trick. I did wonder if and tried using {} for the list but there must have been some other issues with () and , etc. Thanks.

If you could help further with how I might achieve what I set out in my reply to Stefan, that would be great.

So, you want all lines, but only unique values for schemeId?

Try this to get started: docs.appian.com/.../Query_Recipes.html

Almost. If the count of schemeId >1 then remove that entry from the results. I have successfully used the aggregation example you suggested but don't know how to use the result in order to filter the lines as the result is an aggregated table, not the original one, filtered.

a!localVariables(
  local!schemes: {
    a!map(schemeId: 24355, count: 1),
    a!map(schemeId: 6764, count: 2),
    a!map(schemeId: 68745, count: 1),
    a!map(schemeId: 1256, count: 2),
  },
  index(
    local!schemes,
    where(local!schemes.count = 1),
    {}
  )
)