AnsweredAssumed Answered

Available Balance Calculation

Question asked by tm9 on Jul 29, 2014
Latest reply on Jul 31, 2014 by keywords

Hi all,


I am having difficulty with a calculation and I am not even sure if it is possible to do.


I have the following tables.





There is a "global_date field" in the ACOUNT table that grabs the current month. It is used to form a relationship between the ACCOUNT table and the MONTH table. The ACCOUNT table also has the field "Account Balance" which is used to store the actual current account balance for each account record.


Indvidual Budgets are related by a "month" field to the Month table. The Budget table has the field "Amount of Budget Remaining" for each budget record. A calculation global field, "global_Total Amounts Remaining," in the Month table totals the amount of Budgets Remaing for each month.


What I would like to do is create the field "Available Account Balance," in the Account table that calculates the available balance for each account record by summing the "Account Balance" field for all account records and subtracting out the "Total Amounts Remaining."


Is this even possible using a calculation?



I was thinking of adding a "Rank" field for each account record so that one account would be the priority and if the "Available Account Balance" for that account was less than 0 then the account with the next ranking of 2 would pick up with the remaining difference and so on until all of the "global_Total Amounts Remaining" for that particular month would be accounted for.


Some functions that came to mind as useful are the Case, Let, & Evaluate. However, I cannot think of a good way to do this.


Any assitance or suggestions for a better way to approach this is appreciated.


Thank you!