cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
antony1
Level: Powered On

Displaying Relational list Data in PowerApps Gallery, is it possible?

Hi everyone,

I need some help with something that I am not sure is possible. 

I am building a Canvas App that connects to multiple sharepoint lists structured as a series of One to Many tables. I have successfully made a record creation screen that allows the user to create records for an Event and assign multiple People and Categories to each Event record. This is recorded in three lists in Sharepoint: an Event list, Event_People Junction list and an Event_Category Junction list.

My problem is now letting the User view and Edit the records created. I have searched through various forumas and documentation and haven't been able to find anything.

What I want the user to be able to do is to see a list of records created with the People assigned to each Event record. 

My initial approach was to create a screen with a Gallery from the Event list. I have been able to then display the ID's for the people assigned to each Event using Concat and Filter:

Concat(Filter('zzEventPeople', EventID_EP = ThisItem.EventID), Text(PeopleID_EP), ", ")


This returns a comma seperated string of the ID values of people associated with the Event from the Event People Junction list.

My next thought was that it might be possibe to achieve what I want using this string to create a For All + LookUp loop based on the String values to LookUp the People ID and return the First Names and Last Names back into a comma seperated string.

However I don't know if this approach makes any sense or if it is even possible? and how I would even go about coding that?

An Example of the Data:

Event List
EventID  |  Date
1            1/09/2019
2            25/09/2019

Event People Junction List 
EventID_EP  |  PeopleID_EP
1                          2
2                          1
2                          1

People List
PeopleID  |  FName   |   LName
1                   Jim            Contoso
2                   Jenny        Contoso

I understand using Common Data Service would probably be the best work around but currently due to organisational limits, I'm limited to sharepoint lists. I would love any comments, ideas or links to comments that have explored this!! 


1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Displaying Relational list Data in PowerApps Gallery, is it possible?

Hi @antony1 

 

Can you try to update your expression as:
 
Concat(Filter('zzEventPeople', EventID_EP = 2),Concatenate(LookUp(PeopleList,PeopleID = PeopleID_EP).FName," ",LookUp(PeopleList,PeopleID = PeopleID_EP).LName), ", ")
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

2 REPLIES 2
Dual Super User
Dual Super User

Re: Displaying Relational list Data in PowerApps Gallery, is it possible?

Hi @antony1 

 

Can you try to update your expression as:
 
Concat(Filter('zzEventPeople', EventID_EP = 2),Concatenate(LookUp(PeopleList,PeopleID = PeopleID_EP).FName," ",LookUp(PeopleList,PeopleID = PeopleID_EP).LName), ", ")
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

antony1
Level: Powered On

Re: Displaying Relational list Data in PowerApps Gallery, is it possible?

Hi @yashag2255 ,

Thanks so much for your help!

That expression worked like a charm returning the First and Last name!
I subsituted the EventID_EP = 2 for EventID_EP = ThisItem.EventID_EM in Gallery which returned the correct first and last names for each Event record. 

The gallery being based on a junction table of course has duplicates so my next step will be looking at filtering the duplicate values out.

Thanks heaps again!! 
 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,063)