Easily transfer child records to a new parent

Hi all,

I have the following hierarchy in my data tables: Commitment > Mission > Practice > Reflections.
I have a scenario where I want to change a mission from one commitment to a different commitment.
I then want to be able to run a workflow to gather all the grand-child and great-grand child records and automatically update them to the new commitment ID.

In order to do this in appsheet, I imagine I need to run a select list of all child records

  • practices with the commitment ID before value

  • reflections with the commitment ID before value

And then update them with the Commitment ID after value

Is this the most efficient way to make this change app side?
I could really use help coming up with the actions, workflow and expression to execute this.

UNTESTED!!!

(Action 1)

  • For a record of this table: Reflections
  • Do this: Data: set the values of some columns of this row
  • Set these columns:
    • Commitment ID: [Practice ID].[Commitment ID]
  • Only if this condition is true:
    OR(
      ISBLANK([Commitment ID]),
      NOT([Commitment ID] = [Practice ID].[Commitment ID])
    )
    

(Action 2)

  • For a record of this table: Practice
  • Do this: Data: set the values of some columns of this row
  • Set these columns:
    • Commitment ID: [Mission ID].[Commitment ID]
  • Only if this condition is true:
    OR(
      ISBLANK([Commitment ID]),
      NOT([Commitment ID] = [Mission ID].[Commitment ID])
    )
    

(Action 3)

  • For a record of this table: Practice
  • Do this: Data: execute an action on a set of rows
  • Referenced Table: Reflections
  • Referenced Rows:
    FILTER(
      "Reflections",
      ([Practice ID] = [_THISROW])
    )`
    
  • Referenced Action: (Action 1)

(Action 4)

  • For a record of this table: Practice
  • Do this: Grouped: execute a sequence of actions
  • Actions:
    • (Action 2)
    • (Action 3)

(Action 5)

  • For a record of this table: Mission
  • Do this: Data: execute an action on a set of rows
  • Referenced Table: Practice
  • Referenced Rows:
    FILTER(
      "Practice",
      ([Mission ID] = [_THISROW])
    )`
    
  • Referenced Action: (Action 4)
4 Likes

Wow.
That’s completely different than what I tried. I’m so glad I came to ask the experts.

I’ll give this a try Steve. Thank you.

e

3 Likes

This doesn’t seem to work and there’s no audit trail in the log so I can’t trace where it might be failing.
Just to be clear your proposed method is entirely action based. It doesn’t need a workflow trigger right?

It doesn’t need a workflow, but it does need a trigger of some sort. You could use a workflow, or as a Form Saved action, or a button the user presses.

2 Likes

Ok perfect. I’ll work that out. And let you know how it goes here.

Thanks again.

1 Like

Ok that’s working great.
the workflow trigger updated the practices as expected but the reflections.
I had to make one tweak to the Action 1 to pull the commitment from the row that references mission instead of practice (just based on the way I set up my tables ) but all seems to be working now.

2 Likes