Trigger Action to Insert Multiple Records into Another Table based on Field's Value

[Mazen]

Hello,

 

Just simple idea I want to implement, I have 2 tables, table A there is formula field WeekCount to count how many weeks between 2 fields  StartDate and EndDate, therefore I want to multiple rows to be created in Table B based on the value of WeekCount:

 

Example: 

Table A:-

based on update:

StartDate 01/01/2024 

EndDate: 31/01/2024

 

Formula Field

WeekCount will be = 4

 

Trigger Action to create in Table B:-

Row 1 (Field OrderOfWeek will be = 1)

Row 2 (Field OrderOfWeek will be = 2)

Row 3 (Field OrderOfWeek will be = 3)

Row 4 (Field OrderOfWeek will be = 4)

 

Any Help! 

 

Thank you

[Mazen]

Hello guys,

 

I'm trying some solutions and seems this solves part of the problem, the loop perfectly reads and perform on the other table to create the needed outcome of number of rows based on the number of weeks, also checks when the IDs exist in both tables having same number of weeks do not perform the action.

 

the only thing left for me and I was trying for hours, is that how I limit the trigger to be based on further conditions, for example also (if the Weeks Field is changed, for example the user changes the start date or end date, then the trigger shall perform either by updating the existing rows with the new changes, or create new rows representing the new changes)?

 

looking for your help!

 

Thanks

Mazen

[CoopperBackpack]

Hello @Mazen,

Could you specify how a user interacts with the data stored in Table B?

Let's say the Trigger inserts records into Table B and the user doesn`t update this data. In this case, the easiest option is to delete the records from Table B when the corresponding record in Table A is updated and to insert the records again based on the new number of records.

For that, you will need 2 Triggers: one on Insert (the one from your screenshot) and one on Update with the corresponding logic.

[Mazen]

1 hour ago, CoopperBackpack said:

Hello @Mazen,

Could you specify how a user interacts with the data stored in Table B?

Let's say the Trigger inserts records into Table B and the user doesn`t update this data. In this case, the easiest option is to delete the records from Table B when the corresponding record in Table A is updated and to insert the records again based on the new number of records.

For that, you will need 2 Triggers: one on Insert (the one from your screenshot) and one on Update with the corresponding logic.

Thank you @ CooperBackpack,

The users don't interact with table B at all, it's just for me to analyze and have more insights about my clients, and I will try to apply your idea although I never created a trigger to delete records then intiate new records.

 

Also I'm struggling with the trigger in SC, that anything the user updates in table A (not related information to table B), the trigger loops again creating a duplicate rows for same user in table B.

 

Thank you,

[CoopperBackpack]

Hello @Mazen,

The logic to delete the records is shown in the screenshot. I named the first table (Table A) 'Profile_Report'. The logic in the SELECT statement is to identify the ID of the record that was updated and the condition is to track only the 'Weeks' field update.

You can duplicate the Trigger on Insert that you already have, and add the logic from the screenshot as the 1st block. Below should be the same logic that you already have to insert the records. 

[Mazen]

On 6/17/2024 at 6:07 PM, CoopperBackpack said:

Hello @Mazen,

The logic to delete the records is shown in the screenshot. I named the first table (Table A) 'Profile_Report'. The logic in the SELECT statement is to identify the ID of the record that was updated and the condition is to track only the 'Weeks' field update.

You can duplicate the Trigger on Insert that you already have, and add the logic from the screenshot as the 1st block. Below should be the same logic that you already have to insert the records. 

Wow, thanks CoopperBackpack, that's really solve it and works now perfectly!.

 

Thanks Again.