cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NewOzzie
Regular Visitor

Create a Matrix type input in PowerApps (like an Excel sheet)

Hi All

 

We have a situation where we would like to allow users to populate data in a PowerApp using a matrix style input. By this I mean that each row in the gallery represents an entity (say a person) and the columns represent dates with the value being a value (e.g. time worked). Effectively each CELL (not row) is a record in the SQL database behind this.

 

Is this possible out of the box or via a 3rd party addin?

 

See the picture here for an example

2019-10-30_10-28-46.png

Thanks,

Craig

17 REPLIES 17
rahulswimmer
Helper II
Helper II

@johnnyshield 

In Collect Col_PatchBack, you have included ID for Mon, Tue, etc..
How is that possible as they are the new items to be added ?

rahulswimmer
Helper II
Helper II

@johnnyshield I have a delete icon to the rightmost end of every row. When user deletes a row, how do I make sure that its deleted from the collection and source (both before and after pivoting).

the patchback ID will be blank if e.g. Mon.ID is blank, so patch will add a new row

That will be a little move involved, you will need to add a column in both your matrix collection and patchback collection to flag items for deletion. Note that the solution I presented does delete entries with zero hours booked against them. When you save the collection is refreshed, so it will automatically delete the row from the displayed collection if there are no hours booked on that row. 

So I recommend you do not add this feature as the user can easily "delete" a row by having zero hours on the row, then clicking save, and you don't need to change any of the code.

@johnnyshield I have managed to implement row level delete with respect to the matrix form. Below is the code. Please suggest if it can be done in a more efficient manner:

If(
    CountRows(colDeletedRecords) = 0,
    Clear(colDeletedRecords)
);
Collect(
    colDeletedRecords,
    {DId: ThisItem.Mon.ID},
    {DId: ThisItem.Tue.ID},
    {DId: ThisItem.Wed.ID},
    {DId: ThisItem.Thu.ID},
    {DId: ThisItem.Fri.ID},
    {DId: ThisItem.Sat.ID},
    {DId: ThisItem.Sun.ID}
);
ClearCollect(
    colDeletedRecords,
    Filter(
        colDeletedRecords,
        Not(IsBlank(DId))
    )
);
Remove(
    colMatrixFormatNew,
    LookUp(
        colMatrixFormatNew,
        MId = ThisItem.MId
    )
);

Here MId is the ID of each cell in the matrix form. When you click on the delete icon, it will create a collection with all the ID's that are part of that row. 

Then when you save the timesheet, you can use the same ID's to remove those records from the source:

 

If(
    CountRows(colDeletedRecords) = 0,
    "",
    ForAll(
        colDeletedRecords,
        Remove(
            'Timesheet.Details',
            {ID: DId}
        )
    );
    
);

Thanks so much for sharing - this is fantastic!  Can you share the method you used for the "Saving changes ..." status bar?

Screen Shot 2021-09-05 at 9.10.38 AM.png

kgovero
Frequent Visitor

johnnyshield
Advocate II
Advocate II

Hi @kgovero 
Didn't know about that component, but the process for creating a simple animation is as follows:

When the user clicks save, do the following

  1. Count how many actions you have to complete (e.g. 10 patch ops back to SharePoint)
  2. Start a timer, with duration = 1 second plus 0.5sec * patch operations (tune this to whatever you want). You can also keep it constant at - say 3 or 4 seconds.
  3. Create a blue rectangle with width = 400 px * timer value - it will expand as the timer runs to completion. This sits on top of a 400px grey rectangle

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,916)