Answered

Using complex report to view items in a gallery or form viewer

(2) Share

Report

Posted on by BKayMDOT

I am trying to create a power app that would look at a report and allow me to view a specific document/page and return a list of all of the security codes that have access to the document.

The report is laid out like this:

I would like to have something like this, with a drop down that allows me to select an item from the column labelled ‘Resource’ and have a gallery or form viewer of some sort display every security code that can access the resource and the level of access that it has (PU2, PR1, DU9, etc.). For example, if I chose the last item in the sample above (4BDL), it would return something like the following:

This is a gallery, but could use any type of display widget. Could just as easily be a display form with two columns...just something to display these results. The report is kind of a 'three pronged' thing and was thinking of breaking it nto two reports to make things easier. Maybe a power automate flow?

Any ideas and input are much appreciated!

Categories:

Using Apps Using Formulas

I have the same question (0)

All responses (4)

Answers (1)

BKayMDOT 52 on at

Like (1)

Report

Using complex report to view items in a gallery or form viewer

@wolenberg_ Thanks for the tip. Power BI may be the route to go. I have not used Power BI up to this point, but this could serve as an opportunity to do so.

Was this reply helpful? Yes No
Verified answer

wolenberg_ 394 Moderator on at

Like (0)

Report

Using complex report to view items in a gallery or form viewer

Understood... in this case i think the most viable solution for you should be the Power Bi , using DAX formulas the amount of data to be process is not a problem, and this kind of cross reference panel its a native funcionality you can do this in Power Query using the Unpivot Columns feature of the power bi modeling data structure ( the only issue it's the limitation of the user interaction doing updates direct in the database ) This is possible but not viable to build, consider this approach and see what benefits you can have.

Power BI gives you:

• Dynamic filtering by resource
• Clear visuals of access levels
• Scalable security via RLS
• Advanced DAX for custom logic

If this helped or could help others in the community, feel free to give it a like or kudo — it helps surface useful answers for everyone!

Was this reply helpful? Yes No
BKayMDOT 52 on at

Like (1)

Report
Using complex report to view items in a gallery or form viewer

@wolenberg_ Thanks for the input. I am aware of the ClearCollect function but actually haven't gotten around to using it much. To use that option, I get the impression that, to use this option:

colApprovers, { Code: ThisRecord.'1STAPPROVER', Level: "1STAPPROVER" }, { Code: ThisRecord.'2NDAPPROVER', Level: "2NDAPPROVER" }, ...

Would I not have to include a line for every security code for every security code/approver column? If so, that could be impractical. What I included above is just a snippet/sample of the report. There are app. 300 security code columns along with around 50 access levels.

Was this reply helpful? Yes No
Suggested answer

wolenberg_ 394 Moderator on at

Like (0)

Report
Using complex report to view items in a gallery or form viewer

Hello , I have some sugesstions that may be helpfull for you situation, let me know if you have any questions.

You want:

• A dropdown to select a document/page from the RESOURCE column.
• A gallery or form to display all security codes (PU2, PR1, DU9, etc.) that have access to the selected resource.
• Optionally, split the report into two parts or use Power Automate for deeper logic.

Power Apps Canvas App Approach

1. Prepare Your Data

Assuming your SharePoint list or Excel table is structured like the screenshot:

• Each row = one resource (e.g., ZV0001, 4BDU1)
• Each column = one approver/security code (e.g., 1STAPPROVER, PU1, PR1, etc.)
• Each cell = either blank or a code indicating access

To make this dynamic, you’ll need to transform the row into a vertical list of approvers for the selected resource.

2. Create a Dropdown for Resource Selection

Items = Distinct(YourDataSource, RESOURCE)

3. Use a Collection to Flatten the Row
On dropdown selection (OnChange or OnSelect of a button), use this logic:

ClearCollect( colApprovers, { Code: ThisRecord.'1STAPPROVER', Level: "1STAPPROVER" }, { Code: ThisRecord.'2NDAPPROVER', Level: "2NDAPPROVER" }, ... { Code: ThisRecord.PU1, Level: "PU1" }, { Code: ThisRecord.PR1, Level: "PR1" }, { Code: ThisRecord.DU9, Level: "DU9" } )

Then filter out blanks:

ClearCollect( colFilteredApprovers, Filter(colApprovers, !IsBlank(Code)) )

4. Display in a Gallery
Set the gallery’s Items property to:

colFilteredApprovers

Inside the gallery, use two labels:

• ThisItem.Code → shows the security code
• ThisItem.Level → shows the column name (access level)

Optional: Power Automate Flow

If your data source is large or complex:

• Use Power Automate to:
⁠◦ Trigger on resource selection
⁠◦ Parse the row into a JSON array of approvers
⁠◦ Return the array to Power Apps
• This gives you more flexibility for formatting, emailing reports, or logging access.

If you want to split the report into two views:

• One gallery for approvers by level
• Another for security codes grouped by type (e.g., PU, PR, DU)

You can use GroupBy() or Filter() to segment the data accordingly.

If this helped or could help others in the community, feel free to give it a like or kudo — it helps surface useful answers for everyone!

Was this reply helpful? Yes No