Joining two SharePoint lists into a collection

[RB2GSO]

Hi everyone,

This is my first post, though I've used many of these posts to find my way through PowerApps development.  I'm a public school principal using PowerApps to make school administration more efficient and to harness a giant ecosystem of data so that teachers can make more informed instructional decisions.

One key piece of that is joining SharePoint lists into a collection, but I have been totally unsuccessful, despite reading a number of posts in this forum and doing a lot of Google searching & YouTube watching.  I'm hoping you all can help me get over this hurdle or point me to a solution that I have overlooked.

In this specific case, I am trying to join the following two SharePoint lists with the following (relevant) fields:

StudentsPSCurrent

• StudentNumber   (number field)  (this is the relevant unique identifier)
• FName  (single line of text field)
• LName  (single line of text field)
• HomeRoom  (single line of text field)

StudentAdmin

• StudentNumber  (number field)  (this is the relevant unique identifier)
• TranspPM  (single line of text field)

I'd like to create the collection colStudPSAdmin with these fields:

• StudentNumber   (from StudentsPSCurrent)
• FName  (from StudentsPSCurrent)
• LName  (from StudentsPSCurrent)
• HomeRoom  (from StudentsPSCurrent)
• TranspPM  (from StudentAdmin)

I can create the collection, add the fields from the StudentsPSCurrent list, and add a text or number value into a TranspPM field of the collection.  That code is below:

That successfully produces the collection, below:

But, as soon as I add a lookup to use the TranspPM field from the StudentAdmin list as the text for the TranspPM field in the collection, I get an error saying that a text and a table can't be compared in the lookup.  Here's that code and the error:

This same lookup code works everywhere else except in collections.  I've also tried

• wrapping one or both lookup values in Value(... or Value(Text(...,
• adding .Text or .Value to the end of one or both lookup values,
• using ThisRecord instead of StudentsPSCurrent in the lookup (this does not throw an error in the editor, but it does not produce any rows in the collection), and
• a lot of other things that were more or less (or not) intelligent.

I'm stuck!  I hope someone can help me get unstuck!

Thanks!

[WarrenBelz]

Hi @RB2GSO ,

Try this format

With(
   {
      wAdmin:
      RenameColumns(
         StudentAdmin,
         "StudentNumber",
         "StudentNo"
      )
   },
   ClearCollect(
      colStudPSAdmin,
      AddColumns(
         StudentsPSCurrent,
         "TransPM",
         LookUp(
            wAdmin,
            StudentNo = StudentNumber
         ).TransPM
      )
   )
)

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.

[RB2GSO]

Thanks for the quick response, @WarrenBelz .

I gave that a shot and am getting errors in the With statement (see below):

The error on With is "Name isn't valid.  This identifier isn't recognized."  The error on { is "Unexpected characters.  Characters are used in the formula in an unexpected way."

I've not used With before, so I tried adding parens to it, but that didn't work any better (see below):

The error in the ClearCollect portion of the statement is "Behavior function in a non-behavior property.  You can't use this property to change values elsewhere in the app."

Is your line of attack renaming the StudentNumber field in the StudentAdmin list?

Thanks for the help!

Robin

[WarrenBelz]

Hi @RB2GSO ,

Yes as far as the renaming (but also Delegation) - you should never name linking fields the same as it can lead to ambiguity on any lookup reference. I also forgot to add the field reference (now corrected), but that should not have caused the error you have. Below is some code I ran on a couple of test lists where the Title column is the linking field

It runs perfectly and produces the expected collection with the added field. Can you spot any difference to your references apart from the corrected code as mentioned.

I also have a blog on the With() statement that may be of use to you.

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.

[RB2GSO · Accepted solution]

Okay - a little slow to get back to this because it didn't work out of the gate and I haven't had the seat time to get it all sorted out.  Your suggestion was spot on with the same-named columns and helped me understand how the With function works.  With that, I was able to get done what I needed without the Add Columns portion.   The code snippet that worked is at the end of this post.

For those using this post in the future, the problem here was that the unique identifier column in each of the two tables I was trying to join had the same name. To get around it, we used the With and RenameColumns functions to give that column a different name in one of the tables. 

The RenameColumns function is pretty straightforward.  The With function less so.  The With function gives you a way to refer to a chunk of a formula with what is essentially a variable name.  This makes the primary formula more readable because the chunk of the formula you "With"ed is referred to simply with the variable name rather than all the details in that chunk of code. 

So, in this case, perhaps I didn't technically need to pull the RenameColumns chunk out using the With function, but that definitely makes the solution easier to read and understand.  Thanks, @WarrenBelz !

With(
    {
        NewStudAdmin: RenameColumns(
            StudentAdmin,
            "StudentNumber",
            "StudentAdminStuNum"
        )
    },
    ClearCollect(
        collectJoinStudPSAdmin,
        ForAll(
            StudentsPSCurrent,
            {
                StudNum: StudentNumber,
                StudFL: StudFLName,
                TranspPM: LookUp(
                    NewStudAdmin,
                    StudentAdminStuNum = StudentNumber,
                    TranspPM
                )
            }
        )
    )
)