Easily transfer child records to a new parent

[elan_Bailey]

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.

[Steve · Accepted solution]

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)

[elan_Bailey]

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

[elan_Bailey]

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?

[Steve]

elan_Bailey:

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.

[elan_Bailey]

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

Thanks again.

[elan_Bailey]

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.