Update a Subset of Child Rows?

[jaqueliyn]

Hi, folks.  I have a parent/child setup where I'm trying to update rows based on a 'ParticularChildID' column in the parent.  Updating children works fine when I'm basing it on the explicit parentkey to child relationship, but I don't want to update all related children.  Is there a way to add criteria to update just a subset of children?  Unfortunately, if I use a 'select' statement in the 'Referenced Rows' section in the action to pull based on other criteria, it just doesn't work at all.  I should note that the select statement is valid and tested otherwise.  

[Suvrutt_Gurjar]

You could elaborate , how and where you are using the SELECT() statement. In general, you should be able to use SELECT() statement to select a subset of child rows.

[MultiTech · Accepted solution]

This is on my list, towards the top, for a video to make - as this is an essential AppSheet skill.

I call this one:  Data Subsets

The process is surprisingly simple, yet the results are incredibly powerful

--------------------------------------------------------------------------------------------------------------

Setting Up A Data-Subset

The Slice

First you need to create a slice to hold the master list of records for your subset

This would be something like

• [Order_Status] = "Open"
• [Inspection_Status] = "In Progress"
• IsBlank([Record_Start_Date])
• [Record_Duration] <= "001:00:00"

The idea here is that this slice ALL the records in this status

The Subset Virtual Column

This is a clone of the system generated REF_ROWS()
  - Except that you change out the original table with your slice

• Original Formula:  REF_ROWS("Table", "Ref_Column")
• Subset Formula:    REF_ROWS("Slice", "Ref_Column")

-----------------------------------------------------------------------------------------------------------

This new VC will contain only the records that match the parent ID, but only those from the slice

In this way you're able to recreate the following brute force formula:

SELECT(Table[ID], AND([Parent_Ref] = [_ThisRow].[ParentID], [Status] = "Open"))

• But this method utilizes the efficiencies built into the system, avoiding problems when you scale your database to size

----------------------------------------------------------------------------------------

• In the next few weeks I hope to have a video about data subsets ready