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)

[Remco_Edelenbos]

This looks very nice! I did everything the same as in the app and in your post. I see with ‘test’ that my row meets the Action 1 criteria. But the counter keeps blank (i start blank) So i think my action is never invoked. What could be the problem?

@Steve

[Steve]

Please post a screenshot of your action 1.

[Remco_Edelenbos]

@Steve I updated my post

[Steve]

The action needs to be invoked in some way. In the sample app, the Form Saved event action for the control table’s form view is set to action 1. Did you do that?

[Fabio_J_Palacio]

Thank you!!!

[Manish_Jain11]

Hi Steve,

I am trying to implement this feature for my Despatch Slip, I have three tables that make the Despatch Slip (DS)

DS, DS_Items, DS_Items_PL

My First doubt is you have invoked Action 1 using the Form Saved Button , but how about the default Save (Add) ? The Action 1 is only to add rows in the target table but how about saving this record first in the Primary (Control) Table.

For Instance, I would need to add my DS (new record) to the DS Table and then based on the Item_List (my enum) in the DS, I would want to create rows in DS_Items Table with values from this Enum. Can you guide me ?

[Koichi_Tsuji]

Manish_Jain1:

For Instance, I would need to add my DS (new record) to the DS Table and then based on the Item_List (my enum) in the DS, I would want to create rows in DS_Items Table with values from this Enum.

Thats exactly what is solved by the tips introduced by @Steve
Read this detailed guidance carefully to learn the basic of his trick to get started.

[Nicolas_Feldman]

Thanks @Steve, this is really nice.
I followed all your instructions, but somehow Action 1, freezes the app, and all the tabs open in my browser for AppSheet, even my app gallery… This is really strange. I have to close all tabs in my browser to get it going again, and it all goes well until I invoke Action 1 by saving the form.
*Note: Control and Target Table are the same table

Here´s the screenshot for the actions, maybe you see something i don´t.

[Nicolas_Feldman]

Sorry, I didnt put Only If condition for ACtion 1. Its working now!

My next question would be: How to only get the rows with individual Enum?
I dont want to have as well the EnumList divided by commas in my database.
Is that possible?

I would like to eliminate or hide somehow that first row.
Thanks!

[Steve]

I don’t quite understand. Is the row with multiple items the control row, from which the others were created? And you want that control row removed after the individual rows are added?

[Nicolas_Feldman]

Hi @Steve, thanks again for your help!

Yes, that´s what I´m trying to do.
This is for an app which controls “meetings” for a biosphere reserve.
So, basically I have a table fed by a form that has all details of the meeting being created and to generate an automatic invitation to the meeting.
In this form I have an EnumList type column which enlists the “subjects” that are included in the meeting.
This should be enlisted in the invitation, but only those rows with a single item.

• Another way, that may be easier, would be to find a way to enlist the items directly in the report from the control row with all the items. This way I wouldnt need to have an extra table for the rows created for individual items, nor all this sequence of actions.

So, basically from:

EnumList data: First Item, Second Item, Third Item

Get a PDF report with this data:
You are invited to the meeting, these are the things we will talk about:

1. First Item [what would be the formula here?]
2. Second Item
3. Third Item

[Steve]

Steve:

And you want that control row removed after the individual rows are added?

Nicolas_Feldman:

Yes, that´s what I´m trying to do.

That’s just a matter of adding another action to delete the control row, then adding that action to the grouped action triggered by the addition of the control row.

[Steve_Chambers]

Steve:

That’s just a matter of adding another action to delete the control row, then adding that action to the grouped action triggered by the addition of the control row.

Hi Steve,

Thanks a million for sharing this, i have implemented it to ‘bulk create child rows’ and have no need for the control row either, i have added an action to delete the control row in the grouped sequence, however due to the recursive logic it looks the control row is being deleted after the first loop resulting in the first target row being deleted too

Do i need to apply your enum add count logic to the behavior in the delete action? if so, any ideas?

[Steve]

Steve_Chambers:

due to the recursive logic it looks the control row is being deleted after the first loop resulting in the first target row being deleted too

Please describe the logic your app is using. It’s likely going to be a simple change.

[Steve_Chambers]

