can you filter from 2 different datasources on one gallery

[Maexchen]

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?

[yashag2255]

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!

[Maexchen]

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]

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!

[Maexchen]

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 · Accepted solution]

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!

[Maexchen]

thanks, that did the trick