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 ,

I gave my brain an overnight rest (7:40 am here) - have a go at this (although I cannot test it) - I have assumed .Value as the output of your Combo Box - change if incorrect

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(
            Filter(
               ForAll(
                  YourCertificateCombo.SelectedItems As aCert,
                  Filter(
                     firstMatch,
                     aCert.Value in Certificate.Value
                  )
               ),
               CountRows(Value) = CountRows(YourCertificateCombo.SelectedItems)
            ),
            "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]

Hi @WarrenBelz,

I don't know why, but this doesn't work. I can create duplicate records now.

[WarrenBelz]

Hi @JOAS_Niels ,

Yes - I see it now - the groups I am trying to count are "backwards" counting the matches on the combo box rather than the field - again this is untested free-typed code, but try this

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(
            Filter(
               ForAll(
                  firstMatch.Certificate As aCert,
				  Filter(
                     firstMatch,
                     aCert.Value in YourCertificateCombo.SelectedItems
                  )
               ),
               CountRows(Value) = CountRows(YourCertificateCombo.SelectedItems)
            ),
            "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
         )
      )
   )
)

[JOAS_Niels]

Hi @WarrenBelz,

It gives me an error on the aCert.Value part. Do you know how to correct this?

[WarrenBelz]

@JOAS_Niels ,

Forget that one - it will not work for another reason, even if I manage to get it valid. If you go back to the first one, it actually should logically work (I was doubting myself when you said it did not), however I have now tested and the results were inconsistent, so I will have to raise the white flag here.

[JOAS_Niels]

Hi @WarrenBelz,

The results were indeed inconsistent, I couldn't find any logic in it. 

That's unfortunate, Warren. But you did your best. Maybe someone else comes to the rescue, but it is a real challenge.

I will go with the one that works if you keep the order the same, for now.

[JOAS_Niels]

Hi @WarrenBelz,

I found this blog post. He seems to do the same thing, but I don't understand it completely.

https://www.practicalpowerapps.com/data/many-to-many-filters/

Can we use his code to complete your solution?

[WarrenBelz]

Hi @JOAS_Niels ,

That is actually my site. The many to many is not the issue here as it is easy to get all records that match at least one of the choices. I have not completely given up on this as I was close with my first code. What has to be done for each record is find the number of matches compared with the number of choices 

[WarrenBelz · Accepted solution]

Hi @JOAS_Niels ,

There are times that the simple solution is starting at one in the face and they just keep going deeper and deeper down the rabbit hole looking for the complex one. Try this just Concat and Sort both Tables in order and compare them . . .

With(
   {
      firstMatch:
      Filter(
         Teeltregistratie, 
         Product.Value = SharePointForm1.Updates.Product.Value And 
         Leverancier.Value = SharePointForm1.Updates.Leverancier.Value And 
         Seizoen = SharePointForm1.Updates.Seizoen
      ),
      Chosen: 
      Concat(
         Sort(
            YourCertificateCombo.SelectedItems,
            Value
         ),
         Value,
         ","
      )
   },
   With(
      {
         matchingRecords:
         Filter(
            AddColumns(
               firstMatch,
               "Multi",
               Concat(
                  Sort(
                     Certificate,
                     Value
                  ),
                  Value,
                  ","
               )
            ),
            Multi = Chosen
         )
      },
	  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
         )
      )
   )
)

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]

Hi @WarrenBelz,

This is fabulous! I tested the different possibilities, identical certificates, 1 more certificate, 1 less certificate and a different order and it seems to work as intended.

Really impressive. I'm glad you're so stubborn and sorry for the headache 😉