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

[NewOzzie]

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

Thanks,

Craig

[rahulswimmer]

@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]

@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).

[johnnyshield]

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

[johnnyshield]

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.

[rahulswimmer]

@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}
        )
    );
    
);

[kgovero]

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

[kgovero]

It looks like it may be a modified version of this component? https://powerusers.microsoft.com/t5/Canvas-Apps-Components-Samples/Saving-Animation-Component/td-p/4...

[johnnyshield]

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

[Abdou2]

Hi @johnnyshield, Your solution is quite impressive, I have similar project, Could you please help with quick fill functionality and the controls? I am bit thrown away.Thanks

[Attila_the_Hun]

Hi @johnnyshield,

I use a PowerApps based timesheet at work as well which is not quite as refined as yours. I love what you've done with your app, looks great.

I also appreciated your interesting approach to shaping your data out of SharePoint, then back in.

As a PA developer, SharePoint has been a point of contention for some time 😛