SharePoint Lists custom N:N - display and concatenate items in gallery label

[RyanM]

I'm hoping for some help with a displaying a custom SharePoint N:N relationship in a canvas app gallery label. I've found some great advice on this board, but can't quite get the result I'm after.

I have three SharePoint Lists with this basic structure (noting Case and Party have more columns not shown). 

All columns in these tables are either number, date or text - I'm not using SharePoint lookup or other complex column types.

I have a vertical gallery displaying a list of cases, and want to display the PartyName related to each Case in a label. If more than one PartyName is related to a Case, I want to concatenate each PartyName using a comma separator.

The closest I've come to getting this to work is this formula:

Match(Concat(Filter(PartyCaseRelation, CaseID = ThisItem.ID),Concatenate(LookUp(Party, ID = PartyID, PartyName & ", "))), "^(?<trim>.*), $" ).trim

This formula returns PartyName, but if there are multiple Party Names related to a case, only the first PartyName is concatenated multiple times (presumably because LookUp is used?). For example, the gallery label will display:

Jane Smith, Jane Smith 

The other option I've tried is to use a collection, and join the PartyName to PartyCaseRelation:

Applying this formula to the gallery label seems to work, but some party names appear blank:

Concat(
    Filter(
        colConcatPartyName,
        CaseID = ThisItem.ID
    ),
    PartyName,
    ", "
)

Any advice on the best way to achieve my requirement would be greatly appreciated. Particularly, is the Collection workaround worth pursuing, or are there better/more performant methods I should investigate? 

[WarrenBelz]

Hi @RyanM ,

I am not sure of the output you want here, but this should add a column with the data you need

AddColumns(
   Filter(
      PartyCaseRelation, 
      CaseID = ThisItem.ID
   ),
   "Parties",
   Concat(
      Filter(
         Party(
            ID = ThisItem.PartyID
         ),
         PartyName, 
         ","
      )
   )
)

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

[RyanM]

Thanks @WarrenBelz - I think I can make your suggestion work if I go with the AddColumns option. To answer your question about the output I'm after, this is an example with mock data:

From this data, I'm wanting to achieve this in my gallery:

I think I can make it work using a collection, but I'm not sure if that's the best way to achieve this or if there's a cleaner or more performant way?

[WarrenBelz]

@RyanM ,

That should resolve directly as the Items of a Gallery - note that there is a Delegation limit on the volume of the output (as there is in a collection)

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

[RyanM]

Thanks @WarrenBelz - that's definitely helped me make some progress. I'm still not quite getting the result I need, though. I've created collections for my three tables and attempted to incorporate your formula into a collection 'colGalleryItems' to use in gallery Items. This is the formula:

ClearCollect(
    colGalleryItems,
    AddColumns(
        colCase,
        "Parties",
        Filter(
            colPartyCaseRelation,
            CaseID = colCase[@ID],
            Concat(
                Filter(
                    colParty,
                    ID = colPartyCaseRelation[@PartyID],PartyName),
               
                ", "
            )
        ),
        "Outcome",
        Filter(
            colOutcomeCaseRelation,
            CaseID = colCase[@ID],
            Concat(
                Filter(
                    colCaseOutcome,
                    ID = colOutcomeCaseRelation[@CaseOutcomeID]
                ),
                Outcome,
                ", "
            )
        ),
        "CaseType",
        LookUp(
            colCaseType,
            ID = colCase[@ID],
            CaseType
        ),
        "CaseSubType",
        LookUp(
            CaseSubtype,
            ID = colCase[@ID],
            CaseSubtype
        )
    )
)

When I view colGalleryItems, the "Parties" and "Outcome" columns appear as tables:

Inside the Parties colum/table, I see the colPartyCaseRelation columns, and no concatenated party names. The same thing happens with Outcome, which is intended to function the same way. Do you have any advice on revising this formula? The LookUps for "CaseType" and "CaseSubtype" work as expected.

[WarrenBelz · Accepted solution]

@RyanM ,

When you use Filter, you are going to get a Table (I assumed that is what you needed). The Concat() inside that (you are nesting it inside the Filter) will put a single column Result inside that Table. You have a N:N relationship so the output will always be a Table.

[RyanM]

Thanks again @WarrenBelz - I still have some work to do, but you've put me on the right track.