Invoice Trouble

[Nagasaki]

I apologize if this is in the wrong area, I tried looking for the "sandbox," but didn't see it.

We use filemaker where i work to ring up items we sell in our gift shop. The problem is that we sell both taxable and non-taxable items, and our invoice is set up so that you can either charage tax on all the items, or none. How we have gotten around this is to create two seperate invoices, one of the taxed items being sold, the other for the non-taxed. This gets to be a hassle when we need to ring someone up twice because they bought both a taxable and non-taxable item.

What we were wondering is if there is a way to set something up like, "If the case is that a nontaxable item is in the items purchased field, don't use that item to calculate the final sales tax total."

Right now the only nontaxable items we use our invoices for are memberships to our organization and charges for our workshops.

If I can clarify anything, please let me know.

TIA

[mr_vodka]

Abigail,

In your line items table, you can just add a field that checks to see if the product is taxable or not if it is, do the tax calculation else return nothing. Then you can just just do a sum on that new field to determine the tax for the taxable items.

Somthing similar to this:

Case ( Products::itemtaxable = 1; lineitem::price * taxrate )

In your invoice Table, just have the totaltax field to be Sum (lineitem::taxamt)

or

you could put the tax rate on later

Case ( Products::itemtaxable = 1; lineitem::price )

In your invoice Table, just have the totaltax field to be Sum (lineitem::taxamt) * taxrate

Edited November 21, 2006 by Guest
Added Another

[Nagasaki]

Abigail,

In your line items table, you can just add a field that checks to see if the product is taxable or not

That sounds good. One quick question though, how do I mark certain products as "taxable" or "non-taxable" so it knows what to check for?

Thank you

[mr_vodka]

In your products table, there should be a field that should be flagged if it is taxable.

In my exmaple above, I called that field 'itemtaxable'. In your interface for modifying your products, there could be a checkbox or button that sets that field to 1 or you can just manually enter 1 for those items that are taxable.

[Nagasaki]

Sorry to bother you, I just have one last question. Which field do I put the calculation Case ( Products::itemtaxable = 1; lineitem::price * taxrate )in? I tried putting it in the total, which looked like: Subtotal - Discount + Case (Products::itemtaxable = 1; lineitem::price * taxrate ) + Shipping, but that just didn't work.

[mr_vodka]

Sorry been away for a few days.

Okay, let me go through the whole thing again.

There should be at least 3 tables. A Products, Invoice, and Line Items table. The line items table is a join table between the invoice table and the products.

In the products table, there should be a number field that just flags the item to be taxable or not. When you guys enter in items for your inventory you should know whether its taxable or not. Just have a field (which I called itemtaxable) with a checkbox using a value list and value of '1' on the layout where you enter the data for the product. This will toggle the value to be 1 if checked and nothing in unchecked.

Then in your lineitems table, create a new calculation field (I called it 'taxamt') with formula:

Case ( Products::itemtaxable = 1; lineitem::price * taxrate )

Then In your invoice table, you need to create another calculation field which I called 'total tax' and that formula would be Sum (lineitem::taxamt).

Then finally, your TOTAL would be a calculation of ( Subtotal - Discount + TotalTax + Shipping )

[Nagasaki]

This is a followup to a question I asked before. Basically where I work we sell taxable and non taxable items. Our non-taxable items are our memberships and workshops. Everything else that we sell is taxable. I am trying to make it so that the sales tax field in our invoice only calculates tax for our taxable items. So far I have this calculation:

Case ( Product ID = "Apparel" or "Art" or "Books" or "DVD/Video" or "Misc" ; Product Extended Amount* .0825)

However, when I do a mock invoice, no sales tax is being calculated at all. What is wrong with this equation and do you have any suggestions as to how to edit it?

[Nagasaki]

Ahh, sorry, before I posted the question above, i didn't see that you had responded...let me try that first, thanks!

[Nagasaki]

Hello,

We only have one table for our invoices, and before I break it into 3, I tried this calculation first. Basically, I want to flag specific product ids as taxable - if someone purchases art, its taxed when the cashier picks "art" from the product id drop down menu. If someone purchases a membership, that will not be taxed. As a calculation, it looks like this:

Case (Product ID = "Art"; Product Extended Amount * .0825 ; Product ID = "Books"; Product Extended Amount * .0825; Product ID= "Apparel"; Product Extended Amount * .0825; Product ID = "Misc"; Product Extended Amount * .0825; Product ID = "DVD/Video"; Product Extended Amount *.0825; Product Extended Amount * 0 )

My problem with this is that it is only working for one line of my invoice. I have the calculation set up to repeat 15 times. I can't for the life of me figure out why its still not applying to more than one line.

I forgot to add earlier also that we don't inventory our items on the computer, we still do it by hand (yes, we have some catching up to do)

Edited December 16, 2006 by Guest

[Nagasaki]

Nevermind, I just figured out my own question - I needed a sum of all the taxed lines. D'Oh!