How do I create a collection of specific data from a list, based on the value of another list to act as a filter mechanism?

[DGWolfe]

Hi all, I am building an app but my workplace has strict data governance ownership which has caused some out of the box thinking. As such, I have to deliver a finished app that will pull data from various SharePoint lists, but then filter down.

As an example, I have three lists of data (A = Employee data, B = Department1 Data, C = Department2 Data).

I have a gallery that currently works so that the user can search and pull their details up (List A).

I am now trying to pull ONLY the relevant data connected to that user (based on Role) from the other two lists into a collection, but I am stuck with the formula.

I assumed it would be something along the lines of: Find role.value from List A, collect role value data from lists B & C into a collection.

Yet, I am struggling to express this correctly. Any hep would be greatly appreciated.

[Adrian_Celis]

Hi @DGWolfe ,

As far as I understand your problem, you are in the right track.

You can create a collection to house both List B & C. The question is are the columns in Lists B and C the same?

If they are, then you can use a formula like this:

Clear(YourCollection);
Collect(YourCollection,Filter(ListB,Role = varRole));
Collect(YourCollection,Filter(ListC,Role = varRole));

Your data should now be in a collection named YourCollection. I am assuming you have a Role column in both lists and the 'varRole' is the selected user's role.

[DGWolfe]

Yes and no to the question are are the columns in Lists B and C the same?.

There are a few columns that are different, but I only want to extract the same three columns from each list.

I tried what you said, but when I try to input the section collection, it is not picking up my "role" column for some reason?

For context: The user has to come to the app, and search for their info via an input (this works fine)

However, I then need to show two types limits that apply to their roles based on these other lists, but these other lists will have a LOT of data, which is why I am trying to only pull the data relevant to their role to avoid delegation issues.

My formula so far looks like this:

Set(

    searchQuery,

    unumberInput.Text

);

Set(

    varRole,

    roleStaffGalLabel.Text

);

Collect(

    DelegationReviewCollection,

    {

        col_Function: funcStaffGalLabel.Text,

        col_Department: deptStaffGalLabel.Text,

        col_Role: roleStaffGalLabel.Text,

        col_Name: nameStaffGalLabel.Text,

        col_Unumber: unumberInput.Text,

        col_Date: DatePicker.SelectedDate

    }

);

I tried inputting your formula beneath, but still ran into errors

[Adrian_Celis]

Hi @DGWolfe 

What is your DelegationReviewCollection for? It seems like it is collecting the data from your user input?

To ask for clarification. You have List B & C, and both of them have different columns but what you want to get have the same names? (For example: col_Function, col_Department, col_Role). So you are looking to combine all the data relevant to the selected role into only one collection. Am I right?

[DGWolfe]

Hi Adrian, I will post a screenshot to give more context. Sorry, it's a bit complex (overly so, due to the way I have to keep the list info separate).

So the User will come to the app and input their U Number (payroll number). This will pull information from list a and populate their details at the top of the screen. This is being added to my DelegationReviewCollection

Collect(

    DelegationReviewCollection,

    {

        col_Function: funcStaffGalLabel.Text,

        col_Department: deptStaffGalLabel.Text,

        col_Role: roleStaffGalLabel.Text,

        col_Name: nameStaffGalLabel.Text,

        col_Unumber: unumberInput.Text,

        col_Date: DatePicker.SelectedDate

That works fine. However, the issue I am running into is that once they pull their employee details, they need to further review certain information kept on two various lists.

I have two radio button inputs, that link to List B and List C. When you click a choice, it brings up currently all data for that choice (all employee data) and makes a gallery to the right visible with that data.

What I am trying to do is write a formula so that:

1. When Umber is searched, employee details are listed and added to collection (this works fine)

2. Formula then Role (text field) and adds only the role specific data from List B to the collection (which will give the user the ability to view when they work through the topics on the the gallery).

3. Duplicate of 2 - Formula then Role (text field) and adds only the role specific data from List C to the collection

I wish it didn't have to be so complex, but they way my company is structured, I must keep the list data separate. Does that make sense?

[Adrian_Celis]

Hi @DGWolfe 

Thanks for the detailed clarification. I'm sorry for asking more questions but this is necessary for me to understand.

I am not sure if I completely understand your # 2 & 3 on the part where it says "Formula then Role (text field)".

Do you only have one gallery on the right side? And you want to show the relevant information from both List B & C into that gallery using a collection? 

If so, can you give me the exact column names of your List B & C so that we can combine both of them by filtering out their roles.

P.S. Apologies for the possible delayed response.

[DGWolfe]

Sorry, that was a typo on my part.

I have two galleries (one for list B and one for list C). Both invisible and overlayed on top of each other. When the user selects from either radio input, that gallery turns visible and shows the data for that radio button.

I want to only show data for that radio button further filtered down for that user only (currently both galleries show data for all users).

The column headings I need to collect from both Lists B & C are the same. They are: "Authority Type", "Authority Limit", and "Exclusion"

I assumed I could add these three columns for each list (B & C) to my collection, then alter the gallery to point to the collection data and not the full list?

[Adrian_Celis · Accepted solution]

Hi @DGWolfe 

I see. So you have two galleries. I thought you only had one so I thought you need to combine your List B & C in a collection. So now I think your Collection called DelegationReviewCollection is irrelevant to your galleries. To make it more simple for now, you don't need a collection (but if you have a reason for having a collection, let me know).

All you need to do in your gallery is set its Items property. You should simply filter out using the Filter function.

So you need to write something like this in the Items property of one of your galleries:

Filter(ListName, ColumnName = RadioControl.Selected.Value)

Where:

ListName - The name of your SharePoint list

ColumnName - The column name of the one you are going to filter out (This should be the column where the values of your radio buttons)

RadioControl - The name of your radio button control (Take note that the .value part may depend)

[DGWolfe · Accepted solution]

Thanks Adrian,

Sorry, I meant I have two galleries overlapping and invisible on the right side of the app (which you can see in the picture). Each of these galleries is connected to a radio button input.

In total I have three galleries.

Gallery 1 sits at the top and shows List A data when the user inputs their payroll number. This data is where the Role is. 

Gallery 2 connects to Radio input 1

Gallery 3 connects to Radio input 2

The formula you have provided above, is what I already have. This works fine for show all data based on the radio selection, however, that is not what I need to achieve.

I need to show radio data, in either gallery 2 or 3, based on the role in gallery 1.

This is why I thought I needed a collection? Does that make sense?

However, I have used your formula above in my search field and it works! So now I have a collection that pulls all data I need.

After that, I repointed the gallery to the collection and filtered and it worked perfectly!

Thank you so much for your time and patience assisting me with this. It was quite the feat! 🙂