Problems with Calculation and Nested IF instruction based on a Checkbox

[n7mafia]

Hello, I got a problem with a calculation, perhaps someone can help me out?

I got these user entered fields:

DE_Purchase Price

DE_Sale Price

Tax Free (single checkbox)

I got these calculated fields:

DE_Original Price

calculation: 100*DE_Sale Price/85

DE_Profit

If ( DE_Sale Price > 1; DE_Sale Price - DE_Purchase Price ; 0 )

What I would like to achieve is that if the user checks "Tax Free" FMP refrain from subtracting the 15% fee from the profit. I mean the program does correctly calculate the profit and subtract the fees as intended but wheter I check or not the "Tax Free" field it does not recalculate (or calculate at all) the pure profit without the fee. I tried with a nested IF instruction but it doesn't work:

If ( DE_TaxFree = "Tax Free" ; (DE_Sale Price - DE_Purchase Price) ; (If ( DE_Sale Price > 1; DE_Sale Price - DE_Purchase Price ; 0 )))

[comment]

I don't understand your question.

What I would like to achieve is that if the user checks "Tax Free" FMP refrain from subtracting the 15% fee from the profit.

But it is not subtracting 15% fee from the profit now.

[Lee Smith]

Hi Mafia2020,

Please don't use the Quote icon, instead use the Code icon, it is the one next to it.

I couldn't follow your code, so I have made a sample file using the following fields

Cost = DE_Purchase Price

Tax Rate (is a new Field instead of hard coding the tax Rate)

Sale Price = DE_Sale Price

Tax Free

Profit

Tax Amount (is a new Field instead of hard coding the tax Rate)

Profit Copy (Uses the Tax Amount Calc instead of Hard Coding)

HTH

Lee

Mafia2020.fmp12.zip

[n7mafia]

Sorry guys in the heat of the moment I must have overcomplicated my question, I made a sample DB to demonstrate what my problem really is, thanks again for helping out.

Basically, the customer wants two options: one which calculate the profit after deducting a 15% fee and one that calculate the profit without any fee to deduct (that's why I hardcoded the 15% fee since it is either 15% or nothing). I managed to figure out the simple calculations involved into calculating the profit with the 15% fee (which is the most common scenario) and another calculation to calculate the original price (before applying the fee) but I wanted to make it so that IF the customer checks the "DE_TaxFree" checkbox the calculation does NOT subtract the 15% fee and simply makes a difference between DE_Sale Price and DE_Purchase Price (actually this feature does NOT work) and it would be useful if FMP recalculate the "DE_Profit" calculation anytime the "DE_TaxFree" is checked or unchecked (instead of calculating the field once and leaving it be.

I would like to achieve two scenario:

Option - A - Sale with 15% fee to deduct (most common scenario)

The customer inputs "DE_Purchase Price" and "DE_Sale Price" (which is the net income), FMP calculate the "DE_Initial Price" (which is equal to DE_Purchase Price + 15%) and then it calculates the DE_Profit (which is DE_Sale Price - DE_Purchase Price)

Option - B - Sale without any fee to deduct

The customer inputs "DE_Purchase Price" and "DE_Sale Price" (which is the net income) then checks the "DE_Tax Free" checkbox. FMP then refrains (by duplicating the content of DE_Sale Price) from calculating the DE_Initial Price (since it is equal to DE_Sale Price) and the it calculates the DE_Profit (which is DE_Sale Price - DE_Purchase Price)

Currently in the sample DB I uploaded "Option A" does work with a simple IF instruction, what it does NOT work is option B when I check the "DE_Tax Free" checkbox, I tried with a nested IF instruction but it doesn't work.

Test.zip

[Lee Smith]

Did you look at the file I posted?

[n7mafia]

Yes, I have seen that of course (thank you by the way) you used a CASE function (which I felt I should have used insteaf of an IF function even if I don't know how) and a LET function, problem is I couldn't manage to replicate them on my database.

[Lee Smith]

Attach a copy of your file, and I'll code it for you.

[n7mafia]

Thank you very much Lee. File attached.

DB.zip

[Lee Smith]

I'm confused by two of your fields.

What is the difference between

Purchase Price of 10.00 (i took it to mean Cost) and

Original Price of 19.00 They seem like the same thing, but obviously you have different amounts?

i'm not understanding the purpose of your Field DE_Fees ? it doesn't seem to be used anywhere.

see if this helps

DB_1.fmp12.zip

[n7mafia]

The DE_Fees field will be used to calculate the overall fees paid, thanks for coding this for me Lee, I can now venture into the LET instruction and begin use it efficiently (at least I hope so) :)

[Lee Smith]

I like the Let Statement for a lot of reasons. Also, I prefer the Case Statement over the If Statement. IMO, the Case Statement is easer to read, write and to understand.

I would recommend that you consider the creation of two new fields.

1. Tax Rate (instead of hard coding the 15%) sales tax can vary from state to state, along with the counties and cities. You don't want to have to change this calculation accordingly.

2. Tax Calculation, the actual amount should be calculated and shown so that you can see the amount,

If you have trouble with this, let us know.

Lee

[Rick Whitelaw]

Perhaps I should have reread all these posts, but one thing stands out. Profit should not depend on whether a client is tax free or not! It shouldn't change. If you think otherwise I predict misery with the Feds in your future.

[Lee Smith]

Profit should not depend on whether a client is tax free or not! It shouldn't change.

I'm not following you here, please explain what you are talking about.