I have a formula in an app called [💸 Invoices] that calculates a [Total] (Formula) based on a [Direction] (Single Select) case, an [Amount] (Currency), and a [Tax] (Percent):
CASE([Direction],
"⬆️ Expense",
-([Amount]+([Amount]*[Tax])),
"⬅️ Payable",
-([Amount]+([Amount]*[Tax])),
[Amount]+([Amount]*[Tax])
)
Then I have a formula that sums up the linked record Amount [Number] from the app called [📚 Ledger] so we know what has already been paid:
SUM([📚 Ledger].[Amount])
I have to find out the [Open] (Formula) balance by using a formula and I have tried the following:
IF([Total]-[Transaction]>0,[Total] - [Transaction],0)
But this formula returns the following error:
SUBSTRACT: Argument with type 'Total' NOT allowed.
I had to ask myself if I was missing something.
and I did some research
The CASE function seems to output a value "Decided by user".
In my case, it was returning a string result, but I cannot find how to decide the output.
Meanwhile, I was able to fix it quickly by replacing the CASE with an IF function:
IF([Direction]=="⬆️ Expense",
-([Amount]+([Amount]*[Tax])),
IF([Direction]=="⬅️ Payable",
-([Amount]+([Amount]*[Tax])),
[Amount]+([Amount]*[Tax]))
)
It is similar to the CASE one but returns a number instead of a string.