What is wrong with my code? [Apply Discount] is a checkbox the rest are numeric formula fields. [Discount] is a numeric field. It will Sum but fails to subtract the [Discount] even when not used in the IF statement.
If([Apply Discount]= true, Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total][Discount]),Sum([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total]))
If([Apply Discount]= true, Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total][Discount]),Sum([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total]))
 150 Points
 frustrated
Posted 8 months ago
Evan Martinez, Community Manager
 11,556 Points
Hi Billy,
Have you tried wrapping just the section with the subtraction in its own set of parenthesis?
If([Apply Discount]= true, Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],([Removal Total][Discount])),Sum([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total]))
Just to make sure that Subtraction is being calculated first.
Have you tried wrapping just the section with the subtraction in its own set of parenthesis?
If([Apply Discount]= true, Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],([Removal Total][Discount])),Sum([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total]))
Just to make sure that Subtraction is being calculated first.
 150 Points
It still doesn't do the subtraction. I get the sum total whether Apply discount is checked or not.
QuickBaseCoach App Dev./Training, Champion
 66,238 Points
 150 Points
Good idea though, Here is the code that worked. What you said made me think.
If([Apply Discount]= true, Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total])([Discount]),Sum([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total]))
If([Apply Discount]= true, Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total])([Discount]),Sum([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total]))
Evan Martinez, Community Manager
 11,546 Points
Nice I'm glad to hear that worked
Evan Martinez, Community Manager
 11,556 Points
Could you try putting in 1 instead of true for your checkbox really quick and see what you get back?
So [Apply Discount]=1
So [Apply Discount]=1
 150 Points
The code I posted above worked. Just the subtraction part needed to be in its own set of parentheses.
Alex Molochko  now with 2019 Certification!
 3,226 Points
The parens around the subtraction value aren't relevant, it's that you needed to do the subtraction outside the arguments for Sum().
In other words your final code
Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total])([Discount])
works, and this would also work
Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total])[Discount]
I don't know offhand if
Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total],[Discount])
would work, but I suspect not
Addendum  if for some reason you HAD to do it that way (no idea why you might, just go with it), you'd either put negative values in [Discount] or else create a field (called [Discount to Apply] or something) with a formula [Discount]*(1) and use that in your sum formula:
Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total],[Discount to Apply])
In other words your final code
Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total])([Discount])
works, and this would also work
Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total])[Discount]
I don't know offhand if
Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total],[Discount])
would work, but I suspect not
Addendum  if for some reason you HAD to do it that way (no idea why you might, just go with it), you'd either put negative values in [Discount] or else create a field (called [Discount to Apply] or something) with a formula [Discount]*(1) and use that in your sum formula:
Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total],[Discount to Apply])
(Edited)
Related Categories

Tables & fields
 7229 Conversations
 182 Followers