Question about calculation

[Aleksandra]

Hello,

Does anyone know what would be the best way to calculate the remaining budget? For example, I have a fixed budget of $10,000 for project A and $15,000 for project B, and so on. When I add expenses related to a particular project, I want to see how much money is left but I am stuck with the calculation formula. The budget is stored in the Projects table and expenses are stored in the Expenses table.

Thank you all in advance

[CoopperBackpack]

Hello @Aleksandra,

To check the approach I created the following tables:

1) Clients (stores unique clients)

2) Projects_Budget (stores unique projects and their budget)

 

3) AM_tbl_Project_Expenses

 

 

Relationships:

 

This is the Tabular Report that is built on the AM_tbl_Project_Expenses table:

The "Project Budget" is Calculated Field 1. According to my table design I used the following formula to retrieve the budget value for each project:

SELECT Project_Budget 
FROM Projects_Budget 
WHERE Project_ID = target.[@field:Project]

The "Budget Remaining" is Calculated Field 2

SELECT isNull([@calcfield:1],0) - SUM(isNull([@field:Standard_Amount], 0) + isNull([@field:Non_standard_Amount], 0))  
FROM AM_tbl_Project_Expenses 
WHERE ID <= target.[@field:ID] AND Project = target.[@field:Project]

The main point to make the formula work is to refer to the ID field in the 'AM_tbl_Project_Expenses' table. This must be a field with the Autonumber data type.

If you don`t have this field, please add it.

The formula logic is:

•  to select budget isNull([@calcfield:1],0)
•  to sum the expenses SUM(isNull([@field:Standard_Amount], 0) + isNull([@field:Non_standard_Amount], 0))  
• to subtract expenses from budget 
• thanks to the condition in the WHERE clause this calculation takes into the account all the expenses from the previous records (ID <= target.[@field:ID]) related to this project (Project = target.[@field:Project])

Please update this thread if you have further questions.

[kpcollier]

This should be quite easy using a calculated field in your page. What kind of datapage are you using to display the data?

You can either use a view of both tables or just call the other value over using a select statement if you only have one table as the source. 

 

If you want the calculation to be saved to the table, I'm not too sure of another way other than Triggers. However, I have heard somewhere that triggers aren't the best to use for mathematical purposes. I'd give it a try and see if it works for you, though.

[Aleksandra]

Thank you for your response @kpcollier. I tried that approach before, but the issue is that it doesn't update the Project budget field I've created in the Expenses table (it pulls records from the Projects table so I don't have to use that table for this formula, it can be done with Expenses table). So, for example, I have project A with a budget of $10,000. I add project expenses of $1,000 so my remaining budget now is $9,000. If I want to add another project expense of $2,000, it shows me that my remaining budget now is $8,000 and it should be $7,000. 

So, I was thinking maybe it would be better to create a calculation field that will accumulate all expenses related to the specific project and then have a formula [Project_Budget]-[Total_Expenses] for the field Budget Remaining.

[sandy159]

Hi @Aleksandra,

This How-To article on the Running Balance may be helpful:

https://howto.caspio.com/tech-tips-and-articles/advanced-customizations/calculate-a-running-total-in-your-report/

[CoopperBackpack]

Hi @Aleksandra,

I agree with previous comments from kpcollier and sandy159.

The approach depends on the expected result.

1) If you need to store the updated budget  in the Projects table and store the initial budget as well, you may add a new field to the Projects table. 

This field can be updated by the Triggered Action. If the only action is to subtract the expenses, the Trigger is rather simple.

2) If you do not need to store the updated budget, you may use the Calculated field to calculate the result and just display it. 
In this case the article shared by sandy159 can be helpful. However, the solution described there involves 1 table, so for your case it should modified.

If you need further assistance, any screenshot of the current result will be helpful.

[Aleksandra]

Hey again @sandy159, @kpcollierand @CoopperBackpack.  Thank you for your help so far. So, I've created a report for this one, and here is the formula I've applied:

SELECT (IsNull ([@calcfield:1],0) -IsNull([@field:Standard_Amount], 0) -IsNull([@field:Non_standard_Amount], 0)) FROM AM_tbl_Project_Expenses WHERE [@field:Project_ID]=target.[@field:Project_ID]

 

The calculation field (the first field in my formula) is the Project Budget. I would like to see Budget Remaining changing the value every time I add a new cost. I am not getting the correct values here  For example, in this first project in the screenshot, Budget Remaining should be 18,600 and after that 15,800 and then 14,200. It should decrease every time I add a new expense. Hope you understand what I want to achieve and I hope there is a solution for this issue. Thank you in advance,

Aleksandra

 

[Aleksandra]

Thanks for such a detailed response @CoopperBackpack. I applied that approach to another table I had the same issue with, and it worked there. The reason why the issue appeared is that I had an ID field as Random ID and not Autonumber, so I've converted it to autonumber and applied the formula you suggested.

This approach didn't work for the Budget Remaining Value I am trying to get, but I will still try to figure out where the issue has appeared with that one. I'll get back to this thread if I can't figure that out, but your post was super helpful.