Jump to content
  • 0

How duplicate a set of records


innov2e
 Share

Question

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?

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 1

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.

image.png.d09b4637f443bc1d57a76b5a0c8e6312.png

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:

image.png.9ad8a9fc704dfe203e6f189d26f742a3.png

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

image.thumb.png.3d3a675a8a09c5355681807674cad808.png

 

Hopefully this gives you an idea and helps :)

- LOEY

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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

Screenshot-2021-09-09-at-12.06.45.png

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...