How to check for duplicate records in collection .

[Rht]

I have a collection which contains 2 columns SystemName(single line of text column) and SystemId (Lookup column). I need to check weather there are duplicate records present in these 2 columns combined on a button click. Kindly suggest a formula .



Screenshot of CollectionScreenshot of SysID lookup

[mdevaney]

@Rht 
I can likely help here, but first:

Can you please supply your code used to combine values combined on a button click?

Can you please show a scenario where there are duplicate values stored being in the collection?  I don't necessarily know what we are comparing.  Additional screenshots would help.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Rht]

Hi @mdevaney 

We are using 2 SharePoint list at the back end . First is master list for All systems and second is Details list . Details list contains a lookup column of systems list ID . By using New & Edit form data is added/edited in details list . We are using a gallery control to add repetitive data in collection which uses SystemName . On new form we are saving look up value of system name in details list . But for edit mode we are getting SysId as  lookup value of existing data . So in order to add multiple systems in gallery , i need to check whether there is duplicate records present in SysId & SystemName combine.
On a button click i need to check if there is any duplicate record in gallery . 

Kindly find attached screenshots of my SharePoint list and collection .

[mdevaney]

@Rht 
From reading your post your goal seems to be:

"I need to check whether there is duplicate records present in SysId & SystemName combined. On a button click i need to check if there is any duplicate record in gallery."

I believe you want me to find duplicate items in your gallery.  Not to check whether they already exist in a SharePoint list.

Please show me these two things:

• An example of your gallery with at least three items.  There should be at least one duplicate line-item.
• Show me what the gallery looks like in a collection so I can understand the data it holds.  Use the code below:

ClearCollect(myGalleryItems, Gallery1.AllItems)

The reason I am asking more questions is your response did contain the items I wanted to know in my initial reply.  Please provide what I am looking for.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Rht]

Hi mdevaney 

Please find attached duplicate record view of collection . In attached collection , first record (SysID) is a lookup column which has lookup value for Acti-Time (i.e. Id=2 & Value=SYS002) . Second record is  a non duplicate record . Third record is a duplicate record with SystemName is "Acti-Time" . So basically first record and third record are duplicate records and second record is non duplicate record .
Thanks for help in advance .

[v-xida-msft · Accepted solution]

Hi @Rht ,

Do you mean that the SysID field with SYS002 value and the SystemName field with "Acti-Time" value represents same record?

Do you want to check if there are duplicate records within your collection on pressing the button?

Based on the needs that you mentioned, I think the AddColumns function could achieve your needs. Set the OnSelect property of the button to following:

Clear(DuplicatedRecords);
ForAll(
    GroupBy(
            AddColumns(
                   CollectionTable,   // Replace it with actual collection name in your app
                   "SysName",
                   If(
                      !IsBlank(LookUp(SystemList, SysID = CollectionTable[@SysID].Value, SysName)),
                       LookUp(SystemList, SysID = CollectionTable[@SysID].Value, SysName),
                       CollectionTable[@SystemName]
                   )
            ),
            "SysName",
            "GroupData"
     ),
     If(
         CountRows(GroupData) > 1,
         Collect(DuplicatedRecords, SysName)
     )
);
If(
   !IsEmpty(DuplicatedRecords),
   Notify("There are some duplicate records in your collection, the duplicate SystemName are : " & Concat(DuplicatedRecords, Value & "; "), NotificationType.Error)
)

Note: The CollectionTable represents the collection that you mentioned, which is used to store your SysID (LookUp field) and SystemName field. Please replace it with actual collection name in above formula.

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[Rht]

Hi @v-xida-msft & @mdevaney ,

Thanks for response that solved my problem . 

[anas_aitkenspen]

Hi @Rht,

You can also use a simple formula as below to check whether there are duplicate records in your collection.

If(CountRows(Distinct(CollectionName,SystemName))<>CountRows(CollectionName),Notify("There are some duplicate records in your collection", NotificationType.Error))

I'm a newbie to the platform, appreciate it if you could comment. @v-xida-msft @mdevaney 

Thanks and regards,

Anas Lanthra