Steve:

Please describe the logic your app is using. It’s likely going to be a simple change.

Thanks for replying, i have a 3 table hierarchy

Parent table - Orders
Child Table - OrderDetails (Control Table)
Grandchild Table - OrderDetail Materials (Target Table)

and i am trying to bulk create grandchild records that copy in the Parent Id, Child Id and enumlist values. I suppose the obvious fix is to create a separate control table instead of using the Child table, this way i wont need the delete action, doing it this way would create a many to many relationship between the Child and Grandchild if i have that correct.

The catch is i am wanting to execute your grouped action from the Grandchild table on a dashboard view, i have this working for the most part, using a linktoview overlay action

[Steve]

Steve_Chambers:

i have implemented it to ‘bulk create child rows’ and have no need for the control row either, i have added an action to delete the control row in the grouped sequence

So you want the OrderDetails row deleted after adding all the OrderDetail Materials rows?

[Steve_Chambers]

Steve:

So you want the OrderDetails row deleted after adding all the OrderDetail Materials rows?

Hi Steve, i have spent a bit longer with this feature and decided against giving it to the user as I believe the UX will be more intuitive with no delay when adding the child records. Using this enumlist method seems to take a while with my delayed sync turned on. Thanks for the assist tho

[Tiago_S]

Can you update records on Target Table if you edit (add or remove itens) from EnumList on Control Table? How to do that?

[Julio_Moyano]

Hi Steve

I tried to duplicate this exercise but I have issue because the “Add Count” not working, it is not counting “Enumlist”. I checked every step in actions with the same columns names from sample app but I did not success, also checked every columns in the table.

I copied the exercise “community30503” in my appsheet and it is working very well, but it does not work for me because I need to add this on another Apps.

is there some about “Add Count” I forget?

Thanks
Julio

[Julio_Moyano]

Hi, I found the issue

In the UX section (Control Table_Form) Behavior → Event Action, should be “Action 1”

[Mayank_Gupta]

Thanks a ton

[ren_scada]

How do you make the list on the enumlist that we add to have the same unique id?

[hapham]

Hi everyone!
I am looking for the most optimal solution to solve the following problem:

1. I have a list of training majors.
2. Each training industry has a list of profiles name such as GKSK, ID card, Photo, Degree, … each branch has a different number of profiles.
3. When students register for training, they must submit a complete list of profiles.
4. Faculty wants to select a training major to export a CSV list of registered students.
   Each download, depending on the training industry, the downloaded file will have columns that are the names of each profile by that industry.
   Kindly support me! Thanks!

[Kyle_Love]

Thanks Steve!
This worked great for my app! I’ve been trying to solve this for about a week. Goal was to create a pre-generated set of child work order tasks based on the parent work order type when the parent work order was created! Virtual list column on the parent work order to link the master task list table and this workflow solved the problem!

[lle3]

Hey @Steve !

Thanks for this - its helped a lot in getting my project rolling! However, an issue I’ve noticed, and was wondering if I could get help in solving, is that updating (editing) the original control table will not update the target table. A workaround I think would be good is to delete the initial values associated with the first lot of values from the original enumlist (creation) and redo the ‘add row per value’ process again for the new enumlist. Is this possible? and if so, how?

Cheers,
Linda

[Steve]

lle3:

updating (editing) the original control table will not update the target table.

That’s true, as the intent of my solution was to copy a snapshot-in-time, not to establish an ongoing relationship. What you want to do is more complicated. I do not have a suggestion for you at this time.

[Antonio_Depetro]

hi @Steve,

do you have any solutions for this now?

[Matthew_Brown]

lle3:

ring if I could get help in solving, is that updating (editing) the original control table will not update the target table. A workaround I think would be good is to delete the initial values associated with the first lot of values from the original enumlist (creation) and redo the ‘add row per value’ process again for the new enuml

Hi @lle3 Linda…
I just had the same issue and used a bot to make it work. Create the following:

1. BOT that runs on all_changes to control table

