Using SharePoint list for expanding datasets

Recently one of my clients asked to create an app to store daily time entries for their users. Dataverse was not an option as their requirement was to create this dataset in SharePoint.

Below is the use case and technical challenges:

1. For all users, they needed to update daily values for 2 weeks : 14 Days
2. Each day would have an average of 5 records
3. For each user, there will be ~ 70 time entry records
4. Total user count is ~1000

If I look at this data set as one table, it goes beyond 70k records maybe more depending on the individual time entries.

While storing 70k records in SharePoint was not an option, I looked into different methods of implementing this data structure.

Since PowerApps is working with JSON data, we can try to visualize this in JSON.

Let’s assume that we can create an array to store all the time entries for a specific user. For now, we can call this TimeEntryArray.

1 . User Object

{
"UserID":"SomeID",
"UserName": "John Doe",
"Time Entries": [TimeEntryArray]
}

This way we would need only 1000 SharePoint list records instead of 70k.(1 for each user)

2 . TimeEntryArray

Let’s create a time entry array for 14 days now. Each day could be an array item.

Assumption here is there will be an object storing daily time entries for each day. For now, let’s call this DTArray

TimeEntries: [
{
"Week" : 1,
"Day" : "Sunday",
"DailyTimeEntries": [DTArray]
},
{
// Goes on until Week 2 Saturday
{
"Week" : 2,
"Day" : "Saturday",
"DailyTimeEntries": [DTArray]
}
]

3. DTArray

Similar to previous step, we can design the daily time entry array now. This will have below information for each daily entry.

Start Date time/ End Date Time/ Duration/ Description

[
{
"Start": "",
"End":"",
"Duration":2.5,
"Description": "Some Description"
}
 ]

4. Create the complete user object

We can now put everything together and create the final user object. This object will have an array for each day and a nested array for daily time entries.

{
  "UserID": "SomeID",
  "UserName": "John Doe",
  "Time Entries": [
    {
      "Week": 1,
      "Day": "Sunday",
      "DailyTimeEntries": [
        {
          "Start": "",
          "End": "",
          "Duration": 2.5,
          "Description": "Some Description"
        }
      ]
    }
  ]
}

Now let’s create a SharePoint list to store data in this format. We only need 3 fields in this list

Create a multi line text field to store time entries

This data structure reduced 70k records down to 1000 by storing time entry records in a single nested array and writing it to a multi line text field. This text field can now handle any number of daily entries without increasing the rows/columns in SharePoint list.

Now that we have successfully created a data structure for this use case, we can move to performing CRUD operations on this dataset using PowerApps

---

‹ Transpose table columns using Power Automate (With example)

Perform CRUD operations on expanding datasets in SharePoint (Part 1) ›

---

Categories: PowerApps, SharePoint