You are viewing limited content. For full access, please sign in.

Question

Question

subtract multiple fields from one field

asked on February 24, 2021

I have 6 single line fields. I need to get a calculations that is: "Grand Total " field  - rest 4 fields to have the answer to display in the last field "Employee Due". The first 5 fields also have calculation formulas because they are generated from the tables above; Is there a way I can make this to work? 

 

Thank you!

Screenshot 2021-02-24 140951.png
0 0

Answers

APPROVED ANSWER
replied on February 26, 2021

Oh, I didn't realize that you were using Javascript.

It may be possible that your formula isn't recognizing that all of your subtotals have been updated at the end of your sumtotal() function.

You could try updating your script to tell it that the subtotalcol1 (2, 3, 4) fields were changes after you update their values.  Something like this (after line 29): 

$('.subtotalcol1 input').trigger('change');
$('.subtotalcol2 input').trigger('change');
$('.subtotalcol3 input').trigger('change');
$('.subtotalcol4 input').trigger('change');

 

Alternately, you should be able to do those subtotals from the table using formulas instead of Javascript - I'm happy to help with that if you'd like.

0 0
SELECTED ANSWER
replied on February 25, 2021

It sounds like the formula should be 

=Grand_Total - SUM(Credit_Card,Cash,Personal,City_Check)

So you take the grand total and subtract the sum of the rest of the four fields. 

1 0
replied on February 25, 2021

Oh!  I didn't catch that the - symbol was meaning minus, I misread it as a break in the sentence.  hahaha

Your comment makes a lot more sense than mine when taking that into account.

0 0
replied on February 26, 2021

Thank you so much for your responses. I tried this formula and it did work but not quite right. The calculation is not done correctly. It should equal out to 0 but its not. Also, I had to fill in all the fields to get the answer. Is there a way to leave one of them blank and it would just count it as zero?  Thank you!

0 0
replied on February 26, 2021 Show version history

Is it possible there is a typo in your formula?

I just made a test form similar to yours.  I used the formula names in the labels too, so you can match them up to the formula easier.  I'm getting a total of zero.

Here's the formula I'm using:

=grand_total-SUM(all_credit_card_charges,cash_advances,personal_expenses,city_check_issued_reg_fee)

 

Regarding the second part about leaving some fields blank...

I don't see the same behavior, maybe a difference in behavior between versions or something - but in situations like you describe, where a calculation is not liking a blank value, and you would like to use a zero instead, then I like to use IF statements to do that.  IF(all_credit_card_charges="",0,all_credit_card_charges)   If all_credit_card_charges is blank, return a zero, otherwise return the value of all_credit_card_charges.

Then it'll allow blank values to be excluded from the calculation.

Here's the same formula, modified to utilize IF statements for each variable.  The line breaks and extra spaces are there for readability and can be included or excluded as you like.

=
IF(grand_total="",0,grand_total)
-
SUM(
  IF(all_credit_card_charges="",0,all_credit_card_charges),
  IF(cash_advances="",0,cash_advances),
  IF(personal_expenses="",0,personal_expenses),
  IF(city_check_issued_reg_fee="",0,city_check_issued_reg_fee)
)

 

0 0
replied on February 26, 2021

IF statement worked perfectly. I still have an issue with calculation. All the values in the fields come from the tables above so the submitter enters the values in the tables and not just in the field. I think the problem is with city_check_issued_reg_fee field. Submitter enters the amount in the field at the top of the form and then I just use that field in the grand total and in the separate city_check_issued_reg_fee field. For some reason it uses that value in the grand total but it doesn't subtract it in the city_due formula that I use. Sorry for such confusion. 

0 0
replied on February 26, 2021

If the formula inside the city_check_issued_reg_fee field is working as you expect, I don't think it should be failing further into the process when calculating into the city_due field.

Can you share the variable names and formulas for all 6 of the fields?

0 0
replied on February 26, 2021

I used Java Script to calculate the value for the fields so I do not have formulas for them I have css class for each. 

Screenshot 2021-02-26 114819.png
0 0
APPROVED ANSWER
replied on February 26, 2021

Oh, I didn't realize that you were using Javascript.

It may be possible that your formula isn't recognizing that all of your subtotals have been updated at the end of your sumtotal() function.

You could try updating your script to tell it that the subtotalcol1 (2, 3, 4) fields were changes after you update their values.  Something like this (after line 29): 

$('.subtotalcol1 input').trigger('change');
$('.subtotalcol2 input').trigger('change');
$('.subtotalcol3 input').trigger('change');
$('.subtotalcol4 input').trigger('change');

 

Alternately, you should be able to do those subtotals from the table using formulas instead of Javascript - I'm happy to help with that if you'd like.

0 0
replied on February 26, 2021

That has worked perfectly. Thank you so much! 

1 0
replied on February 26, 2021

Glad to hear that you got it working.

Don't forget to mark your question as answered if everything has been addressed.

Have a wonderful day!

0 0

Replies

replied on February 24, 2021 Show version history

You should be able to do a formula on your grand total to sum the various subtotals.

Based on the field labels, I assume your formula with the field variables would be something like this:

=all_credit_card_charges+cash_advances+personal_expenses+city_check_issued_reg_fee

Think of how a spreadsheet works - you can do a calculation in B1 based on A1, and then a calculation in C1 based on B1, and then a calculation in D1 based on C1.  This is the same idea.  You calculations in the total should be able to work off of the subtotal fields even though they have their own calculations.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.