Filter 2 share point lists with a single gallery

[Farquad88]

Hello,

I am stumped here and would appreciate any help.

I have two share point lists:

Weeklylist: the first is a list of projects with project details (manager, end date, etc) the relevant column here is the project name (note: this is not the title column) each project has a single row.

risklist: the second list is a list of risks associated with the projects found in weeklylist. It is possible that a project has multiple risks, it is also possible that it has none. Each record in the risklist has the project name (which may be repeated when there are multiple risks)

on my start page, I have a gallery which uses weeklylist to filter by projects, when a user selects the project they are taken to a page where other data related to that project. One of the sections on this page is for risks. I would like it to pull all associated risks (0-3) based on the selection made in the gallery.

it does not seem to work as the gallery is based on weeklylist and the form for risks is based on risklist.

2 questions:

1. How can I filter a form with data from list2 based on a gallery selection in list1?

2. Is it possible to make this dynamic, since there may be multiple risks for a single project I’d like it to display all of the risks instead of only the top match.

thanks!

[RandyHayes · Accepted solution]

@Farquad88 

You can get all of the risks from the risklist with a formula such as this:

Filter(risklist, 'project name' = Gallery1.Selected.'project name')

This will use the selected project name from your gallery on the first screen (please rename Gallery1 in the above to your actual gallery name, and also adjust the other names as needed).

I hope this is helpful for you.

[Farquad88]

Randy,

I still receive an error. I want to make sure this should be the ‘item’ on my form?

I think the issue may be that I am applying this to an editform which isn’t able to pull multiple records in one instance?

a 2nd question is, where you say ‘project name’, my field in either list is just called project. I would just put ‘project’ in the formula?

[RandyHayes · Accepted solution]

@Farquad88 

No, you cannot edit multiple records in a Form.  

Filter will return a table of records.  You will need some way for users to select the risk that they want to edit from either a dropdown, or a gallery or some other control with an Items property.  From that you can set the Item property of the form based on the selected record.

[Farquad88]

Thank you!

I applied the filter formula you provided to a gallery which allows me to select each record individually and make updates.