Perform CRUD operations on expanding datasets in SharePoint (Part 2)

This post is a part of an ongoing series. If you are new to this topic, I highly recommend going through my previous posts:

1. How to use SharePoint lists to store expanding datasets

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

So far, we have created an expandable dataset in SharePoint and viewed data with the help of PowerAutomate and nested galleries.

In this post, we will discuss how to create records in a dataset like this.

Let’s start..

1. Open the “Time Entries Screen”
2. Add 4 input fields to capture new time entry data

StartDropDown : Items = ["01:00","02:00","03:00","04:00","05:00","06:00","07:00","08:00","09:00","10:00","11:00","12:00","13:00","14:00","15:00","16:00","17:00","18:00","19:00","20:00","21:00","22:00","23:00","00:00"]
EndDropDown : Same as the start
Duration : Text = DateDiff(DateTimeValue(StartDropDown.Selected.Value),DateTimeValue(EndDropDown.Selected.Value),Hours)
Description : Text input field

** Note that I am using one dropdown for start time just for this example, you can use complex date time inputs for this. (i.e. AM/PM)

The idea here is to add this new record into the daily entries sub gallery as another row.

DailyEntriesGallery is taking data from the ScheduleObj. If we can add the new record to ScheduleObj, it would show up in the daily entries table.

Let’s try to patch the ScheduleObj now. There are multiple methods of doing this.

Option 1 – Using concat function to recreate the daily entries table and then add the new entry. Here’s a forum post on how to use this for nested record creation.

Option 2 – Since we already have existing records in the table above, we can simply add them into a collection and add the new record to that as well. Then we can patch the ScheduleObject with this new collection.

Here are the steps:

1. Add a save button next to new input fields
2. Create a collection using existing daily time entries

OnSelect

Clear(colDailyEntries);
// Create collection with existing gallery items
ForAll(
DailyEntriesGallery.AllItems,
Collect(colDailyEntries,{
Start: ThisRecord.Start,
End:ThisRecord.End,
Duration:ThisRecord.Duration,
Description:ThisRecord.Description
}
)
);

3. Add new record to the collection

//Adding new record to the collection
Collect(colDailyEntries,{
Start: Text(StartDropDown.Selected.Value),
End:Text(EndDropDown.Selected.Value),
Duration:Value(DurationLabel.Text),
Description:DescriptionInput.Text
})

4. Test if the records are populated correctly in the collection

Now we need to patch the schedule object with this new collection for that day.

If we try to patch now, there’s an error “First argument of a patch should be a collection”. Reason for this is that we are using “Set” method to assign flow response. This creates an object not a collection.

There is an easy fix for this. change the “set” to “ClearCollect” in the flow run action.

// Patch ScheduleObj with the new collection
Patch(ScheduleObj,LookUp(ScheduleObj,Day=ThisItem.Day&&Week=ThisItem.Week),{DailyTimeEntries:colDailyEntries})

Complete OnSelect value

As the final step of this update, we will have to patch the SharePoint list record with the new ScheduleObject.

5. Add a “Save Schedule” button at the top of the app

OnSelect value

//Patch Sharepoint List

Patch('User Time Entries',LookUp('User Time Entries',ID=Gallery1.Selected.ID),{'Time Entries':JSON(ScheduleObj)});

//Clear all collections
Clear(ScheduleObj);
Clear(colDailyEntries);
Refresh('User Time Entries');
Navigate(Screen1);

6. In order to use this data structure, SharePoint list field should have the JSON Schema. What we can do is set the default field value in SharePoint column to something like below:

[
  {
    "Week": 1,
    "Day": "Sunday",
    "DailyTimeEntries": [
      {
        "Start": "",
        "End": "",
        "Duration": 0,
        "Description": ""
      }
    ]
  },
  {
    "Week": 1,
    "Day": "Monday",
    "DailyTimeEntries": [
      {
        "Start": "",
        "End": "",
        "Duration": 0,
        "Description": ""
      }
    ]
  },
  {
    "Week": 1,
    "Day": "Tuesday",
    "DailyTimeEntries": [
      {
        "Start": "",
        "End": "",
        "Duration": 0,
        "Description": ""
      }
    ]
  }
]

When I tried setting up default value in SP column under column settings, it kept on formatting the text.

I created a simple Power Automate flow to update the column value with schema when a new item is created.

All set, time to test!

This method could be used for any data source that has enough column space to store objects as text.

You can define your objects according to the business needs and then use the same methods to read/write to datasource.

Happy Learning!

---

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

---

Categories: Uncategorized