FAQ: add row per value in EnumList

[Steve]

Add one row per value in an EnumList column list.

Try the sample app!

For this example, the table from which the row-addition process will be initiated is called Control Table. The table to which new rows will be added is called Target Table. Use your own names as you see fit. Note that they could even be the same table. Control Table must allow updates; Target Table must allow adds.

Control Table must have at least two columns for this process: EnumList (type EnumList; a list of Enum values; may be virtual); and Add Count (type Number; the number of the next row to add; may not be virtual). Column names may be changed as desired.

Add Count should be blank or zero initially.

Action 1: Add Rows for Remaining Values

This is the action that you should invoke to add the rows: it does everything.

• For a record of this table: Control Table
• Do this: Grouped: execute a sequence of actions
• Actions: (added below, after creating actions 2, 3, and 4)
• Only if this condition is true:

  if(
    isblank([Add Count]),
    isnotblank([EnumList]),
    ([Add Count] <= count([EnumList]))
  )
  

Action 2: Increment Count

This manages the Add Count column value, which contains the index of the next value in the EnumList for which a row is to be added. It’ll start at 1 and increment to the count of items in the EnumList.

• For a record of this table: Control Table
• Do this: Data: set the values of some columns in this row
• Set these columns:
  • Add Count: ([Add Count] + 1)
• Only if this condition is true: TRUE (or blank)

Action 3: Add Row For This Enum

This action adds one row to the Target Table table for the Enum value of this row’s EnumList at the position indicated by Add Count (but only if the value isn’t blank).

• For a record of this table: Control Table
• Do this: Data: add a new row to another table using values from this row
• Table to add to: Target Table
• Set these columns:
  • Enum: index([EnumList], [Add Count])
  • (other columns as desired)
• Only if this condition is true: isnotblank(index([EnumList], [Add Count]))

Action 4: Add More Rows

This action implements a loop by recursively performing action (1) for each successive value in the EnumList.

• For a record of this table: Control Table
• Do this: Data: execute an action on a set of rows
• Referenced Table: Control Table
• Referenced Rows: list([_thisrow])
• Referenced Action: (action 1)
• Only if this condition is true: TRUE (or blank)

Action 1

Return to action (1) and add actions (2), (3), and (4) to its Actions list.

• Actions:
  • (action 2)
  • (action 3)
  • (action 4)

[Mode]

Hi steve,

I have used your idea above thanks for that genius idea.

I have changed a few things to make it editble friendly, so with the changes it will update the target table rows based on the control table enumlist field on edit.

Note: I made it ref table so you have to have another coulmn in the target table called Parrent, and you can remove the coulmn "add count" in the control table.

Action 1: Add Rows for Remaining Values

This is the action that you should invoke to add the rows: it does everything.

• For a record of this table: Control Table
• Do this: Grouped: execute a sequence of actions
• Actions: (added below, after creating actions 2, 3, and 4)
• Only if this condition is true:

[EnumList]<>[Related Target Table][Enum]

Action 2: Delete voided target table records

This will delete all records that you removed from the enumlist coulmn on an edit

• Do this: Data: execute an action on a set of rows
• Referenced Table: Target Table
• Referenced Rows:

select(Target Table[Key],and([Parent]=[_THISROW].[Key],not(in([Enum],[_THISROW].[Enumlist]))))

• Referenced Action: (Delete)
• Only if this condition is true: TRUE (or blank)

Action 3: Add Row For This Enum

This action adds one row to the Target Table table for the Enum value of this row’s EnumList (it will deduct all current records and add only new ones added to EnumList, so it will support edits)

• For a record of this table: Control Table
• Do this: Data: add a new row to another table using values from this row
• Table to add to: Target Table
• Set these columns:
  • Parent: [Key]
  • Enum: index(([Enumlist]-[Related Target Table][Enum]),1)
  • (other columns as desired)
• Only if this condition is true: isnotblank(index(([Enumlist]-[Related Target Table][Enum]),1))

Action 4: Add More Rows

This action implements a loop by recursively performing action (1) for each successive value in the EnumList.

• For a record of this table: Control Table
• Do this: Data: execute an action on a set of rows
• Referenced Table: Control Table
• Referenced Rows: list([_thisrow])
• Referenced Action: (action 1)
• Only if this condition is true: TRUE (or blank)

Action 1

Return to action (1) and add actions (2), (3), and (4) to its Actions list.

• Actions:
  • (action 2)
  • (action 3)
  • (action 4)