cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rht
Regular Visitor

How to check for duplicate records in collection .

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 CollectionScreenshot of SysID lookupScreenshot of SysID lookup

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
mdevaney
Super User
Super User

@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
Regular Visitor

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 .

 

@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
Regular Visitor

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
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xida-msft & @mdevaney ,

Thanks for response that solved my problem . 

anas_aitkenspen
Regular Visitor

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

 

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (4,035)