Comparing choice fields in two SharePoint lists

[Siddz]

In my canvas app, I have two SharePoint lists with identical choice columns (checkboxes that allow multiple selections). The column name in both lists is called "Favorite Fruit."

SharePoint List A

Name	Favorite Fruit
Jane	Apples
John	Oranges, pears
Janice	Apples, pears

SharePoint List B

Name	Favorite Fruit
Adam	Oranges
Alice	Apples, pears
Amanda	Apples, oranges

The goal:

I would like to compare the logged-in user's "Favorite Fruit" column in SharePoint List A with the same column in SharePoint List B, and display the matching rows/records in a gallery. For example, if Jane is the logged-in user, the gallery should display the records for Alice and Amanda because both Alice and Amanda selected "apples" in the "Favorite Fruit" column.

My code below only works if the "Favorite Fruit" column in both lists are exact matches. I would like to return all records containing the logged-in user's fruit choices (in this example, that would be any records from SharePoint List B that contains "apples").

Button > OnSelect property:

ClearCollect(
     colSharePointListA, 
     SharePointListA
);
ClearCollect(
     colMyCollection,
     Filter(
       colSharePointListA,
       Concat(
            SharePointListAChoiceField.Value,
            Value,
            ","
       ) in Concat(
           LookUp(
               SharePointListB,
               Name.Email = User().Email
           ).SharePointListBChoiceField.Value,
           Value,
           ","
       )
    )
);
Navigate(
     ScrMyScreenWithGallery,
     ScreenTransition.Fade
)

Gallery > Items property:

colMyCollection

[WarrenBelz]

Hi @Siddz ,

Try the below - note this will only work properly if both of your lists have item numbers under your Delegation Limit as both Split() and the in Filter are not Delegable.

With(
   {
      wItems: 
      With(
         {
            wListA: 
            LookUp(
               SharePointListA,
               Name = YourUserName
            ),
            wListB: SharePointListB
         },
         Split(
            wListA.FavoriteFruit,
            ", "
         )
      )
   },
   ForAll(
      wItems As aItems,
      Collect(
         colMyCollection,
         Filter(
            wListB,
            aItems.Result in FavoriteFruit
         )
      )
   )
)

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.

Visit my blog Practical Power Apps

[Siddz]

Thanks, @WarrenBelz.

Looks like I'm having an issue with the Split() function:

Here's the error:

[WarrenBelz]

Hi @Siddz ,

I actually built a model to test this - Firstly your exact data

ClearCollect(
   ListA,
   {
      Name: "Jane",
      FavoriteFruit: "Apples"
   },
   {
      Name: "John",
      FavoriteFruit: "Oranges, Pears"
   },
   {
      Name: "Janice",
      FavoriteFruit: "Apples, Pears"
    }
);
ClearCollect(
   ListB,
   {
      Name: "Adam",
      FavoriteFruit: "Oranges"
   },
   {
      Name: "Alice",
      FavoriteFruit: "Apples, Pears"
   },
   {
      Name: "Amanda",
      FavoriteFruit: "Apples, Oranges"
   }
)

Then I used a text box (txFruits) to put the name in and did this (posted as a picture to show there is no error) - I even used the With() on the collections to simulate they are Lists.

I had Jane in the Text box and got this collection 

I put in John and got this

What have you done differently - it is definitely producing the result as per your posted requirements.

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.

Visit my blog Practical Power Apps

[Siddz]

Thanks, @WarrenBelz.

I'm not sure where I'm going wrong. I'm still trying to get the Split() function to work. I may have misrepresented the requirements. Here's an attempt to clarify:

// On my button's OnSelect property, I'm collecting the current user's record from SharePointListA
ClearCollect(colFruitsList1, Filter(SharePointListA, Name.Email = User().Email)); 

// I'm also collecting all records from SharePointListB
ClearCollect(colFruitsList2, SharePointListB);

With(
    {
       wItems: 
       With(
          {
            wListA: 
            LookUp(
                  colFruitsList1, 
                  Name.Email = User().Email
           ),
           wListB: colFruits2
      },
      Split( // Error: The function 'Split' has some invalid arguments 
         wListA.FruitsColumnInColFruitsList1, // Error: "Invalid arguments type (Table). Expecting a Text value."
         ", "
      )
   )
},
ForAll(
     wItems As aItems,
     Collect(
       colMyColection,
       Filter(
         wListB,
         aItems.Result in FruitsColumnInColFruitsList2 // Error: "Name isn't valid. This identifier isn't recognized."
        )
      )
   )
);

Navigate(
     ScrMyScreenWithGallery,
     ScreenTransition.Fade
)

[WarrenBelz]

Hi @Siddz ,

What type of field is FruitsColumnInColFruitsList2  - you posted it a comma-delimited text.

[Siddz]

Hi, @WarrenBelz.

FruitsColumnInColFruitsList2 is a choice field that allows multiple selections. I thought I should concatenate the values from that field, but maybe that’s not necessary. 

[WarrenBelz · Accepted solution]

HI @Siddz ,

That is correct - showing it with commas in your post suggested it was a Text field. The solution looks a bit simpler, when I finally got there - I have this working off a Multi-choice field. You do not actually need the second With element if it is a collection.

Clear(colMyFruits);
With(
   {
      wListA: 
      LookUp(
         colFruitsList1,
         Name.Email = User().Email
      ).FavoriteFruit
   },
   ForAll(
      wListA.Value As aList,
      Collect(
         colMyColection,
         Filter(
            colFruits2
            aList.Value in FavoriteFruit
         )
      )
   )
)

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.

Visit my blog Practical Power Apps

[Siddz]

Thanks, @WarrenBelz!

My collections are returning empty (colMyFruits and colMyCollection) after navigating to the gallery even though there are common fruits in the "FavoriteFruit" column in both lists.

ClearCollect(colFruitsList1, Filter(SharePointListA, Name.Email = User().Email)); 
ClearCollect(colFruitsList2, SharePointListB);

ClearCollect(colMyFruits,
With(
    {
      wListA: 
      LookUp(
          colFruitsList1,
          Name.Email = User().Email
      ).FavoriteFruit
    },
ForAll(
      wListA.Value As aList,
      Collect(colMyCollection,
         Filter(
            colFruitsList2,
            aList.Value in FavoriteFruit
         )
       )
    )
  )
);
Navigate(ScrMyScreenWithGallery) // The gallery's Items property is colMyCollection

 colMyFruits:

colMyFruits > Value:

colMyCollection:

[WarrenBelz]

Hi @Siddz ,

Sorry, I am raising the white flag on this one - it should work but does not. Saving as a concatenated string does as per my first post.