- If the sales tax is determined by the product type, then I would include in your product table a TaxCategory (eg:, Zero, Low, High).
- I would create a Tax Rate Table, with those 3 records and their relevant rates of tax.
- I would make the invoice have a TaxRate field which was looked up by the TaxCategory of the product on the invoice.
- That way historic invoices won't have their tax rate changed when you change the current tax rate.
this sounds like it would work but each invoice would be for a different county so would I just create the tax table with a unique ID and relate it to the invoices table?
Include County in your relationship (and in the taxrate table) to get it to match to tax rates by county.
thanks. working well!
Well this isn't working as planned. were you saying
"- I would make the invoice have a TaxRate field which was looked up by the TaxCategory of the product on the invoice."
I read this as Invoice::Tax Rate is a looked up value from the new Products::Tax Category field created but shouldn't it be in line item as it is unique to each line item i.e Soft or Trinket or Media - the only 3 categories of product?
If different categories require different tax rates, make a new table of just categories and tax rates. If these rates vary by count, add a county field and add records to this table for each combination of cateogry and county to record the appropriate rate.
Put the Category name in the Products table so that LineItems can look up the category when a product is selected. It can then use that looked up category (and county) to look up the tax rate. (County can be a field in line items that auto-enters the county specified on the parent, Invoice record.)
Given the "Multi-stage" lookup involved, you may have to use a script trigger to insure that the tax rate is successfully looked up and copied into the taxrate field in LineItems from your tax rate table.
Thanks! Part of the problem was that I already had records in the portal so that is why I wasn't seeing the changes but when I deleted and re-added item, it populated so now I will go back and make sure I don't have any un-necessary look-ups because I was trying every which way possible.
well I re-populated the portal and the categories show up fine but the if I type in tax it auto-fills ALL the tax fields for each record, it doesn't apply the different rate for each category. I may have misunderstood how t build the tax rate table but it has 3 fields: ID, Category and Rate. I then have 3 records of soft, Trinket and Media with corresponding tax rates (each different so I can monitor input)
The Line Items Tax Rate looks up from Tax Table
I have not started with the County portion yet.
You should only get one tax rate for a specified category. One field in lineitems gets a single rate from the one record in the Tax Rates table that matches to the specified category.
It should work like this:
- You enter or select a productID in a line items record.
- The category for this item is looked up from Products.
- The tax rate is then looked up into the one and only taxrate field in lineitems from the matching record in Tax Rates.
Keep in mind that the second look up may need some scripting to make it automatic. (Others have reported trouble getting the second look up to occur smoothly when it relies on other data to be looked up first.)
Here's a demo file: http://www.4shared.com/file/4-ec0wBD/InvoiceswTaxRateDemo.html
I took the Invoices demo file created by Comment (Download link for original version: http://fmforums.com/forum/showpost.php?post/309136/ ) and added the needed taxRate table, category, county and calculation fields to demonstrate a tax rate system that matches both by county and by product category.
Note that I needed an OnObjectSave script trigger on the county field to commit the current invoice record before the line items records would correctly look up the county so that it could in turn be used to look up the tax rate. This demo assumes that "county" is the location the sales transaction takes place. If "county" is the county of residence of the customer, you can move the count field from invoices to contacts and look up the county from the Contacts table in stead of Invoices.
Thank you very much. This makes much more sense now that I see the relationships as well.
My county is included with the Customer address which was imported when I started with FMP. It seems that now I would need to add a county to a new table and input new tax data in the taxrate table for each new invoice unless I am doing something wrong.
In the solution you provided I created a new invoice with Clark as the county and I had to then input Clark into taxrate table 3 times for each category as well as tax info and then go back to the invoice to finish data entry. This seems to make the problem worse, not better. Maybe I should rethink and keep the tax info with the Customer info(?) and work on how to incorporate that into the lineitems table.
I liked your solution and I found some errors with my realtionships as we have almost the same, identical tables.
In the solution you provided I created a new invoice with Clark as the county and I had to then input Clark into taxrate table 3 times for each category as well as tax info and then go back to the invoice to finish data entry. This seems to make the problem worse, not better.
I don't see how that makes things worse. You will only need to do this operation for each new county. Once you've added tax rate info for a given county, you will not have to do it again unless the rates should change. Any method I can think of will require much the same set up. trying to do it within the customer table will be a real nightmare as this could multiply the number of times you need to enter tax rates many fold. (If you have 5 customers in the same county, you have to enter 15 tax rates: 3 categories X 5 customer records.)
Did you notice that once you've entered a county or a category in the category table, you need only type in a few letters of either in a new record in this table and it auto-completes to fill in the rest of the name to save typing?
Did you know you can use duplicate record to copy an existing record and then just edit the field or fields that are different from the one duplicated?
Keep in mind too, that this just a proof of concept demo file. There are a number of options for making the management of your tax rate records less of a hassle. Layouts, an added table for managing counties and scripts are all possibilities here.
You might save a little bit of data entry by using one record for each county with separate fields for each category's tax rate, but that is much less flexible an option---espeically should tax law or business practice changes require adding an additional category.
Right I see your point. I wasn't thinking about the long term. thanks for the insight. I'll revisit my db tomorrow. Maybe I'm just burnt out and still going through Excel withdrawal.
this worked wonderfully and I put a small portal of the info on the invoice worksheet (which is used before final invoice is created)
Working on the final summary to separate the categories and final totals now.