Filter Dataverse for GUID not in a list

[GilbertS]

Hi,

I have records in my Dataverse table which can have state 'Concept' or state 'Final'. If you want to change the record with state Final my PowerApp creates a duplicate of this record. So the record with state Final remains as is. A new record is created with state Concept copying all fields and include the changes. Next to that the new concept record also stores a CopyOf field pointing to the original final record. This is working perfect.

But now I want to create a flow to fetch all final records unless there is a concept record available for this final record.

What I created in my flow:

1. Retrieve all concept records.

2. Store all CopyOf values in a variable (for instance: 'd7dac597-41f2-ec11-bb3d-0022489eb857', '6de93a6b-1ff2-ec11-bb3d-0022489eb857')

3. Retrieve all concept records and all final records where id of the record is not in the variable.

My filter is:

(_cr438_versie_id_value eq 'd757b16f-85d6-ec11-a7b5-0022489de7d3') or (_cr438_versie_id_value eq 'e257b16f-85d6-ec11-a7b5-0022489de7d3' and cr438_tblwerkervaringid in (@{variables('ConceptWerkervaringReeks')}) eq false)

However this is not working. The flow is doing several attempts to fetch the data. But finally it fails with:

Bad Gateway: "Index was outside the bounds of the array."

I drilled down that also the filter IN is not working. I tested the filter with:

cr438_tblwerkervaringid in ('d7dac597-41f2-ec11-bb3d-0022489eb857', '6de93a6b-1ff2-ec11-bb3d-0022489eb857')

There I bypassed the NOT IN and the use of variables. But that also is not working. It fails with:

Bad Gateway: "The method or operation is not implemented."

Can someone indicate to me how to filter records from the dataverse where I can exclude certain records based on several id's?

I want to keep the filtering in one go, since I order the records in a complicated way. And if I have to split the filtering and combine it later on, it is hard to get the order as wanted.

[eliotcole]

Hi, @GilbertS, what would be great is if you could add the following into your original question, here:

1. Some screenshots of your flow.
2. Paste copies of any expressions into code ( the </> button 😉 ) blocks.
3. Some sample data. (optional but helpful)

This will really help folks get a true idea of what's going on here, and how they might assist you better. Just obfuscate (erase or obscure) any sensitive data in the images and replace it in the sample data

All that said, hopefully this first attempt of a more general nature can help get you where you need to be ... keep testing and trying, though:

1. Filter Copies - Create a Filter action on the table named Filter Copies
   • In the filter condition, tap advanced, remove everything there, paste this in:

     @and(
         not(
             empty(
                 item()?['CopyOf']
             )
         ), 
         not(
             equals(
                 item()?['CopyOf'], 
                 null
             )
         )
     )

2. Select Copies - Create a Select action on that filter named Select Copies and in this action
   • From - Use Filter Copies here.
   • Map - Tap the little 'T' to the right to make it one big field and use this expression:

     item()?['CopyOf']

3. Filter Finals - Finally, use a basic filter on the original table this time.
   1. Left - In the left section place the Select Copies dynamic value
   2. Middle - Choose 'contains' in the middle, condition, section
   3. Right - In the right either choose the field which CopyOf corresponds to from the table column list or place it in an expression of item()?['COLUMN_NAME'] where COLUMN_NAME is the name of the column that Copy of references.

Now, I may be off, here, but hopefully that should provide a list of items which have the 'Final' status, but do not have copies, and it should run relatively quickly.

You may just want to use this in the Filter Finals in advance mode, which returns true (ie. includes the item) if the COLUMN_NAME value is included in that list of Select Copies that you made:

@contains(body('Select_Copies'), item()?['COLUMN_NAME'])

[GilbertS · Accepted solution]

Thanks for the suggestion. I was already able to solve it differently. I created a variable which I built up during the check of concepts. It starts with And id ne FieldCopyOfId. In the final retrieve for all records I just put in this variable, which can contain a large list of And id ne .... But it is now working.

Thanks!