dennismoorejr

Calculation Based on variable field values

Discussion created by dennismoorejr on Feb 17, 2019

In my solution I have a field that is calculated on a few different variables.

 

I need to created a calculation that will return a value of the current balance owed.

 

For simple terms a balance is calculated on the amount of membership quarters the person owes.

 

A membership stamp cost $25 per quarter.

 

The stamp quarter are defined by 

 

Year XXXX Quarter 1 - Jan 1 thru Mar 31

Year XXXX Quarter 2 - Apr 1 thru June 30

Year XXXX Quarter 3 - Jul 1 thru Sept 30

Year XXXX Quarter 4 - Oct 1 thru Dec 31

 

As of today's date 2019 Feb 17 a person who joined  2018 July 15 needs to have paid the following to be considered a current status.

 

2018 Quarter 3     $25

2018 Quarter 4     $25

2019 Quarter 1     $25

 

Total                     $75

 

The participants requirement at the end of the year for 2019 would be $150 ( 4 x 2019 quarters plus 2 x 2018 quarters).

 

His dues balance today should reflect -$75.

 

Each participant record includes a start date.

 

In this participants case his start date was 15 July 2018.

 

Each participant has a different requirement based on their start date.

 

I currently have a global field that has a value of $200 (based on the start of the solution in 2018 ($100) and the current requirement of 2019 $100).  If no increase is made I would change the value to $300 for 2020.

 

I need to create a calculation that will define the dues balance based on the following fields.

 

Dues Balances = 

 

Total Number of quarters required determined by start date minutes payments made to date subtracted from the total membership dues.

 

Since the solution started in 2018, the total balance require for all participants would be $200 (2018 $100 plus $2019 $100).

 

 

The variables that affect the calculation are the prices of the stamps have the possibility of increasing in 2020 and so on.  I would like to prepare for this increase.   For example in 2020 if the cost changes to $30 per quarter, the requirement of the dues balance would be $320 (2018 $100 plus 2019 $100 plus 2020 $120).

 

Also, if a participant's status changes from say deceased or withdrawn the calculation should stop at that date, example if the participant listed above was to withdraw on 2019 Sept 30 they would only be required to have paid the following;

 

2018-3

2018-4

2019-1

2019-2

2019-3

 

$125. 

 

I am not sure how to achieve this with all the twist and turns of the calculations variables.

 

The solution does include a table where stamp purchases are entered and related to the participant id.

 

The stamp table calculated the total purchases and subtracts from the balance total.

Outcomes