cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Maexchen
Helper II
Helper II

can you filter from 2 different datasources on one gallery

I have two Sharepoint lists,
- List1 has "Project Name" and "Project status"
- List2 has "Project Name", "details" and "owners"

 

there can be multiple entries in the second list for the same "project name", but only one entry per "project name" in the first.

I now want to show all entries from the second list in a gallery but filtered on "owners" from list2 and "project status" from list1 (only showing data from list2 though)

 

individually the filters are:

 

SortByColumns(Filter(List1,projectname = "in progress"),"Created")
SortByColumns(Filter(List2,User().Email in owners.Email),"Created")

 

is there a way to combine them?

1 ACCEPTED SOLUTION

Accepted Solutions
yashag2255
Dual Super User II
Dual Super User II

Hi @Maexchen 

 

Instead of directly referring to Gallery1.Selected in the "Item" property of the edit form, you can update the expression to:

LookUp(ListName, ID = Gallery1.Selected.ID)

 

Here, I have used ID column for unique identification, if the column Name differs, please update it accordingly.

 

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

6 REPLIES 6
yashag2255
Dual Super User II
Dual Super User II

Hi @Maexchen 

 

Can you try out the below expression:

SortByColumns(AddColumns(Filter(List2,User().Email in owners.Email),"Project Status", LookUp(RenameColumns(ListA,"Project Name","ProjectName"), "ProjectName" = "Project Name")."Project Status"),"Created")

 

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!

thanks, this one doesn't work. (I believe it misses the filtering on "in progress") but I think I get your idea, so hopefully I can work with this.

yashag2255
Dual Super User II
Dual Super User II

Hi @Maexchen 

 

Based on the expression shared by you, you were matching the "in progress" value in the project name column, is that correct?

If this needs to be mapped to the Status column, you can update the expression as:

SortByColumns(AddColumns(Filter(List2,User().Email in owners.Email),"Project Status", LookUp(RenameColumns(Filter(ListA, "Project Status" = "inprogress"),"Project Name","ProjectName"), "ProjectName" = "Project Name")."Project Status"),"Created")

 

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!

edit: took me a couple of minutes to write this, so this is not an answer to the post above, but should be one higher.

 

okay, has anyone got an other solution?

I was able to build a formula like the one above but the issue is that the gallery I want to show the items in is linked to a form where the whole entry from list2 is shown and that does no longer work. When I link the item of the form to "Gallery1.selected" I get an error (Expected a value compatible with "data source") which I believe is due to me manipulating the datasource by appending a column. 

 

However for completeness: the formula I constructed based on the input from @yashag2255 

 

SortByColumns(Filter(AddColumns(List1,"status2",LookUp(List2,'Project name' = Title,status)),User().Email in owner.Email,status2="in progress"),"Created")

 

 

yashag2255
Dual Super User II
Dual Super User II

Hi @Maexchen 

 

Instead of directly referring to Gallery1.Selected in the "Item" property of the edit form, you can update the expression to:

LookUp(ListName, ID = Gallery1.Selected.ID)

 

Here, I have used ID column for unique identification, if the column Name differs, please update it accordingly.

 

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

thanks, that did the trick

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

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

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (92,896)