Looping based off of a count to create new child records from a parent under a grandparent

AppSheet AppSheet Q&A
,
1

I am trying to create a feature where a user can add a grandparent record, and create parent records within that which contain a count of children, and then when the grandparent record is saved the child records get created for each parent. I apologize if that is too abstract. I have created an app which will create one child record for each parent, but it is not properly looping through the count of children to create more than one record for each parent. What am I doing wrong?

Here’s a sample app that I’ve created.

For further background: I consulted this video which was very helpful and from it I was able to create a simple “parent creates children” interaction, but I have struggled to get this to work with one more layer of hierarchy. In my current iteration I’m not actually using a Numbers table nor a virtual column as the video shows and as I did in my earlier iteration. Maybe that’s my problem? I never really understood why the Numbers table was necessary. Either way, the automation is not looping through the “child creation” process as I intended. I’m going to paste some gory details below, and thanks in advance to anyone who can point me in the right direction.

=======

This is how I created the sample app linked above:

Generalized grandparent, parent, child pass down

  • Create a table for Grandparents

    • Grandparent id

  • Create a table for Parents

    • Parent id

    • Grandparent id

    • Count of children

  • Create a table for Children

    • Child id

    • Parent id

  • Add all of those tables to an Appsheet app

  • Set the Parent id column of the Children table to a ref of the Parents table

    • Is part of? Yes

  • Set the Grandparent id column of the Parents table to a ref of the Grandparents table

    • Is part of? Yes

  • Create an action in the parents table that creates a single new record in the Children table

    • Name: Create one new child

    • Data: add a new row to another table…

    • Add to: Children

    • Set columns Parent id to [_THISROW].[Parent id]

  • Create an action that decrements the Count of children in the Parents table

    • Name: Counter downer

    • Data: set the values of some columns in this row

    • Set these columns: Count of children = [Count of children]-1

  • Create an action which groups these two actions together called Grouped action to loop

    • Behavior > Only if this condition is true = [Count of children]>0

  • Create an automation

    • Event: Adds to the Parents table

    • Condition: [Count of children]>0

    • Process: Run a data action, Grouped action to loop

Testing

  • Does saving the Grandparent record create parents? Yes

  • Does it create children with the right parent id? I think so?

  • Does it create the right number of children for each parent? No

2

Here is my typical approach to similar problems (and the beautiful thing is that it all happens client side)

Table Requirements:

  • Looper
    • Loop_Num: Number (I like to do 500 records with 1-500 in this column)
  • User_Variable
    • Email: The current users email (Initial value of USEREMAIL())
    • record_id: A way to store the current primary key value of your parent table
    • any other variables you may want to store…
    • Make sure you security filter this table [Email] = USEREMAIL()

Actions:

User_Variable

  • name: INPUT record_id (instructional video linked)
  • type: set the values of some columns in this row
  • input: *make sure it is the same name as the variable you reference in your [_INPUT].[my_variable]

Looper

  • name: add child rows
  • type: add rows to another table…
  • secondary table: child_table
  • columns:
    • primary_key: UNIQUEID()
    • parent_table_fk: ANY(User_Variables[record_id])
    • Only applicable if you are looping off of an enumList in parent table:
      • indexed_column: INDEX( SPLIT(TEXT(SELECT(parent_table[enumList_column], [id] = ANY(User_Variable[record_id]))), “ , “),[Loop_Num])

parent_table

create user var if not exists

  • name: create user var if not exists

  • type: add rows to another table…

  • secondary table: User_Variable

  • column: Email → USEREMAIL()

  • condition: COUNT(User_Variable[Email]) = 0

create children

  • name: create children

  • type: execute action on a set of rows

  • secondary table: Looper

  • ref list: [children_count_column] OR LENGTH([enumList_column])

  • ref action: add child rows

create children (grouped)

  • name: create children (grouped)

  • type: Grouped

  • sub actions:

    • create user var if not exists
    • create children
    • (optional navigational action to take the user to the appropriate view)

Let me know if you would like to discuss further over a web call or something.

2 Likes
3

Try these search results.

2 Likes