2. Process to clear out all records on target table (create/call an action “Data: execute action on set of rows”, for records of this table: Control, referenced rows: target, ref rows (select(ConversationDetail[RowKey], [ConvoKey]=[_THISROW].[ConvoKey]), ref’d action: delete)

3. Process to call @Steve s Grouped action above

4. Process to reset counter on control table to zero

Hope this helps, thanks!
Matt

[Antonio_Depetro]

Hi @Matthew_Brown,

I created the process that deletes previously entered rows as you recommended.

Can you give me some advice on how to do this?

1. Process to reset counter on control table to zero

---

Thank you.

Antonio

[Melanie_Rodel]

Hi @Steve !

Thank you, that was the only example about loops I found that I actually understood! It's confusing but pretty awesome!

I copied your syntax to implement a nested loop. But it was a pain.

I want to create a list of tasks from a list of locations (office, storage,...) and a list of task categories (lights, furniture, ...). Meaning if there are 3 locations with 10 categories each, it will create 30 tasks. Each time the action is triggered, it's supposed to use a list of locations from one table (the number of locations in the list can vary because each building has different locations) and a list of categories in its own table (which is fixed). 

I got it running, but I doubt anyone else (or I myself in a couple of weeks) will be able to understand what I built because it requires 11 actions in total to run the two loops and reset all the counters.

Is there a more elegant solution than the "group actions" you used, now that there are  workflows, bots, events, processes and tasks? I don't understand the difference between them.

[Dan_Velus]

Hi @Steve 

Thank you for this excellent solution and the article that explains it very clearly. 

I have a problem, I hope you can point me in the right direction.
When I do my 'Control Table' form for the first time, it works fine in splitting the Enum List into individual rows of data in the 'Target Table'. However, when I go to edit the entry and add another 2 new values to the Enum List field, The 2 new records are not added to the Target table. It doesn't matter how many new entries I add, the last value in the list gets repeated in the Target table.

For Example:
1st-time form submission: 

Control Table, Enum List column: Lisa Boss, Anna Clement, Tom, Joyce
Target Table, Enum column: Lisa Boss
                                                       Anna Clement (next row)
                                                       Tom Joyce (next row)
1st Edit form submission: 
 Control Table, Enum List column: Adam Boss, Lisa Boss, Anna Clement, Brad Clement, Tom, Joyce - (2 new names added on edit)
Target Table, Enum column: Lisa Boss
                                                       Anna Clement (next row)
                                                       Tom Joyce (next row)
                                                       Tom Joyce (next row) - (Since Tom Joyce was last on the Enum list)

2nd Edit form submission: 
 Control Table, Enum List column: Adam Boss, Lisa Boss, Anna Clement, Brad Clement, Tom, Joyce, Johnny Zeek, Joanne Zeek - (2 new names added on 2 nd edit)
Target Table, Enum column: Lisa Boss
                                                       Anna Clement (next row)
                                                       Tom Joyce (next row)
                                                       Tom Joyce (next row) 
                                                       Joanne Zeek (next row) - (Since Tom Joyce was last on the Enum list)                 

Hope my explanation makes sense. 

It works on the first entry but it doesn't work when you edit the first entry.

Please help.

[JuanIsidoro]

Very good, I was looking for something similar. It was very useful to learn! ^^
I'll work now on trying to make it not just add one row. If not you can edit and delete rows based on ENUMLIST changes.

In other words, as if it were a subscriber to the ENUMLIST that then changes the registered IDs or deletes them, depending on the ENUMLIST (as a dynamic variable).

I've searched and haven't seen anything similar.

If I manage to implement it, I will comment here.

Thank you very much!!

[rakesh7736]

what change i need to do if there is more columns in the control table, and I need the all the other column in the target column

[se]

i'm also trying to work this out - I have multiple columns in the control table (one being the enumlist that is being split). The enumlist is effectively being split and moved across to new rows in the target table, however, the other column fields are appearing blank.

[miguelo]

¿Pudiste resolverlo? 

[Justi]

Hi @Steve
I did all the steps and it seems to work fine. But when all the actions are finished, the rows of the "Target Table" are deleted and only the last one remains, could you please help me? how can i solve this?

[da_common]

Same, its just updating the same row in place one by one till only the last value is updated. The count is updating as well.

Any tips?

Thanks in Advance

[robcorvus]

Just used this, thanks, very useful