How to collect values if and only if they will be unique in the destination collection

[MattR]

Scenario: I am gathering records into a collection. I want to add new records to the collection if and only if they will be unique in that collection. Ideally I would be able to specify the column(s) that would be checked for uniqueness (i.e. ID, or FirstName and PhoneNumber, etc.)

Example:
I have a collection that contains three records with one 'Name' column:  {Joe, Bob, Sally}

I have a set of people gathered based on membership in teams: {Steve, Rebecca, Joe, Steve}

There are duplicate values between the two collections, and in the second collection.

I would like to evaluate as follows:

TempCollection: {Steve, Rebecca, Joe, Steve}

MasterCollection: {Joe, Bob, Sally}

CollectUnique(MasterCollection, TempCollection (,Formula i.e. 'Name' to evaluate based on Name column))

------Steve is collected as he is unique in the destination

------Rebecca is collected as she is unique in the destination

------Joe is not collected as a record with the Name value of 'Joe' exists in the destination

------Steve is not collected as a record with the Name value of 'Steve' exists in the destination

The final values would be:

MasterCollection: {Joe, Bob, Sally, Steve, Rebecca}

Does this functionality exist in a different form? Does anyone know of an easy way to collect only unique/distinct records into a collection? I currently use a messy set of ForAll(), IsEmpty(), Filter(), Search(), Collect() to accomplish this objective but it has made some formulas long and difficult to interpret.

[v-yamao-msft]

Hi MattR,

When creating a collection, I am using this function to stop creating duplicate records in the collection.

The function I am using is: If(Not(TextInput1.Text in test),Collect(test,{name:TextInput1.Text}))

When a name has been already existing in the collection, it won’t be saved again tough I click the button. Hope this could be a reference for you.

Please feel free reply if you need more help.

Best regards,
Mabel Mao

[MattR]

Hi Mabel,

This sounds like it works great for single-column collections. How would it work for a multi-column collection?

For example, the value of 'Name' might be duplicate but the value of 'status' and 'note' might be different. I want to collect if and only if the 'Name' is unique, but I don't care if the status and note are the same or different values from what exists.

[SoManyQuestions · Accepted solution]

Stumbled upon your issue while working on my App. Hope you have found your answer already, but for the others who are having the same problem.

I think you will have to build two Collect formulas (one for each collection to add to uniquecollection). When working with multiple collumns simply add a "." after the collection name in order to specify in which column PowerApps has to look. For example:

Collection1.ID in Collection2.ID

[juresti]

Hello,

I ran into this post before I realized I had implemented a solution for entering unique values into collection.

This is what I did.

If(IsEmpty(Filter(collectionTable,employeeID = ddlEmployees.cboEmployeesOut.eid))
,Collect(collectionTable,

    .... collect colums ... 

  )

) // end if

What I did is first I filtered the collection to check if the record exists. You can check on multiple columns for the filter criteria giving you a the ability to match every single column.  

So if it returns empty that means the record is not in the table.

And I think you can put this in a ForAll loop.