Can't copy table (and parent table) to another table changing IDs.

[Julio35]

Hello everyone, 

I have two connected tables as follows:

Table one: ID_table1, Date_t1

Table two: ID_table2, ID_table1, Date_t2, x, y, z

In table 1 i also have a column with ref_rows from table2.

I would like to make an action or automation to copy this tables to other tables (same columns) but i need to change IDs because they must stay unique. While doing this, while setting up new IDs, i still need to keep the references through the tables. I also need to give the user the possibility to change the date. 

To sum up, i need a way to copy two related tables to two more tables while changing dates and IDs and keeping references. 

I tried to use actions and automation but it seems quite difficult, maybe would be better to use Appscript?

NOTE: Records to table 1 are added via automation when adding records to table 2. If i use Actions/Automation/Appscript to copy rows between table, will the first automation still start? In this way i would have more data than i need. 

Thank you very much

[Marc_Dillon]

I think knowing the bigger picture of why you're wanting to do this, and what you're trying to build, would help to give a good suggestion in this case. Additionally, it's not clear exactly which records you're wanting to copy, can you show examples of the existing data, and what your desired result would be?

[Julio35]

Hello, 

Thank you very much for helping out. 

Sure, i'll try to explain better.

I am trying to build an app to help a restaurant manager making up shifts for his employees. For each day (imagine a calendar view), pressing on the "Plus" button he can add a new shift, for example:

Day: 09/10/2023

Employee: Name 

Where: Kitchen

Start: ...

End: ...

Parent ID: (set automatically)

Of course the row has an hidden ID (key as well). This is only for one worker and one shift (lunch for example), if the manager wants to add the shift for 5 workers working both lunch and dinner for the whole month, he manually has to add 5(workers)*2(shifts)*30(days) = 300 rows (average)...

I would like to implement a way to have a "Standard week" that can be copied over only a week or a whole month so that the manager can copy the week (and eventually modify as needed) instead of adding everything manually. This way, to make a one-month shift, he just has to copy the same "Standard week" 4 times...

By standard week i mean a pre-built table with all the shifts. When copying the "Standard week", i need to be sure that IDs are unique in the SHIFT table, i can't just copy the "Standard week" IDs...

NOTE: All the shifts belonging to one day, have a parent record as well (Parent ID) so that i can generate detailed reports. Parent records are automatically generated when entering shifts in the following way:

• ADD shift: date, employee, parent id(empty)..
• If there is no parent record with same date (SHIFT date) then add a parent record (DATE, PARENT RECORD ID)
• Set parent id = parent record id in shift table

SHIFT TABLE

SHIFT-ID	Parent ID	Date	Employee	Start	End	Where
SH-1	PA-1	10/10/2023	Em-1	08:00AM	11:00AM	Kitchen
SH-2	PA-1	10/10/2023	Em-2	7:00PM	9:00PM	Tables
SH-3	PA-2	10/11/2023	Em-1	9:00AM	11:AM	Kitchen

PARENT TABLE

Parent ID	Date
PA-1	10/10/2023
PA-2	10/11/2023

To sum up, i would like a way to fill-in Shift table weekly or monthly while also updating the parent table. 

I hope this helps...if you have a whole new way to build it i am happy to hear...

Thank you very much

[Julio35]

Hi  @Marc_Dillon,

Sorry i got some problems with my post but it is now fixed. Do you have any idea how to handle this problem?

I was thinking i could use action-iterations but i am curious if there is a simpler way to handle it. 

Thank you

[Marc_Dillon · Accepted solution]

Let's assume the following tables:

Schedule Template:

id	Day	Employee	...
1	x	a	...
2	y	b	...

Week:

id	year	week
202301	2023	01
202302	2023	02

Weekly_Shift

id	Week (Ref)	Day	Employee	Date	...
ghj489	202301	x	a	formula from week & day	...
pyt873	202301	y	b		...

The creation of a new Week record can drive the copying of all schedule template records into the weekly_shift table, referenced to the just-created week record. Use a reference action to do so.

I don't understand why your main question seems to be about the id values. Just use UNIQUEID() for the id column of weekly_shift. Not really sure what your other questions may be here.

[Julio35]

Thank you very much for your help!!!

It sounds like a very good solution. I'll try to implement it and i'll be back on this topic. I don't know much about "Reference action" but i'll look into that. 

Just so that i am sure i understood correctly:

- i create a new entry from the week table

- All records from the Schedule template are (automatically) copied in the weekly_shift template (where the week is the one just created).

It sounds all clear, i am just missing how to copy all the entries of Schedule Template in the Weekly_Shift table...do i need some sort of loop or that's what reference action are used for?

Thank you

[Marc_Dillon]

---

@Julio35 wrote:

that's what reference action are used for

---

[Julio35]

Thanks!!! I'll be back with updates. 

[Julio35]

Hello @Marc_Dillon ,

I think i am at a good point with the implementation but i got stuck...

I learned about reference actions (thanks about that, very useful), and i can now execute an action on all childs when editing a parent.

However, in this case, the parent table is Week and the child is Weekly_shift...how can copy of all Schedule Template records into the Weekly_shift table? Schedule template does not have any connection with the other tables. 

Thank you

[Julio35]

Hi, 

I almost made it! Reference actions are not just for parent-child, it could be any table (filtered table). That's what i did:

- Schedule template  - Action "CopyRowToWeeklyShift":

I just made an action on Schedule template where i set values of another table (Weekly_shift) where:

Week = maxrow("Week", "_RowNumber") (the one just created)

Date is a formula from [_thisrow].[Day] and [Week].[week]

- Schedule template  - Action "Copy Schedule template":

This is a ref action where:

Table = Schedule template

Execute action on a set of rows

Reference table: Schedule template (same table)

Referenced rows: filter("Schedule template", True) (all of them)

Referenced action: CopyRow

If i click on "Copy Schedule template", it is actually very slow...i think there might be some errors somewhere. I'll keep working on it, please let me know if you have any suggestions. 

Thank you very much 

[Julio35]

@Marc_Dillon  The solution you suggested worked very well!!! I just changed Referenced table to week. Running it from an automation it is actually fast.

Thank you very much for your help