Sales tax calculation

[Jredeemer14]

Hello, I'm trying to make a calculation for sales tax for an appliance business. We live on the border of michigan and indiana so there are different sales tax scenarios. I need a sales tax field to know how to calculate based on the following scenarios:

If an item is being delivered is being delivered to indiana (IN), then subtotal sould be multiplied by .07 (Indiana sales tax). if not, it should be multiplied by .06 (MI sales tax). I have something like this but it doesn't work. Any help?

if isempty(Delivery?)

and

Customers::City = IN

then

(Subtotal )*.07

Delivery is either yes or empty in case you are wondering. Any help would be appreciated, thanks - Jeff

else

(Subtotal )*.06

[mr_vodka]

The sales tax rate should be stored in a perferences table or a state table. Then the rate can be looked up or auto-entered into a sales tex rate field in the invoice.

[Jredeemer14]

That would work, except that if the customer is from Indiana and they pick it up at our store, we only charge 6% sales tax as opposed to if we deliver it and they are from indiana, then we 7%. If the customer lives in michigan, regardless it is 6%. So it is a little bit of a dilema.

[Fitch]

You were close:

Subtotal * Case( IsEmpty(Delivery) and Customers::City = IN ; .07 ; .06 )

[Jredeemer14]

That has to be close, but I keep getting an error saying the specified table cannot be found and highlights "and Customers"

Subtotal * Case( IsEmpty(Delivery?) and Customers::State = IN ; .07 ; .06 )

[T-Square]

If the rule is "Item is delivered to Indiana then .07", then I think the formula should be:

Subtotal * Case( IsEmpty(Delivery?) and Customers::State = "MI" ; .06; .07 )

I like to keep each test separate, so I'd write it this way:

Subtotal *

If( Not(IsEmpty(Delivery?));

If(Customers::State = "IN"; .07; .06);

.06)

Also, if the field is named "Delivery?" (with the question mark), it might be good to remove the question mark just for simplicity's sake.

[Jredeemer14]

Subtotal *

If( Not(IsEmpty(Delivery?));

If(Customers::State = "IN"; .07; .06);

.06)

That works like a charm! Thanks a bunch, I have another similar calculation that involves the same fields that I tried modifying the calculation above to work, but no luck.

I have another field that I called "Sales Tax State" which will tell me what state I need to pay taxes to. I only pay Indiana sales tax for people who are from indiana and have it delivered to indiana so I need the sale type of calculation, but the output just has to say either MI or IN (for mi sales tax or in sales tax), so I tried something like this, but it didn't work, any help would be appreciated:

If( Not(IsEmpty(Delivery?));

If(Customers::State = "IN"; IN; MI);

MI)

[Fitch]

I believe that the problem is the same one you had with my original calc: each "IN" needs to have quotes around it, and "MI" does too. Also I copied and pasted your code example, and didn't read your requirements closely. Sorry about that, it should be:

Subtotal * Case( Delivery? = "Yes" and Customers::State = "IN" ; .07 ; .06 )

If you want to separate each condition explicitly, I'd do it this way rather than use nested IF statements:

Subtotal * 

Case( 

 Customers::State = "MI" ; .06 ; 

 IsEmpty(Delivery?) ; .06 ; 

 .07 )

or 

Subtotal * 

Case( 

 Customers::State = "MI" ; .06 ; 

 Delivery? = "Yes" ; .07 ; 

 .06 )

The other calc would be: 

Case( 

 Customers::State = "MI" ; "MI" ; 

 Delivery? = "Yes" ; "IN" ; 

 "MI" )

[T-Square]

If you want to separate each condition explicitly, I'd do it this way rather than use nested IF statements

Well, I say To-MAY-to, you say To-MAH-to

let's call the whole thing off!

David

[comment]

There's more here that just a nuance. The Case() function stops evaluating when it finds the first test that returns true. So it's not only more readable and easier to maintain than nested If()'s - it's also more efficient.

In fact, in applications where speed is of the essence, you'd be well advised to order your tests according to their statistical probabilities (the most frequent cases first), so the number of required evaluations is reduced overall.

Speaking of good practice - I agree with John that hard-coding tax rates into the calculation formula is not.

[T-Square]

Here's how I understand it (and I did think about this for quite a while before making my flip comment):

In this case, if you evaluate the first condition in the top level IF statement, then the second IF statement will only get evaluated when the first one is true. If it's false, then only the one evaluation takes place. So I don't see an added burden.

I grant that using a NOT construct adds an extra evaluation, and perhaps that was what you meant?

And I agree that a separate table makes all this much better...

David

[comment]

if you evaluate the first condition in the top level IF statement, then the second IF statement will only get evaluated when the first one is true.

You are correct (except it's the other way around: the second If() statement will be evaluated only when the first one returns FALSE).

Still, I'd hate to be the one who has to re-arrange the order of tests in multiple nested If'()s structure. I don't think it's good practice, and I wouldn't recommend it to anyone - especially not to beginners.

[Fitch]

Reminds me of The Great Parentheses Debate.

@David, I probably wouldn't have said anything more on the subject if the OP hadn't asked another question. Since he did, I took the opportunity to revise my original calc as well as answer the new question. In doing so, I wanted to present the answer the way I would normally write it. I'm sorry if it came across as hostile to your solution; that wasn't my intention.

To some extent it comes down to personal style (see above link for dissent). That said, I agree with Michael that nested IF functions are more difficult to create and revise; worth considering since the OP self-identified as a beginner. And since we've come this far... I wasn't going to say anything... but whenever I see this:

Not(IsEmpty(field))

I am compelled to rewrite it as:

not IsEmpty( field )

[T-Square]

Personally, my mind balks with case statements, except when I am enumerating a list of exclusive options. Using it to nest clauses just seems weird to my strictly hierarchical brain, which expects that we'll deal in one statement with whether the Delivery box is checked and THEN decide what the sales tax is based on the Delivery... Which was probably why I went all fruity earlier (remember, the tomato is a fruit!). [bTW, I am NOT saying I think I'm right!]

Fitch: I didn't take it personally at all.

{David}

(Parenthetically speaking, here)

[comment]

Using it to nest clauses just seems weird

It does seem weird in this case - whether you're using Case() or nested If()'s. IIUC, you're speaking of 'branching' rather than 'nesting'. Your calc, written out in plain human terms goes:

Is there delivery? If yes, I need to see to which state. If it's Indiana, then tax is .07, otherwise it's .06. But it there's no delivery, then tax is .06 again.

But when you read this, it becomes very clear that tax is .07 in one case only, out of 4 possible:

Delivery:          T         T         F         F

State = Indiana:   T         F         T         F

Tax:              .07       .06       .06       .06

Which could be stated simply as:

Tax is .06, unless there's delivery and it's to Indiana - then it becomes .07.