Hi, so I have created an app that does this, turns out I can't post images / or youtube links without this post being rejected.
You have to group the data manually to get it into a matrix shape, then unfold it again once you're done. Because this is manual, you want to limit the number of columns you want to view in the app, as each column means repeating code. I chose 7, as we're looking at week long blocks of data.
Steps involved
1. Load filtered SharePoint list data into a collection on app start (so you're only getting 500 rows) - put this in App Start
// Set today's date
Set(var_date, Today());
// Collect data from SharePoint list (filter this appropriately)
ClearCollect(Col_timesheet, SharePointList)
2. Set a target date (var_date), then set variables for the Mon - Sun dates; I have a datepicker to change the var_date as well
// Set the dates of the selected week, used in the gallery
Set(var_mon,var_date-Weekday(var_date,MondayZero));
Set(var_tue,var_date+1-Weekday(var_date,MondayZero));
Set(var_wed,var_date+2-Weekday(var_date,MondayZero));
etc.
3. Pivot your data into a matrix shaped collection; this and step 2 are on a date change button ("Jump" in my video)
// Use GroupBy and AddColumns to move this into matrix format. The filter chops off the data based on the Monday - Sunday range to be displayed.
ClearCollect(Col_WeekMatrix,
AddColumns(GroupBy(Filter(Col_timesheet,(Date >= var_mon) && (Date <= var_sun)),"Title","GroupedData"),
"Mon",First(Filter(GroupedData,var_mon=Date)),
"Tue",First(Filter(GroupedData,var_tue=Date)),
etc.
//For this to work as an editable gallery, you can only have one entry per Title per date (hence First entry), you will need to sort out error handling for duplicates if they exist. If you are just reporting totals, replace First with Sum.
4. Create a gallery
//Gallery contents
Col_WeekMatrix
5. Add a label in your gallery to display the row titles
//Row labels
ThisItem.Title
6. Add 7 text inbox boxes in your gallery, to show the values for the days of the week. In my case I am displaying the timesheet hours booked.
//Text input box for Monday
ThisItem.Mon.Hours
//Text input box for Tuesday
ThisItem.Tue.Hours
etc.
7. It is much easier for the PowerApp developer to patch each item OnChange, however I have found that it kills the app performance, you lose your text input focus and it screws up tab order. Instead I have a save button outside the gallery, which saves the gallery edits to a list shaped collection, which can be patched back to SharePoint.
//Create the list shaped table that can be patched back to SharePoint
Clear(Col_PatchBack);
// We are only patching back changes, so filter the gallery based on changes vs the matrix shaped collection that fed into the gallery
// You need to repeat this for each day (column) and append it to the list - there is no programmatic way to loop within PowerApps (Flow could do it)
Collect(
Col_PatchBack,
ForAll(
GalleryTimesheetWeek.AllItems,
If(
Mon.Hours <> Value(TextInput_Mon.Text),
{
pbTitle: Label_Title.Text,
pbHours: Value(TextInput_Mon.Text),
pbID: Mon.ID,
pbDate: var_mon,
}
)
)
);
// Repeat the above code block 6 more times, changing Mon to Tue etc.
8. You now have a collection shaped like your source list, which you can patch back to SharePoint or your datasource. I don't think you can use Patch alone, as you won't have all the same columns (well I couldn't get it to work!). I use pb... prefix so I don't have to think about disambiguation problems.
// Clean up SharePoint list by removing any new zero hours records
RemoveIf(
SharePointList,
ID exactin ( Filter(
Col_PatchBack,
Or(
pbHours = 0,
IsBlank(pbHours),
IsEmpty(pbHours)
)
).pbID)
);
// Then remove the same records from the patchback collection
RemoveIf(
Col_PatchBack,
Or(
pbHours = 0,
IsBlank(pbHours),
IsEmpty(pbHours)
)
);
// Update existing items in SharePoint
ForAll(
Filter(Col_PatchBack, Not(IsBlank(pbID))),
UpdateIf(
SharePointList,
ID = pbID,
{
Hours: If(IsBlank(pbHours),0,pbHours),
Date: pbDate,
}
)
);
// Finally add new items to SharePoint
ForAll(
Filter(Col_PatchBack, IsBlank(pbID)),
Patch(
SharePointList,
Defaults(SharePointList),
{
Title: pbTitle,
Hours: If(IsBlank(pbHours),0,pbHours),
Date: pbDate,
}
)
);
*if someone can improve my code, please let me know. e.g. Remove / RemoveIf delegation issue.
There's a bunch of other stuff in the app, which I won't detail like using a timer to save back to SharePoint before refresh; some error handling; setting current user and patching that back to SharePoint. Let me know if you need a hand.
Cheers, Johnny
