Validate duplicate records with multi-choice field

[JOAS_Niels]

Hi all,

I'm using the below code to validate entries in a PowerApps form (on the OnSave event). This verifies if the entry is already existent in a SharePoint list.

With({matchingRecords:Filter(Teeltregistratie, Product.Value = SharePointForm1.Updates.Product.Value
                            And Leverancier.Value=SharePointForm1.Updates.Leverancier.Value
                            And Seizoen = SharePointForm1.Updates.Seizoen
                            )}
      ,
      If(IsEmpty(matchingRecords) Or 
                (CountRows(matchingRecords) = 1 And 
                 First(matchingRecords).ID = Value(Label2.Text)), 
          SubmitForm(SharePointForm1),
          Notify("Error - Er bestaat al een record met dezelfde waarden voor Product, Leverancier, Seizoen en Certificaat",NotificationType.Error)
      )
)

This seems to work. However I want to extend this code with a validation on a multi-choice field.

The field is called "certificate" and multiple certificates can be selected. How can I compare the multipe values selected with the values in the records of the SharePoint list. I've searched the web, but can't seem to find a similar use case.

Hope you can help.

[WarrenBelz]

Hi @JOAS_Niels ,

You seem to have a many-to-many relationship there is multiple certificates can be selected and written to  multiple-value field. Note the ???? below is the output of the combo box which will either be Value or the field name displayed.

With(
   {
      firstMatch:
      Filter(
         Teeltregistratie, 
         Product.Value = SharePointForm1.Updates.Product.Value And 
         Leverancier.Value = SharePointForm1.Updates.Leverancier.Value And 
         Seizoen = SharePointForm1.Updates.Seizoen
      )
   },
   With(
      {
         matchingRecords:
         Ungroup(
            ForAll(
               YourCertificateCombo.SelectedItems As aCert,
               Filter(
                  firstMatch,
                  aCert.???? in Certificate.Value
               )
            ),
            "Value"
         )
      },
      If(
         IsEmpty(matchingRecords) Or 
         (
            CountRows(matchingRecords) = 1 And 
            First(matchingRecords).ID = Value(Label2.Text)
         ), 
         SubmitForm(SharePointForm1),
         Notify(
            "Error - A record already exists with the same values ​​for Product, Vendor, Season, and Certificate",
            NotificationType.Error
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[JOAS_Niels]

Thanks, Warren. This is almost what I wanted. My fault for not being entirely clear.

I would like to give an error only when the second record is a complete duplicate, so all of the selected items (cerfificates) should match the other record. Now, the code gives me an error when at least one certificate matches.

To give an example. 

Supplier1 has a crop with certificates: Cert1 and Cert2

Supplier1 has an other crop with certificates: Cert1 only

The current code gives me a duplicate error in this situation, but this should be possible.

Is this possible?

[WarrenBelz]

Hi @JOAS_Niels ,

I simply addressed the many to many filter issue assuming as you noted that the rest was working. I do not fully understand your model, but what has changed with the extra filter that is not working now ?

[JOAS_Niels]

Hi @WarrenBelz ,

The extra filter prohibits me to create almost similar records. See this example:

Supplier	Product	Certificates	Field (not part of key/validation)
Supplier1	Potatoes	BIO, SKAL	Field 1
Supplier1	Potatoes	BIO	Field 2

These records are not possible with the extra filter, but should. When I create the second record the code gives me the error that it's a duplicate, because of the "BIO" certificate. 

The field column is just to make the example a bit more clear, this should not be part of the validation to determine duplicates.

Hope this makes more sense now.

[WarrenBelz]

Hi @JOAS_Niels ,

Are you saying you want all selected items to match all field values exactly ?

[JOAS_Niels]

Hi @WarrenBelz ,

Yes. Only then the error should be shown. Not when there is only one item match and there are more items/certificates.

[WarrenBelz]

Hi @JOAS_Niels ,

If I have interpreted this correctly, you want both selection sets to be exactly the same - if they are in the same order, this will work

With(
   {
      firstMatch:
      Filter(
         Teeltregistratie, 
         Product.Value = SharePointForm1.Updates.Product.Value And 
         Leverancier.Value = SharePointForm1.Updates.Leverancier.Value And 
         Seizoen = SharePointForm1.Updates.Seizoen
      )
   },
   With(
      {
         matchingRecords:
         Filter(
            firstMatch, 
            Concat(
               YourCertificateCombo.SelectedItems,
               ????,
               ","
            ) =                  
            Concat(
               Certificate,
               Value,
               ","
            )                    
         )
      },
      If(
         IsEmpty(matchingRecords) Or 
         (
            CountRows(matchingRecords) = 1 And 
            First(matchingRecords).ID = Value(Label2.Text)
         ), 
         SubmitForm(SharePointForm1),
         Notify(
            "Error - A record already exists with the same values ​​for Product, Vendor, Season, and Certificate",
            NotificationType.Error
         )
      )
   )
)

but out of order hurts my brain thinking about it.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[JOAS_Niels]

Thanks @WarrenBelz ! I understand the complexity, this is a real challenge.

This is however one step closer to the solution. I hope somebody else can top this, but maybe I'm asking too much.

Really appreciate the effort Warren, this is better than we had.

[WarrenBelz]

Hi @JOAS_Niels ,

I cannot currently think of an effective way of doing this, but I believe you need to consider how to test each record to see if the number of matches with the selected items equals the number of items selected in the Combo Box.