cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GrahamSpaull
Frequent Visitor

Collate Results From Multiple Filters Of Lookup Columns In to One Table

This is my first post, and I do not profess to being anything other an amateur with PowerApps, that said I have successfully developed some apps which work fine and have some experience of coding in other applications.... but this one is frustrating me.

 

I have the below filter which is returning a single line of a table, however, there are 4 values in the collection JTMAdd, however I seem to have to invoke the First function on the filter to get the lookup values back from the required training.  What I really want is table of ALL training courses that match the required training of all 4 values in the collection JTMAdd.

 

 

Filter('Training Courses', 'Course Title' in Filter(Choices('TM Roles'.'Required Training'), Value in First(Filter('TM Roles', Site.Value = DataCardValue22_1.Selected.Value, Title in JTMAdd).'Required Training').Required_x0020_Training.Value).Value)

 

The list Training Courses holds a list of training courses and relevant information, the TM roles list has a lookup column to select the relevant training courses against a role (in the field Required Training), JTMAdd holds a list of additional roles selected against a person which is to matched against the Title column.  So overall in this case there should be 4 rows of a table returned of which I then need to check each row's Required Training and return all the training courses for all four rows to a single table.

 

The collection has 4 rows in it with a single value in each (see below).  In formal coding I would using something like a for each function, but that does not appear to be available in Powerapps.

GrahamSpaull_0-1607356983881.png

 

Any help would be appreciated.

 

Thanks

 

7 REPLIES 7
RandyHayes
Super User III
Super User III

@GrahamSpaull 

It's a little hard to grasp the full depth of your scenario without a little more detail.  
Can you provide some samples of what you have (data) and what you expect?

Also, you mention the Required Training is a Lookup, can you provide some details on the definition of the column in SharePoint?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Hi Randy

 

Thanks for coming back to me.  I will try to explain better below

 

There a SharePoint list that holds a range of Training Course Information including a location (which is a lookup column to another list that holds a list of site locations)

GrahamSpaull_0-1607445813576.png

 

There is then another SharePoint list that holds a list of designated roles which includes a lookup column to another list for location (similar to above) and a second multiple selection lookup column that holds a list of required training courses associated with at role taken from the Training Courses SharePoint List, column Course Title

GrahamSpaull_1-1607446033987.png

 

A further list then holds records against a specific person, which role they are associated with (lookup column from the TM Roles list), which Site they belong to and if they hold any additional roles to their primary job that they require training for e.g. First Aider (once again a lookup column to the training courses)

 

GrahamSpaull_2-1607446300556.png

In my powerapps app I am looking to build a screen to display a training matrix as below.  Top table displays role based training (easy), the second is supposed to display training courses associated with additional roles, but I just cant get the formula right to display these as it involves a lookup column to another lookup column  (getting list of additional roles, interrogating the TM ROles table for those roles and retrieving the list of associated training, then getting all these results from the Training Courses list.

 

Hope this helps??? any guidance of help would be appreciated.

GrahamSpaull_3-1607447036397.png

 

 

GrahamSpaull
Frequent Visitor

Just a quick update, I have managed to do a work around using flow, but its quite a slow query.  Be really interested in anyone has a solution to this within Power Apps

RandyHayes
Super User III
Super User III

@GrahamSpaull 

Sorry for delay...trying to catch up on all the posts.  Are you still experiencing issues that you need help with?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Hi Randy

 

Yes, still cannot work out a route using PowerApps, having to use flow but the query can take upwards of 1 minute to complete.

Hi @GrahamSpaull ,

I will throw in a bit of input - firstly Value is a very bad title for a field as it is a Reserved Word in Power Apps and can lead to ambiguity and unexpected results. Apart from this, is the below something like you are looking for - note free-typed so only a syntax idea.

Filter(
   'Training Courses', 
   'Course Title' in Choices('TM Roles'.'Required Training') && 
   Value in LookUp(
      'TM Roles', 
       Site.Value = DataCardValue22_1.Selected.Value &&
       Title in JTMAdd
   ).'Required Training'
).Value

 

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.

RandyHayes
Super User III
Super User III

@GrahamSpaull 

Did the suggestion from @WarrenBelz help any?  Are you still having issues?  You definitely should NOT be having to do that in Flow.  PowerApps can handle it all for you quite quickly.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (74,429)