Trigger and Task Misc

[NiceDuck]

Halo!

There's is actually no question here. I just decided to make a thread where to compile all the trigger and task misc. inquiries that I post or found so I can easily track them out.

[NiceDuck]

Issue on transferring records using not exist.

I noticed this issue a while ago.

 

On this case, Cascade_B table is empty before the task run. After the task, it pasted every single 'aa' on the Cascade_B even though we are using a not exist condition. This is because the conditional statement is ran through all of the records on the Cascade_A table at the same time and not one after another. 

In short, the result on the Cascade_B is actually an expected behavior.

If you want your task to ran the conditions on those records from AA one at a time (to prevent duplicates), you will have to use a setup like this instead:

 

[NiceDuck]

 

Task for placing a count in records per group.

[NiceDuck]

Inlcluding this here:
 

 

[NiceDuck]

Sorting out values on table variable.

For some reason, when you use this setup, the values on the table variable are not being sorted.

 

Not sure if its a bug though.


 

[NiceDuck]

I find this as a workaround for this issue: 

 

[NiceDuck]

just placing this Here:
 

 

[NiceDuck]

I just noticed this strange behavior:

When assigning a date value on the variable and you date add function in it and then assign it on a text255 field, the format of the date is changed into partial text.

example:



You will get a result like this:



Notice that value on the 'test_val' field.

Anyways, the value is still being accepted with no issue if you assign it on a normal date field. See the 'date1' field.

[NiceDuck]

Adding this here:

 

This join setup will act like not exist/No in function. 

[NiceDuck]

For the inverse (In/Exist), just... change the Join type to 'Inner' and remove the contents of the 'Where' clause.

[NiceDuck]

Another note:

A variable in triggered action and task that is set to 'number' as its data type can only have up to 12 digits or characters on it.  Anything further will cause the trigger to fail.

Unlike actual fields, variables cannot turn their values into scientific notation.

[NiceDuck]

Use the field value as a 'Top' Clause value on the trigger.

I noticed that field values cannot be used directly on the 'Top' clause of action blocks. I tried to put the values on the variables first with the same result.

With this setup, you will be able to dynamically control the value of affected/collected records just like using the 'top' clause. Basically, this is a workaround. 

The intended workflow is that, updated top # of records on the table cascade_b depending on the number on the field 'post' from the #inserted.

 

 

[NiceDuck]

Basically, I used a loops to collect the top# of IDs from the cascade_B table and put it on a table variable.

I then connected the table variable on the update block using inner join so only the records of the collected IDs will be updated.

The 'For Each' loop is just to make this work with bulk inserts/updates. If there is no expected bulk operation, that part can be skipped. 

As for the left join, it's just a new 'Not Exist/Not In' setup that I learned, as that said, it is just to make sure that no same IDs are collected. You can still use a normal 'Not In' or 'Not Exist' function instead of that.

[NiceDuck]

Sending an email only if a value in a certain field is changed.

 

This workflow is effective in making sure that your email block will only send an email if a specific field has changed its value. 

Usually, the setup is that an update trigger is created and an email block that references the value of the #inserted is used to send this update. However, that plain setup makes the trigger send an email even though you have changed a different field on the record, causing redundant emails and wasted resources. 

On this setup, it will only send an email if the value on the field 'test_pin' is changed.

The key point on this workflow is to have the #inserted joined to its parent table using a unique_field, preferably an ID field then compare the values of the same field from both origins (#inserted and demo_table01). With this, you are comparing the field's new value (#inserted) and its old value (demo_table01). If their values differ, this means that the value on that field is updated. 

[telly]

Hi ,

For more information about triggered action, you may refer to this article:
https://howto.caspio.com/tables-and-views/triggered-actions/

[Queso]

Hello guys, feel free to check this Caspio Live for popular examples of Triggered Actions as well: 

 

[NiceDuck]

Aggregate functions involving #inserted (part 1 Sum)

Workflow:
Upon inserting a record into the table, get the sum of all records on the table with a matching parent as the newly inserted and update all of them with the new Sum.
 
Results:



Please note that as much as possible, there shouldn't be any update trigger on the table to avoid trigger nesting. 

 

[NiceDuck]

Aggregate functions involving #inserted (part 2 average.)

Upon inserting a record into the table, get the average of all records on the table with a matching parent as the newly inserted and update all of them with the new average.

Unlike in part 1 however, we can't just add them up together as the average is a different aggregation. For this, we will need to use a table variable.

Results:

[NiceDuck]

Aggregate functions involving #inserted (part 3 count distinct.)

Upon inserting a record into the table, count all the unique records on the table with a matching parent as the newly inserted (including the newly inserted) and update all of them with the new count.

For this setup, we can simply use the same setup as part 2.



 

[NiceDuck]

Aggregate functions involving #Deleted (part 1 Sum)

Workflow:
Upon deleting a record from the table, get the sum of all records on the table with a matching parent as the newly deleted and update them with the new Sum.

Obviously, this will take a different approach than the Aggregate functions involving #inserted (part 1 Sum).

Just like the rule on Insert operation where the records on the #Inserted are not yet on the table until the trigger run is complete, the records on the #deleted are not removed on the table until the trigger run is complete. for this case what we will need to do is to get the sum from the table excluding the records that have been deleted.



You may notice that instead of just using an 'Not Equal', I am using a 'Not Exist' operator. This is so the trigger will be adaptive to bulk delete instead of just one by one record deletion.

[NiceDuck]

Nested Trigger Calculations.

Let's say that you have a trigger that updates the value on your #inserted. With the following setup.

Field A = static number + some values from another table.

FieldB = Field A / staticnumber.

How exactly do you need to set this one up?

At first, I thought that this can simply be set like this:

field A = Value_num

field B = Value_num_2

 
However, it seems that this is an incorrect setup.

You see, Field A will only have its value after the update block is finished. That means that if you use Field A for calculations in the same update block, Field A is still blank, hence you will also get blank results for Field B. (You will get blank results because if any of the values involved in the calculations blank, the whole equation will now return blank).

To resolve this, you will need multiple update blocks.



Since the calculation for the field b is on the second update block, this means that the calculation for field A is already completed hence it now has proper values.