How duplicate a set of records

[vidierre]

I have a table Processes containing for each company a set of processes:

COMPANY TABLE
COMPANY     ADDRESS     EMAIL
COM_AA       V.Bari             info@companya.com
COM_BB       V.Roma          segr@companyb.com

PROCESS TABLE
COMPANY       PROCESS_NAME
COM_AA         STD_PROC_A
COM_AA         STD_PROC_B
COM_AA         STD_PROC_C
COM_AA         STD_PROC_D
COM_AA         COM_PROC_1
COM_AA         COM_PROC_2
COM_BB         STD_PROC_A
COM_BB         STD_PROC_B
COM_BB         STD_PROC_C
COM_BB         STD_PROC_D

Any company can add/delete/modify them but when a new company is added to the company table I need to insert into the process table the set of standard processes.  I would like to have a 3rd table named STANDARD PROCESSES and copy all its content with the new company name into the PROCESS TABLE.

It is correct to handle this with a trigger into the company table that operate such data copy?

[ParkLoey]

Hi @innov2e ! I got curious about this workflow and tried it on my end as well. Here's what I came up with. Basically, I created a trigger on insert to my company table so it only triggers when I add new companies to the table.

Then I got here my StandardProcess_Tbl which is where I'll get the processes I want to insert when there's a new company:

And here's the result when I added a new company "BB"

 

Hopefully this gives you an idea and helps 

- LOEY

[TellMeWhy]

So, only NEW company will trigger the Triggered Action to insert on the Process Table, right? If so, yes, that can be done with Triggered Action, maybe you just have to set a condition where if it doesn't exist yet, or even count the records with the same company name, if it's more than 0, it will not insert.

[autonumber]

Hi! I agree with TellMeWhy this can be achieved using Triggered Actions. I just wanted to add these links that you can refer to in creating Triggered Actions. 

https://howto.caspio.com/tables-and-views/triggered-actions/

 

[vidierre]

Thank for your answer. It will be a huge trigger. I will try to build a sort of template tables and copy all their content into the process table.

[vidierre]

Basically @ParkLoey suggestion works.

There are some issues when there are referencial integrity rules in place using the Company field.
This because when the trigger start on the insert data event of Company_tbl the record in this table is not yet there, so when caspio try to insert rows into Process_tbl return an error.

And this seems the way as Caspio is designed. If future enhancements will introduce a Run action on "after insert" it should works different.

BUT I found a not-so-elegant workaround to reach the goal.

1) I added a i2e_task table

The Guid, Date, App, Task are mainly descriptions of WHO require WHAT (in case to use it in others apps) in my app there is
App: GDPR
Task: InitCompany

2) The trigger of the Company_tbl is changed to insert only a record into the i2e_task table.

here cames the trick

3) I added a TASK with the logic needed to copy records from StandardProcess_tbl to Process_tbl 

4) I scheduled this task once in an hour (It could be copied four times and be scheduled at xx:00 xx:15 zz:30 xx:45 (future enhancements on the schedule will make me happy)

It is not very elegant, but it works and allow me to use the referential integrity also.

I wrote this here just to share with all and because I am curious if someone has better way to do this.

[Kronos]

Hi everyone,

Just want to share this here for future reference as well. Caspio has a marketplace extension for cloning records. Maybe their Professional Services team can customize the extension to clone a set of records. 

Clone Record

Hope this helps!