How to duplicate a row with Ref Virtual Field

[ejprado]

Hello, I want to copy/duplicate a row in the same table but I want to duplicate the related rows that it has associated. 

Here is the action that I created but I don't see in the list of fields the Virtual Field (Rel List).

Any ideas? 

Thanks!

[Joseph_Seddik]

Actions cannot set values to virtual columns or any other column that has an app formula.

You have to recreate your virtual column with its formula in the destination table.

[ejprado]

Thanks for replying @Joseph_Seddik , how do I do that? I'm new using AppSheet. Thanks!

[Joseph_Seddik]

Just create a virtual column in the target table and put the desired expression in its app formula. Then copy the other columns from the first table. 

Please note that duplication of tables is not a good idea and is seldom needed. You might want to tell us what do you like to achieve and we could give you a better solution. 

[ejprado]

@Joseph_Seddik 

I am a fitness trainer. I have 3 tables: one with clients, one with workout plans and one with exercises associated to each plan.

A client has one workout plan per day, and each workout plan is composed of between 20-25 exercises with their specific directions for each day and each client.

I need to be able to duplicate a workout plan with all the related exercises and then assign it to another client who has the same condition to avoid having to create the plan from scratch.

[dbaum]

If I understand your original post correctly, you're duplicating a row within the same table. I've never needed to do that, but assuming it works in general, any virtual columns should simply automatically calculate when you create the new row. As Joseph noted, there's no need to include a virtual column in the "Add a new row" action. Go ahead and try your action and see whether it produces the result you need.

-----

Update: Oh, now I read more closely. Your virtual column is the list of related rows in another table and what you really need is to duplicate those rows in that other table. That's requires a separate action. If you need help thinking that through, Joseph and others here are likely good guides; I'm also happy to try to help.

[Joseph_Seddik]

Here is the data plan:

1. Clients table: contains columns about fixed information related to each client, like "Name", "Contact",  "Date of birth", "Hight", "Medical conditions", etc. You should also have a UniqueID Key column and you can set the "Name" column as Label. 
2. Exercises table: a Key column and other columns containing information related to each particular exercise. 
3. Workouts table: a Key column and a "Workout name" column marked as Label. Nothing more; just these two columns.
4. Workout Details table:
   • UniqueID Key column.
   • a Ref column pointing to Workouts table, with Is part of option set.
   • another Ref column pointing towards Exercises table. 
   • Information related to how to perform each exercise, like "Groups", "Repetition", etc.
5. Agenda table: Containing just a Key "Date" column.
6. Workout Plans table:
   • UniqueID Key column
   • a Ref column pointing to Clients table, with Is part of option set.
   • another Ref column pointing to Workouts table. 
   • another Ref column, type Date pointing to the Agenda table. 

This setup ensures minimization of your data, easiness of operation and you won't need to copy or duplicate record. For your purpose, you can start in a Workout Plans form, selecting a Client, a Date and an existing Workout. 

Please read this guide carefully and follow the links inside. Pay special attention to the Many-to-Many relationships part: Data: The Essentials | AppSheet Help Center.

[TeeSee1]

One approach that sort of works... (you may be able to optimize this further but as a first attempt)

Tables

• workouts: id, date, client_id, copied_from
• workout_details: a child to workouts with workout_id as a reference col back to the workout table, exercise_id, etc
• exercises: a master table listing exercises (i.e pushups, situps, jumping jacks....), id, name, etc.
• clients: a client master table. id, name, etc
• temp_wo: a table to temporarily store a workout id that you are copying. id (I use user_email), wo_id (to hold the workout id you are copying)

Copy process

1.  Create a workout row by copying a workout. Populate the copied_from of the new row with the id of the copied from workout row.
2. Go to the newly create workout row. Edit it (client, date, etc)
3. Perform a sequence of actions (still on the new workout row)
   1. Copy the id into the temp table
   2. Call a copy me action of the workout_details table
      • Reference rows: FILETER("workout_details", [workout_id] = [_THISROW].[copied_from]
   3. the copy me of the workout details
      • [workout_id] = LOOKUP(USEREMAIL(),"temp_wo","user","wo_id")
      • The rest of the workout_details columns

I have the composite action set as the form save event in my test app.

 

[Steve]

https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-add-a-set-of-rows/m-p/258033