Need to display a grid in a canvas app where columns are rows of a table

(2) Share

Report

Posted on by Gilles12

109

Hello.

I need help to understand if my need is feasable in some way in a canvas app.

I have tables (would it be in the dataverse or sharepoint) and I need to display a grid in which columns values (headers) are rows of a table. Of course the number and values of these columns is variable.

Let's take a simple example in order to illustrate. Let's say I have customers, employees and projects at the customers. Employees are in charge of projects. I need to show who is in charge of which project for which customer. Overtime, customers, employees and projects are changing, and sometimes there is no project for some customers and some employees as well. We can imagine there is a status on project just to show the current ones.

I know it's already difficult to transpose data in order to have this kind of representation even with a fixed number of values (like havings regions or quarters in columns). So I think this need is even more complex.

I have also asked Copilot and I was brought in solutions like denormalizing data in a more simple collection rather than three tables, but it seems not realistic.

This kind of need seems to be obvious and frequent. Have you already handled such a situation? Is there an easy or less easy solution? Otherwise I am thinking about puting some power automate in the process but it is heavy because there would be a delay when adding new projects, new employees, etc. Power Automate would need to recreate a new data source from scratch (like recreating a temp sharepoint list with employee as real columns). Not the best solution I think.

Thanks for taking the time to review and give your opinion (this is made with PPT just for illustrating the principle, not the real form).

Gilles.

Categories:

General Questions

All responses (11)

Answers (1)

Verified answer

jpespena 335 on at

Like (1)

Report

Need to display a grid in a canvas app where columns are rows of a table

@Gilles12

Glad to see you made it.

For the Project name LookUp I used this code:

LookUp(Project table, Customer = CustomerIDHidden.Text And Employee = ThisItem.EmployeeID).Project

Given that the EmployeeID is a number column, it should work just fine.
Gilles12 109 on at

Like (2)

Report

Need to display a grid in a canvas app where columns are rows of a table

@jpespena

As a follow up of my last message...

I've got it work for the alignment, it was the TemplateSize property that were not aligned between the galleries.

(I have tested all cases: several people on the same project, several projects for the same customer, employee or customer with no project, change the sort order... everything works!)

So all is good, if you could just tell me if the method I used (the lookup) is the same as you (as you did not explain this point) in your mail answer:

LookUp(ProjectTable, CustomerID=CustomerIDHidden.Text && EmployeeID=Int(EmployeeIDHidden.Text)).Project

And for my curiosity, why using this method (in red) does not work?

LookUp(ProjectTable, CustomerID=CustomerIDHidden.Text && EmployeeID=ThisItem.EmployeeID).Project

Thank you again your have been brilliant (I have marked your initial post as answering the question)!

Gilles.
Gilles12 109 on at

Like (1)

Report

Need to display a grid in a canvas app where columns are rows of a table

@jpespena

Thanks to your explanations and screenshots, I am on the right way!

May I get a few more minute from your time? I have replicated what you have done before going back to my project (that is a bit more complex, but I am sure from there I will be able to do anything).

What was missing in your explanation is the way to get the project name. I have managed to micmic for the employees your method of a hidden ID for the customers, and I have used a lookup. This is working well.

LookUp(ProjectTable, CustomerID=CustomerIDHidden.Text && EmployeeID=Int(EmployeeIDHidden.Text)).Project

Have you done differently?

Just for my understanding, this one was not working, what's wrong with this method (question can be applied to the customer also)?

LookUp(ProjectTable, CustomerID=CustomerIDHidden.Text && EmployeeID=ThisItem.EmployeeID).Project

Important, when I add customers and employees without a project, it works (see below).

Finally, I am just struggling with the alignment, and I don't understand what is the issue. I have tried to change a lot of attributes of the Employee Gallery, but I can't fix that. Can you help on that too?

Thank you,

Gilles.
jpespena 335 on at

Like (2)

Report

Need to display a grid in a canvas app where columns are rows of a table

@Gilles12

I created 3 collections to represent your 3 tables; CustomerTable, EmployeeTable and ProjectTable.

