Filter Gallery based on dynamic values

[ricchiuto]

Hi all, I'm new to Power Apps and looking for some giudance.

I'm working on a Power App that will be used to keep track of people in a large organization. In certain scenarios, the organization needs to make sure all of their people are accounted for. An automated system will send out text message alerts, and mangers need to know which of their people have responded and which ones still need to be accounted for. The automated system does all of this, but only a select few people have access to this system, so a Power App is being requested to display this data for all involved.

Requirements:

1. The automated system will export a .csv file, initially to show who is working from the office and needs to be accounted for, and then again at a given interval/frequency to show updated data
2. The Power App will need to be able to filter this data in two ways: based on response status, and based on sub-levels of organization. 

My issue:

I'm able to filter on Response Status without a problem. My challenge is being able to filter on the sub-levels of organization. A sample data record would look something like 

Name	Response	Org
John Smith		Org1/sub1.1/sub1.1.1
Jane Doe		Org1/sub1.2/sub1.2.1

What I'm trying to achieve is a filter that will allow users to drill down into their respective sub levels. Ideally, allowing them to set a default level the first time they use the app and save that selection for later use. 

The catch is these levels will change based on the given event. So I would like the options to be dynamic and only show what is available in the data (in this case Org1, and then a choice between sub1.1 & sub1.2, and then if I choose sub1.1, only show options under sub1.1 [sub1.1.1]).

Thanks in advance for any thoughts, suggestions, or ideas 🙂

[efroseroc]

Hi,

The table shows the reality of your database?

In that case you may use SPLIT, to separate  each division.

Or in your database you could have a column for each division.

For example: [Main division: Org1;  1stDivision: 1.1;  2dnDivision: 1.1.2]

Or

                      [Main division: 1; 1stDivision: 1; 2dnDivision: 2]

That should help you making the filtering easier.

[Drrickryp]

@ricchiuto 

It sounds like you have two one-to-many relationships in your data.  It would be very helpful to you in managing your app if you have separate tables for each level of the relationship.  For example, each employee can be in a Region/Division/Department and cascading dropdowns can be set up for each level to filter the data to find an employee. 

[ricchiuto]

Hi @Drrickryp, thanks for your response.

The challenge is the input data and the organizational structure can/will change from event to event. But the format in which it is passed will be consistent. For example 

/TopLvl/SecondLvl/ThirdLvl/FinalLvl (this could be between 1-7 levels deep)

And there will be roughly 2,000 sub-org units/offices where people can be classified and that will change depending on which physical location they are working out of, thus changing the person responsible for accounting for them.

What I've done so far is use Concat(), Split(), and Distinct() to get all of the sub-orgs that appear in a given report into a table. Now what I need to do is find a way to drill into the options (essentially the way File Explorer drills into a file path). I only want to see the sub-orgs that exist in the report .csv file and at the appropriate levels.

My thought is that I can split on the "/" and then build a Filter string and then filter on StartsWith(MySource, searchString), but I'm having trouble on how to present the options for which sub-orgs should be made available for the next level drill down.

I hope that is a little more clear. Sorry for any confusion and thank you for your thoughts!

[efroseroc]

Not really about the issue at hand but, this video by Shane Young may be of help https://www.youtube.com/watch?v=u-b8w9ea1kk.

He drills down through sharepoint folders, maybe is the same way you can filter your data.