Skip to main content

Hi,



I work for a charity that distributes surplus food to members of the local community for a small weekly fee. I use Airtable to keep track of, among other things, how much each person pays each week, versus how much they should’ve paid.


I use two linked tables to track this - one on which I record each individual transaction, and another that uses rollup fields to add each member’s transactions together so I can see each member’s history at a glance. I’ve set it up so that the Rollup table shows me how much (if anything) a member owes us in total, but the actual maths for that is done in the Transactions table.


At the moment, one food delivery costs £3, but we’re about to increase that to £4. My trouble is with how to get Airtable to reflect this change. If I just change the formula for the amount owed from {Packages delivered}*3 to {Packages delivered}*4 then it’ll re-calculate every member’s payment history and tell me that everyone is £1 per week in debt, so I’m hoping there’s an IF formula I can use to say “before 31.08.2020, it’s £3 per package; after 31.08.2020 it’s £4 per package”. I’ve tried everything I can think of, but I don’t have a good enough knowledge of formulas to make it work.



Thanks in advance for your help 🙂

Hi Jeni,



My personal inclination would be to put a ‘delivery cost’ field in the transaction table. Then you can change it accordingly based on future needs, and have a simple rollup for totaling the delivery costs of every package. Doing so might save you running into this same challenge in the future. If you have access to the ‘batch update block’ then it’s an easy way to update lots of records at once for this purpose.



That being said, here is the formula you’re looking for right now, if you just want to duct-tape it together 😛 



IF(IS_AFTER(


   {Package Date}, 


   DATETIME_PARSE("31 Aug 2020")


),4,3)


That formula will check if your ‘Package Date’ field is after Aug 31, 2020, and returns a 4 if true, and a 3 if false. You can plug that in to your current formula by simply multiplying the result by package count as you currently do.



Thanks for helping your community!



Cheers,


Kris

Hi Jeni,



My personal inclination would be to put a ‘delivery cost’ field in the transaction table. Then you can change it accordingly based on future needs, and have a simple rollup for totaling the delivery costs of every package. Doing so might save you running into this same challenge in the future. If you have access to the ‘batch update block’ then it’s an easy way to update lots of records at once for this purpose.



That being said, here is the formula you’re looking for right now, if you just want to duct-tape it together 😛 



IF(IS_AFTER(


   {Package Date}, 


   DATETIME_PARSE("31 Aug 2020")


),4,3)


That formula will check if your ‘Package Date’ field is after Aug 31, 2020, and returns a 4 if true, and a 3 if false. You can plug that in to your current formula by simply multiplying the result by package count as you currently do.



Thanks for helping your community!



Cheers,


Kris


Hi Kris,



That has worked perfectly, thank you so much!



And thanks for your suggestions on how to improve the system - I’ll have a good look at it when I’ve got some time.



🙂

Reply


Terms & Conditions