To build the nested gallery, you need to create the CustomeGallery vertical gallery first:

Inside this gallery you'll add text label for the CustomerIDHidden and horizontal gallery for EmployeeGallery:

Inside the EmployeeGallery you need to add text label for the project name.

After you build the nested gallery just add other galleries for the header and customer column. Just make sure that the items for each are the same the their counterparts in the nested gallery (EmployeeHeader items = EmployeeGallery items, CustomerRow items = CustomerGallery items)
Gilles12 109 on at

Like (1)

Report

Need to display a grid in a canvas app where columns are rows of a table

Hello @PN-13051647-0.

No, in model driven apps there are grid features (OOB and addons) but they are very traditionnal, with classic table columns. Model driven capabilities are quite restrictive, no data transposition or headers coming from another table. Anyway, the app in which to include this screen is a canvas app.

Thanks for your reply,

Gilles.
PN-13051647-0 22 on at

Like (1)

Report

Need to display a grid in a canvas app where columns are rows of a table

Is there a OOB for this same functionality but in model driven?
Gilles12 109 on at

Like (1)

Report

Need to display a grid in a canvas app where columns are rows of a table

@jpespena

I really appreciate the time you have spent. Can I just ask you to give me your three Items properties in order to understand the nesting?

I am not sure you have three tables like me, it seems you have only one gallery nested, with the customer name being part of the project, isn't it? In my case, according to my example, several employees can be on the same project, an employee on several projects (or no project), and a customer can have several projects (or no project). So I need to have a separate customer table, and in addition I have other attributes for the customers (I wanted to make it simple) so I can't just have the customer name in the project table. But perhaps I am wrong on what you have done.

Ideally I would like to be able to do something like this.

Until now, my interrogation is how to have a "double nesting", to have the project both nested into the customers gallery and employees gallery, and I am not sure this is feasable. As of now in what I try to build, I have only one nesting, so in the cells of the table I can only relate to customers, or to employees, depending on what I nest in what, but not both.

But perhaps you are providing a simpler solution.

Or perhaps, this was my second option, I need to denormalize my model just for the sake of this screen, and have the customer name (and other potential customer attributes needed in the left) into the projects so that I have ony one nesting.

As you may understand, until now with canvas apps I have done simple things (my experience is more with model driven/D365 apps), but with this kind of need I feel I am learning fast.

Many thanks,

Gilles.
jpespena 335 on at

Like (1)

Report

Need to display a grid in a canvas app where columns are rows of a table

@Gilles12

Yes, I tested it in PowerApps. Both pictures from my previous comment are from the testing that I did.
Gilles12 109 on at

Like (1)

Report

Need to display a grid in a canvas app where columns are rows of a table

@jpespena

Thank you for providing this. It's close to one of the two suggestions Copilot provided me, but I have not been able to achieve it. I will have a new try based on your description. Did you really do the test or is your answer theoritical? Seems you have created the objects but the layout is a mockup.

@ronaldwalcott

Thank you as well. However, the need is more complex that my example, I have simplified. The text in the cells need to be an aggregation of data about the project (which would still be achievable with Power BI while aggregating data in a single text), but also the grid should be part of an app in which the user can add customers, employees and projects, and with Power BI the refresh would not be quite immediate.
Suggested answer

jpespena 335 on at

Like (1)

Report

Need to display a grid in a canvas app where columns are rows of a table

You can achieve this with a nested gallery, however it's not advisable as it may affect the performance of the app. To do this:

1. Create a vertical gallery with Items = Customer table

2. Inside the row of the Customer gallery, add a Text Label with Name = CustomerIDHidden, Text = ThisItem.CustomerID

3. Inside the row of the Customer gallery, add a horizontal gallery with Items = Employee table.

3. Inside Employee gallery, add a text label with Text = LookUp(Project table, Customer = CustomerIDHidden.Text And Employee = ThisItem.EmployeeID).Project

Just add a separate galleries for the Employee header and Customer column and align it to the Customer gallery