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)

[BobSig]

Thanks Mode and Steve,

I was able to make this work.  It took me a while to figure out the correct expressions, but, finally made it.  I originally attached this action group to the save button on the form of the Control Table.  That worked to copy the individual rows to the Target Table.  However, when I would EDIT the EnumList on an existing Control Table row, I had trouble updating the Target table.

I solved this by removing the Action from the Form Save button and configured the action as a BOT.  The BOT is set to run any time there is an ADD or UPDATE to the Control Table.  Seems to be working fine.  Will continue to test more as I built my app. 

Thanks again for your example.

[Dvd]

Hi Steve,

This Add one row per value in an EnumList column list is a lifesaver for me. I've set everything up and works ok for me.

My Control table is also the target table.
The only thing I see in the data of the table , is that the initial row where the actions are triggerd from by saving the form, stays in the data table after creating all the new rows from the selections in the enumlist. see example

I'm trying to figure out how to delete this first row , because this leaves an empty value on the [enum] which to me is no more needed.
I'm kinda stuck here on how to resolve this.

I was thinking of removing the row with id and made 2 columns, to figure out how it works.

Column "Copy of id on adding row" = id of first row is added to the new row in action 3 also added adcount to see how it counts.
Column "Copy of id of first row" = id is copied to this column in action 2

I would like to thank you in advance for your time.

Kind regards Didier

[Dvd]

Update I added an action 5 --> delete row in action 1
This action is executed after the complete enumlist has been copied to a row.

The first row is not deleted but cleared, so i need to figure out how to remove the empty row from the google sheet.

An other approach could be, that i'm trying to figure out, is to copy the last value in the enumlist to the first row with all the enumlist values. So I don't have to delete anything and worry about an empty row in my database.

This is because my control table is also my target table.

[Dvd]

Resolved my issue when "controltable is also the targettable"--> got 2 solution
1st  is to delete (clear) the row with a fifth action delete row. which then you need a script to effectively remove the empty row in your database

2nd see below

In action 1
Added -1 to count(Enumlist) and removed = (because i need to have 1 value for the first row.

if(
isblank([Add Count]),
isnotblank([Selectedcodes]),
([Add Count] < count([Selectedcodes])-1)
)

In action 2
Add count = (add count +1) because i need the first item in the enumlist to be in the first row, so start adding row with the second value in the enumlist

In Action 3  add the second item in the enumlist
Enum = index([enumlist], [Add Count]+1)

In action 5
Add the first item in the enumlist in the first row

Data set the values of some collimns in this row

Enum=index([Enumlist], 1)

behavior is true

[Aditya_K1999]

@Steve I have done as per the instructions. But it is telling me "Service Name is invalid". In my app service name is the enumlist, with a base type of ref.

[Dvd]

@Aditya Try changing base type to text

[Aditya_K1999]

I can try that but that field is actually referring certain values.

[Cubicae]

Many thanks for the explanation, really helpful 👍

[Waorives]

Firstly I would like to thank you for your topic, @Steve . It was a big step in my project.

Just a question. I have a Set Table (Parent Table) where these Sets are made up of pieces (Child Table). When I insert the parts (child table) into the Sets Table (Parent Table), these values ​​go to a third table that I call Detail Sets.
I used your topic because I would like to replicate some sets that have the same pieces, just with changing quantities. When starting the action, it selects the products from the EnumList and places it in the Detail Sets table, however I would like it to pull the value from other cells in the row, for example, quantity.

I'm new to Appsheet and I can't say if this is an easy thing, but I've been struggling with it for a few days.

Your topic was a big step forward.

[myra_o]

My target table just keeps re-loading with different data in the same row. Never making new rows in the target table. I have re-read and compared my app to this and still have no idea what is making this happen. Anyone else have the same issue and found the solution?

[myra_o]

Naturally I found the solution out right after I posted this. The target table has to have a key value and specifically for me a initial value of uniqueId() in order for it to work. 

[Steve]

---

@myra_o wrote:

My target table just keeps re-loading with different data in the same row. Never making new rows in the target table.

---

Make sure the new row is getting a new, unique key column value. It sounds like it isn't.