Feb 16, 2024 05:31 PM
I need to be able to total up sums per line from an original start date for a full year after.
Example: Original start date
Amount paid-1, amount paid-2, amount paid-3, etc.
This would be calculated for 1 year, then we would have a new date to calculate the 2nd year.
Each customer will have a separate start date so the formula would have to be connected to the start date column.
Feb 16, 2024 06:22 PM
I'm not sure I understand your setup... can you post a screenshot or a video?
Feb 16, 2024 06:24 PM
Agreed, an example screenshot or video would be helpful to fully understand the request
Feb 16, 2024 07:58 PM
I have included the screenshot. Each client will have a different start date. Then we make payments for certain things for them when needed so there will be multiple amounts and multiple dates. We just want to calculate amounts for a year from the start date. After a year it would start over.
Feb 16, 2024 11:32 PM
So for every payment you're going to be adding a 'Date' and 'Amount' field, e.g. 'Date-3' and 'Amount-3'? If so, you could make a formula field that'll do this but it'll be pretty tedious to set up and hard to manage as you add more payments and years
It feels like each of the payments should be its own record in a 'Payments' table actually. You'd then have another 'People' table, and finally a 'Year <> People' table that would contain the start years for each person. E.g. you might have a person called 'Jerry' in there twice, one with a start year of 1 Feb 2023, and the other with a start year of 1 Feb 2024
Unfortunately, I can't think of a way to automate this without using a script of some sort; there'd need to be an automation that would trigger every time a new payment record was created that would look for the appropriate record in 'Year <> People' to link to, which we can't do with the current 'Find Record' functionality