Inner join multiple tables

[Mike_N]

So I am trying to create a Power App that replaces a paper patient care report (see attached).  I have created all the lists I need on SharePoint.  I have 7 lists with the Patient Info list being the "master" list and all of the other lists are related to this one via a Patient_ID.  I am trying to create a collection on app start (maybe not the best idea, so tell me if there is a better way) that has all the info related to one patient.  I thought the below ClearCollect would work but I can only add one column and it is always the last one in the "list".  It doesn't give me any errors though.  

I have a gallery on the start page that shows patient care reports that have already been completed.  I want them to be able to select one and the transition through the next pages/screens viewing the data from the report.  Similarly I would like to use those pages/screens to enter a new record.....but that seems like a different hurdle at the moment.  I would like to figure out how to view the ones I hand jammed in there first.  Any help would be greatly appriciated.

ClearCollect(

    colPCRs,

    AddColumns(

        'Patient info',

        "IncidentInfo",

        LookUp(

            'Incident info',

            Patient_ID = 'Patient info'[@ID]

        )

    );

    AddColumns(

        'Patient info',

        "InjuryType",

        LookUp(

            'Injury type',

            Patient_ID = 'Patient info'[@ID]

        )

    );

    AddColumns(

        'Patient info',

        "AntLoc",

        LookUp(

            'Anatomical Location',

            Patient_ID = 'Patient info'[@ID]

        )

    );

    AddColumns(

        'Patient info',

        "IntAsst",

        LookUp(

            'Initial assessment',

            Patient_ID = 'Patient info'[@ID]

        )

    );

    AddColumns(

        'Patient info',

        "Vitals",

        LookUp(

            Vitals,

            Patient_ID = 'Patient info'[@ID]

        )

    );

    AddColumns(

        'Patient info',

        "Narrative",

        LookUp(

            Narrative,

            Patient_ID = 'Patient info'[@ID]

        )

    )

)

[WarrenBelz · Accepted solution]

Hi @Mike_N ,

Firstly, "relational" lookups are not Delegable in SharePoint so you need to do something like the below. The sort on the first ones is just a bit of "future proofing" and will use the newest 2,000 (if you have your limit set to this) records in each in the query

With(
   {
      wInInfo,
      Sort(
         'Incident info',
         ID,
         Descending
      ),
      wInjType,
      Sort(
         'Injury type',
         ID,
         Descending
      ),
      wAntLoc,
      Sort(
         'Anatomical Location',
         ID,
         Descending
      ),
      wIntAsst,
      Sort(
         'Initial assessment',
         ID,
         Descending
      ),
      wVitals,
      Sort(
         Vitals,
         ID,
         Descending
      ),
      wNarrative,
      Sort(
         Narrative,
         ID,
         Descending
      )
   },
   ClearCollect(
      colPCRs,
      AddColumns(
         'Patient info',
         "IncidentInfo",
         LookUp(
            wInInfo,
            Patient_ID = ID
         ).YourFieldName,
         "InjuryType",
         LookUp(
            wInjType,
            Patient_ID = ID
         ).YourFieldName,
         "AntLoc",
         LookUp(
            wAntLoc,
            Patient_ID = ID
         ).YourFieldName,
         "IntAsst",
         LookUp(
            wIntAsst,
            Patient_ID = ID
         ).YourFieldName,
         "Vitals",
         LookUp(
            wVitals,
            Patient_ID = ID
         ).YourFieldName,
         "Narrative",
         LookUp(
            wNarrative,
            Patient_ID = ID
         ).YourFieldName
      )
   )
)

I have done a blog on Delegation that may be of interest 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.

[Mike_N]

@WarrenBelz, thank you!  This definitely gives me a collection with all the correct columns but.....  it's not making the correct "relation" based on the ID in the Patient Info table which should "link" to the Patient_ID in all the other tables.  I think it has to do with the LookUp but I just can't see where the issue is.

Also I had to modify your code a little and add a colon after each table in the top part of the With.  So it is wInInfo: now. 

[Mike_N]

@WarrenBelz Ok problem solved.  I had some filed type mismatch issues.   Fixed that and all is good now.  Below is the code I was able to get working.  Thanks so much for the help and pointing me in the right direction....now on to figuring out how to update records and insert new ones.  

With (
    {
        wInInfo: Sort(
            'Incident info',
            ID,
            SortOrder.Descending
        ),
        wInjType: Sort(
            'Injury type',
            ID,
            SortOrder.Descending
        ),
        wAntLoc: Sort(
            'Anatomical Location',
            ID,
            Descending
        ),
        wIntAsst: Sort(
            'Initial assessment',
            ID,
            SortOrder.Descending
        ),
        wVitals: Sort(
            Vitals,
            ID,
            SortOrder.Descending
        ),
        wNarrative: Sort(
            Narrative,
            ID,
            SortOrder.Descending
        )
    },
    ClearCollect(
        colPCRs,
        AddColumns(
            'Patient info',
            "IncidentInfo",
            LookUp(
                wInInfo,
                Patient_ID = 'Patient info'[@ID]
            ),
            "InjuryType",
            LookUp(
                wInjType,
                Patient_ID = 'Patient info'[@ID]
            ),
            "AntLoc",
            LookUp(
                wAntLoc,
                Patient_ID = 'Patient info'[@ID]
            ),
            "IntAsst",
            LookUp(
                wIntAsst,
                Patient_ID = 'Patient info'[@ID]
            ),
            "Vitals",
            LookUp(
                wVitals,
               Patient_ID = 'Patient info'[@ID]
            ),
            "Narrative",
            LookUp(
                wNarrative,
                Patient_ID = 'Patient info'[@ID]
            )
        )
    )
)

[WarrenBelz]

@Mike_N ,

Have a look at it now - I restructured yours without sufficient thought - you need a field name (the one you are getting from the other list - otherwise you will return a table.

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.

[WarrenBelz]

@Mike_N ,

Our replies crossed - you also should not need the [@] disambiguation operator - the ID is available, however if this works for you, stay with it. Note my comment that what you have will return a table (not a value), but if this is what you need, also keep it.

If this is solved, please mark the post that assisted you the most as the solution.

[Mike_N]

@WarrenBelz , thanks again!  I actually need the table returned so that works for me.  And I tried with just the ID and not using the [@] operator but it just didn't make the relationships work.