Help

Adding sums between 2 dates

1609 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Brenda_Welsh
4 - Data Explorer
4 - Data Explorer

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.  

 

           

4 Replies 4

I'm not sure I understand your setup... can you post a screenshot or a video?

ladracy
4 - Data Explorer
4 - Data Explorer

Agreed, an example screenshot or video would be helpful to fully understand the request

Brenda_Welsh
4 - Data Explorer
4 - Data Explorer

 

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.

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