Triggered Action Not Working

[SydneyH]

I have a triggered action setup with the goal of replacing the "Account Balance" in one table, based on entry into the other table. The triggered action is currently setup to add the newly deposited amount to the existing balance. The task validates but won't populate the data as intended unless I remove the "addition problem". If the task is set to update 'Account Balance = #inserted.DepositAmount' it will run successfully. Has anyone else experienced this issue? I have  nearly identical tasks within other tables of my application that function as intended with subtraction of similar variables. 

[CoopperBackpack]

Hello @SydneyH,

Is the 'AccountBalance' field blank initially?

If yes, this means that it stores NULLs. And when we add anything to NULL the result is NULL (blank).

This NULL should be replaced by zero:

Do you need to run this Trigger on Update as well? I can see the 'Update' action on your screenshot but in the text, you only describe the 'Insert' action (adding new entries).

Please note that the logic on Update is different. If you need assistance please let me know.

[SydneyH]

Thank you for explaining this so thoroughly.

I believe we can run the trigger on Insert only, vs. both insert and update. We are tracking each deposit as its own line item upon entry/insert. The starting balance for every new participant is always 0, which is likely where my problem was considering the 'case' logic was not implemented. This fixed our issues. Thank you so much!

 

-Sydney

[cheonsa]

Hi all,

I just wanted to share this Trigger setup that will update the Total Allowance field on the Parent Table every time a new record is inserted or updated on the Child Table. 

[NiceDuck]

We really need a proper 'ISNULL()' block. Creating all of those condition blocks just to replicate the same function feels too stuffy. 

[Corpcatalog]

I have a similar problem

I have a table the holds the agregate data of dried coconut from a number of growers who delivered bags of green Coconut to the processor.

For each batch we have multiple delivery receipts from the grower with the number of bags and the total weight in kg of green copra.

In the grower purchase table for every receipt entry I need to add the weight and the number of bags to the batch total green weight field in the batch table.

The index field is the batch id.

I have been working on triggered actions for a week and still can't get it 

So the trigger should be 

Insert a new record in the grower purchase table and ad the weight from the weight field to the total green weight field in the batch table for each record  where the batch id is the same

So I get the sum of the weights for all receipts with the same batch number.

I think I am missing something

 

[kpcollier]

@Corpcatalog, I think you are simply missing adding the current NetworkBatch.TotalGreenWeight with the Growerpurchase.Weightkg.

So instead of TotalGreenWeight = Growerpurchase.WeightKg + 1, it would be TotalGreenWeight = NetworkBatch.TotalGreenWeight + Growerpurchase.WeightKg.

To explain a little further, the setup you shared is replacing the TotalGreenWeight value with whatever was just inserted with Growerpurchase.WeightKg and adding 1 to it. If you want to have a running sum, you need to take the value that is already in TotalGreenWeight and add the new Growerpurchase.WeightKg to it. This should take what was originally in TotalGreenWeight and adding the new weight to that, instead of replacing it with the newly added weight + 1.

Also, you don't need to do a JOIN and use the WHERE clause to select the correct record with BatchID. Since you are already limiting the records to match with an index field in the JOIN, you don't need to use WHERE because only one record should be selected. I'd try getting rid of the WHERE clause as well as the update above. 

[Corpcatalog]

HI KP

I re-watched the youtube and change the trigger to this - but it still doesnt run.

Then I tried still not working 

 

[kpcollier]

#inserted is the way to go. Is it not updating at all, or are you getting the wrong number?

First, make sure your trigger is on the correct table. It needs to be on the same table as the datasource for your submission/report page where you are inserting the new record.

Try switching out the JOIN with the WHERE block - Since you are already using the insert trigger on Growerpurchase (I assume), those records should already be selected. And, since the UPDATE block is for the NetworkBatch table, those are already selected. I don't think you need the JOIN it again, just use the WHERE clause.

Make the WHERE clause = #inserted.BatchID equals NetworkBatch.BathID

[Corpcatalog]

Hi KP

Ok first thing I worked out I was actually using the wrong field 

The correct field for the table receiving the data is BatchNo However I tried what I think are all the possibilities and it still didnt add anything to the field

The Batch file looks like 

 The Growerpurchase file looks like 

The current triggered action I have in the above table is, which still doesnt work - I tired it with difference join and with and without where clause and joining the table or joining just the inserted records.

 

I Find triggered actions to be a very clumsy way to work. Ive been using caspio for 12 years and shy away from using the triggers when I build an app because I invariable can't get them to work.

[kpcollier]

Just a couple things to be clear - 

The trigger is on the Growerpurchase table, right?

And, you are testing this out with new records, correct? An insert trigger will not update past records, as in it will not go back and update the TotalGreenWeight field with WeightKg from prior records. That would need to be done using a Task.

[Corpcatalog]

Yes the trigger is in the Growerpurchase table and I am adding a new record in the growerpurchase table, the primary and foreign key for this record is the batchno

ORG-030223-STMW-19, for example for the last record in the table.

As I understood from watching the video, everytime I add a record to the growerpurchase table I can get the trigger to add the weight to the record with the corresponing Batchno in the Batch Table.

If not what is the point of triggers in one to many relationship tables?

 

 

 

[Corpcatalog]

@kpcollierthanks for all your help with this - I asked Caspio some help and they found a solution which included trigger on update

see attached