Blog

Data - How to build, edit, update records in nested child tables

When working with collections, we may want to model a data structure that contains child tables. This post summarises the syntax to to create, edit, and to update records in child collections.

Collections play a key role in building apps. They offer a way to store local working data that's specific to a user and a session.

It's very simple to create a simple structures of data by calling the Collect or ClearCollect functions. What isn't so obvious is how to create, update, and and to edit child nested tables inside a collection. This post summarises the formula to carry out these tasks.

Creating a collection with a nested child table

To demonstrate how to create a collection with a child table, let's take the example of a data structure that stores customer and order details. The parent table stores the details of customers. This table will include a field called orders that contains a nested child table with order records that pertain to the customer.

The syntax to create this structure looks like this:
ClearCollect(colCustomerOrder, 
            {CustomerID:1, 
             Surname:"Leung", 
             Firstname:"Tim", 
             Orders:Table({OrderID:1, Product:"Widget", Quantity:1})},
            {CustomerID:2, 
             Surname:"Smith", 
             Firstname:"Sally", 
             Orders:Table({OrderID:1, Product:"Books", Quantity:2})}
)
We can attach the above formula to the OnSelect property of a button, and we can click the button to build the collection.

How to display a collection with a child table in a gallery control

Once we build this collection, we can display the contents in a gallery control. The screenshot beneath illustrates an example. The main steps to build this gallery are:

  • Add a gallery control and set the Items property to colCustomerOrder
  • Add labels to display the surname and forename. For example, we can set the text property of a label to ThisItem.Surname to display the surname
  • Add a nested child gallery to display the orders for each customer. We would set the Items property of the child gallery to ThisItem.Orders


Adding records to a nested child table

To add a record to a nested table, we use syntax that looks like this: 

With({parentRecord:LookUp(colCustomerOrder, Firstname="Sally")},
     With({orderTable:parentRecord.Orders},
          Collect(orderTable, {OrderID:3, Product:"DVDs", Quantity:1});
          Patch(colCustomerOrder, parentRecord, {Orders:orderTable})
     )
)
This formula looks up the parent record where the firstname matches "Sally". It retrieves the order table for this record, and assigns this to orderTable.

Next, the formula calls the Collect function to add a new record to the order table, and finally calls Patch to patch the updated record back into the colCustomerOrder collection.


Updating records in a nested child table

To update a record to a nested table, we can use syntax that looks like this: 

With({parentRecord:LookUp(colCustomerOrder, Firstname="Sally")},
     With({orderTable:parentRecord.Orders},
          UpdateIf(orderTable, Product="DVDs", {Quantity:5});
          Patch(colCustomerOrder, parentRecord, {Orders:orderTable})
     )
)
This formula uses the same pattern as the previous formula but instead of calling Collect to add a record, it calls UpdateIf to update the quantity of the order where the product description matches "DVDs" product, to 5.


Deleting records in a nested child table

To delete a record to a nested table, we can use syntax that looks like this: 

With({parentRecord:LookUp(colCustomerOrder, Firstname="Sally")},
     With({orderTable:parentRecord.Orders},
          RemoveIf(orderTable, Product="DVDs");
          Patch(colCustomerOrder, parentRecord, {Orders:orderTable})
     )
)
This formula calls the RemoveIf function to remove from the "Sally" record, the order row where
the product description matches "DVDs".

Conclusion

When we build a collection, there may be the requirement to work with nested child tables. This post summarised the syntax to build, add, update, and to delete child records in nested tables.
Previous
Next
Related posts