Rate Master

[manojk]

I have a rate master table with following fields

 

Product ID.         Rate.         Effective Date 

P0001.                   5000.        01/01/2022

P0001.                    6000.       01/04/2022

P0001.                    7000.      01/06/2022

 

Based on the above if the date of sale is 1st Feb then it should fetch 5k and if the sale date is 1st May it should fetch 6K and if sale date 15th June it should fetch 7k. How can I about it

[Queso]

Hi,

Maybe you can achieve the said workflow by changing the data type of your Rate field into a Formula Field and insert a formula that has a combination of CASE statement and Date function. The CASE statement's WHEN has the DATE function while its THEN has the fetch rate. Try the following formula:

CASE

WHEN Day([@field:Effective_Date]) = 1 AND Month([@field:Effective_Date]) = 2

THEN 5000

WHEN Day([@field:Effective_Date]) = 1 AND Month([@field:Effective_Date]) = 4

THEN 6000

WHEN Day([@field:Effective_Date]) = 1 AND Month([@field:Effective_Date]) = 6

THEN 7000

Else 0

END

[cheonsa]

Hello,

Just wanted to share with you another solution aside from Formula field, you can use Triggered Actions. Triggered Actions allow data manipulation, calculations and sending notifications by constructing application logic with a visual interface. They will be executed on specified events to perform actions on data in your tables. Changes in one table can affect data in other tables.

https://howto.caspio.com/tables-and-views/triggered-actions/