How can I produce one record as the difference between two others?

[vidierre]

I try to make simple my question. I have table_a with these fields

• PERIOD   -  contains a text with a period that start from January for each quarter, like JAN-MAR, JAN-JUN, JAN-SEP, JAN-DEC
• VALUE_ 1  - a numeric value
• VALUE_ 2  - a numeric value
• ....
• VALUE_ n  - a numeric value

I need to build a real quarter table_b where I have a record for JAN-MAR, APR-JUN, JUL-SEP, OCT-DIC. This imply that

• for the 1st quarter the record of table_b will be with vaules of JAN-MAR that will be the same of one in table_a
• for the 2nd quarter the record of table_b will be with vaules of APR-JUN as result from record JAN-JUN less record JAN-MAR
• for the 3rd quarter the record of table_b will be with vaules of JUL-SEP as result from record JAN-SEP less record JAN-JUN
• for the 4th quarter the record of table_b will be with vaules of OCT-DEC as result from record JAN-SEP less record JAN-DEC

I do not know if trigger can be useful or if I need to code something else.....

[NiceDuck]

This will need an Application Task instead. https://howto.caspio.com/tasks/

Before I suggest a setup, do we have to aggregate the values from table_A per quarter before inserting them to table_B? Or just copy them there with a label indicating which quarter are they?

 

[NiceDuck]

If they are meant to be aggregated, kindly make a task like this: 
 

 

[NiceDuck]

Actually, I highly suggest that you create a formula field on table_A first that will specify which quarter it is.

Kindly make this formula:

Case 
When [@field:Label] = 'Jan' OR [@field:Label] = 'Feb' OR [@field:Label] = 'Mar'
then 'Q1'
When [@field:Label] = 'Apr' OR [@field:Label] = 'May' OR [@field:Label] = 'Jun'
then 'Q2'
When [@field:Label] = 'Jul' OR [@field:Label] = 'Aug' OR [@field:Label] = 'Sep'
then 'Q3'
When [@field:Label] = 'Oct' OR [@field:Label] = 'Nov' OR [@field:Label] = 'Dec'
then 'Q4'
End

It will then give you this in table A:


 

[NiceDuck]

Then we can use a more reliable and simpler Task:

[vidierre]

5 hours ago, NiceDuck said:

Then we can use a more reliable and simpler Task:

Thank for this quick suggestion. I'll study it a little and will reply soon providing some data sample.
Read carefully my question because we cannot aggregate or SUM data from table_a. This table does not contains quarter data, but data of a period from the beginning of year until the end of a quarter (remember JAN-MAR, JAN-JUN, JAN-SEP, JAN-DIC)

We need to subtract data of table_a from a record (i.e. the JAN-SEP) fro the previous (i.e. JAN-JUN) to obtain value of a quarter (i.e. JAN-SEP - JAN-JUN = JUL-SEP).

[NiceDuck]

 
Thank you for pointing that one out.

Will there be only 4 records in table A? one for each of these? JAN-MAR, JAN-JUN, JAN-SEP, JAN-DEC

If there will be more of them, we will still need to aggregate those values first, but we can just save them on a table variable instead of an actual table.


How about this?


 

[vidierre]

Your tip "but we can just save them on a table variable instead of an actual table" something I haven't thought of. I need to go deeper on your solution, but I feel this is the right path.