Sql Insert Into Statement

[WillJennings]

I was wondering if it is possible to insert the result of a calculated field back into the data table I'm using as a data source with the SQL INSERT INTO function?  I'm using SQL to create a SUM within the calculated field already.  This is what I have and it works:

 

SELECT


SUM (CASE
    
       WHEN  [@field:ReturnMaster_File_Type] = 'Federal' AND


             [@field:ReturnMaster_ACCEPTED] <= '2/15/2015' AND


             [@field:ReturnMaster_Sent] = '' THEN     


             [@field:InfusionsoftCustomers_a2014_Fed_EF_Fee]


       WHEN  [@field:ReturnMaster_File_Type] = 'State'  AND


             [@field:ReturnMaster_ACCEPTED] <= '2/15/2015' AND


             [@field:ReturnMaster_Sent] = ''THEN 


             [@field:InfusionsoftCustomers_a2014_State_EF_Fee]


       ELSE 0


END) FROM _v_BillingView where ReturnMaster_EFIN = target.[@field:ReturnMaster_EFIN]




I'm not super familiar with SQL but I was thinking it might be possible.  I was trying something like this:




INSERT INTO BillingView (InfusionsoftCustomers_AmountBilled2)


VALUES


(SELECT


SUM (CASE
    
       WHEN  [@field:ReturnMaster_File_Type] = 'Federal' AND


             [@field:ReturnMaster_ACCEPTED] <= '2/15/2015' AND


             [@field:ReturnMaster_Sent] = '' THEN     


             [@field:InfusionsoftCustomers_a2014_Fed_EF_Fee]


       WHEN  [@field:ReturnMaster_File_Type] = 'State'  AND


             [@field:ReturnMaster_ACCEPTED] <= '2/15/2015' AND


             [@field:ReturnMaster_Sent] = ''THEN 


             [@field:InfusionsoftCustomers_a2014_State_EF_Fee]


       ELSE 0


END) FROM _v_BillingView where ReturnMaster_EFIN = target.[@field:ReturnMaster_EFIN])

 

Of course this comes back as Invalid.  Since I'm not familiar with SQL I wasn't sure if my syntax was wrong or if it just ins't possible within Caspio.  Any insight would be greatly appreciated.

[Jan]

Hello Will,

 

As far as I know, the INSERT is denied in Calculated fields for security reasons.

There is the post how to save a value of a Calculated field.

 

I hope, it helps.

[cheonsa]

Hi all, 

With the current version of Caspio, there is now Triggered Action. You can use Triggered Action to perform calculation and to insert into a Table.

Here is the link for reference: https://howto.caspio.com/tables-and-views/triggered-actions/

 

[NailDyanC]

Hi, just to add in the previous comments above, you can also have a field and set the form element of that field to Calculated Value so that  you can store/save its result to the table. In a addition as well with to the triggered action workaround, you may consider checking this video: