Jump to content

Calculation - Discounts & Taxes


This topic is 5748 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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??

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 [email protected]

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 5748 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.