Calculation - Discounts & Taxes

[Nagasaki]

My coworkers and I are having a hard time working this out. Anyway, here is the story:

On our invoices we sell items (which you select under the "product id" field)that are taxable or nontaxable. I have found a way to set it up so we only charge sales tax on the taxable items.

Well now there is a curveball, because we also will discount certain items (basically anything but memberships)to our high-paying members. There are two problems I am having with this - 1 is that when the discount is applied, it is applied only to the subtotal before tax, so the tax is still being calculated based on the price of the items pre-discount. I would fix this easily, except for problem 2 - I don't know how to tell it to not apply the discount to memberships, or the reverse, to tell it to apply the discount to every thing but the memberships after the tax has been added. It seems like its fairly simple to work on the tax problem or discount problem as separate entities, but together they just seem like they require impossibly embedded equations to correct them.

I've tried to attach a copy of our invoices database so you can see what we're working with.

Thanks for your time,

Abby

InvoicesCopy12-30-06.zip

[Sunshein]

Abby, it seems like you should set up a related table for customers and one for inventory items. That way you would not have to type in names and addresses, or descriptions of items sold for each new invoice. You would then code in taxable inventory items and code customers as to discounts, etc. allowed.

[mr_vodka]

As discussed in earlier posts that you and I had, it is more proper to get away from using these repeating fields and move to a table for your products and a line items table.

What you want to achieve can be done much easier once your tables are setup more properly.

Why do you insist on using repeating fields??

[Lee Smith]

I believe this is the invoice file that came with FileMaker.

Lee

[mr_vodka]

Hi Lee,

Hmmm. I did not know that this was a starter solution from FMI. I am surprised that they have set it up this way for a starter solution.

[LaRetta]

If you mean the repeating fields, don't be surprised. FM doesn't provide elegant example files. :crazy2:

[Nagasaki]

So far we are keeping our inventory by hand, but maybe this would be a good way to push the guy in charge of that to start using filemaker to inventory.

We kind of have a customers database in our mailing or membership database - and actually we are trying to think of a way to link the mailing or membership with to the people fields on the invoices, I can't just think of what field to use to link them (sometimes a membership or mailing is under one person's name, but then their spouse will come in and buy something, so name wouldn't necessarily work) Lol, issues abound!

So per everyone's advice, I think I will try to set up a new invoice database using the types of tables you guys suggested, and ditch the pre-formatted filemaker invoice layout.

Edited January 4, 2007 by Guest

[Nagasaki]

Also, which fields do you consider the repeating fields? The name and address fields on the invoices that are separate from our mailing database?

[LaRetta]

Repeating fields used to hold multiple products instead of a true relational structure using LineItems.

[Nagasaki]

I see...I am a bit out of my range here, can anyone recommend a good book covering this? I can't find anything on it in Filemaker Pro 7 bible. Thanks for your input everyone, I appreciate it.

[mr_vodka]

Hi Abigail,

Take a look at this post and this thread.

Also follow the concepts that you and I discussed in past posts.

[Nagasaki]

So, if I am understanding everything correctly (that'd be a first!), using the line item format on the invoice would benefit us by displaying our merchandise from a related inventory table, so we wouldn't have to type in an item description each time? And also, when entering in inventory, we could flag particular items to be taxable or non-taxable?

[mr_vodka]

Bingo.

Plus while repeating fields limit you to the number of repeats that you have, a line items table that joins the orders and products table is unlimited to how many items there are as part of the order.

[Nagasaki]

Hello,

I was wondering if anyone could take a look at my revised file - first off, the guy in charge of the gift shop where I work is very resistant to the idea of keeping inventory in file maker, so I haven't done anything using line items instead of repeating fields. Instead what I tried to do was to do a calculation on the amount field. Basically it goes something like, if the discount field is not empty, then the product extended amount equals the price x quantity - discount rate x quantity x price (or something like that - the correct calculation is stored in the field product extended amount). The calculation works, but it will only calculate the first item I enter. I had the same problem with the tax before, which is why I had to set it up so that each item is taxed individually. I suppose I could set up the discount the way I set up the tax, but I was wondering if there was a way to make it work this way first.

I have a sneaking suspicion that this is another example of why repeating fields don't work.

Thanks!

InvoicesCopy12-30-06.zip

[AudioFreak]

Not sure why he would be reluctant to using FM to track your inventory?? Set up properly when you create the invoice you could have it automatically remove that item from the inventory.

Maybe that's why......He's afraid of losing his job.........o_@

Yes drop repeating fields. They will only make your solution harder to deal with as it grows.

[mr_vodka]

Abigail,

Although I still Strongly believe that you guys should move to a more relational model, I have modified your file with the fixes.

You should tell the boss that its not necessarily an inventory but rather a products table. If he doesnt want to track his inventory that way, then that is fine but at the least maybe you guys can enter the products into a seperate table.

Good Luck.

Invoices_new.zip

[Nagasaki]

Hi Mr_Vodka,

Thank you very much for your modifications, I am taking a look at that file as I type. I might have some questions though, which I hope you don't mind - and I am definitely going to work on the relational model, I'm still not sure how it all works yet, but I'll do a test database to play around with it - thats seems to be the best way to study filemaker!