Taxes on LineItems?

[David Nelson]

Taxes on products. There are sales tax, state tax, city tax and it goes on. So when invoice is created and a product added into LineItems, do I add with script each of the tax lines as lineitem records? Or do I use fields for these taxes so they calculate and adjust automatically depending upon the price entered or changed on the product?

The problem is, if I enter the product and the taxes on different lineitems in same table then their amounts will not change if I change the product price, which can happen. If the taxes are fields on the product then they would change automatically if I change the product price.

Even lookup will not know to recalculate the tax lines and I don't think I can lookup from same table anyway, right? Ideas? How does everyone handle taxes on invoices? Tax not based upon all items on the invoice - only products flagged as taxable. This is why I can't put tax on invoice and I am not sure that would be best anyway.

I am pretty sure they should be individual records so reports can be ran totaling each tax for the period for accounting reasons. Otherwise I could not make a report with the product in Sales Tax summary and also in City tax summary. How to keep them in synch then?

Edited February 10, 2012 by David Nelson

[Ocean West]

a Record in a line item is a product with a quantity and a flag option to charge tax - the Rate based on several factors can be looked up from a tax rate table such as state tax, county tax, city tax, and other criterion such as Effective date - based on the invoice date -

Typically you would have to have updated your tax rate table prior for a new tax rate to go into effect - sometimes these are published months beforehand - once entered after a certain date passes and the invoice date is ≥ the effective tax rate date then all subsequent transactions will use the new rates.

If you need to split out taxes in to multiple individual totals you could have multiple fields on the row for tax collected / charged. the sum of all rows ends up as a sub total on the invoice.

In some industries they also charge "fees" sometimes the fees can be associated per line item or as its own line item or on the invoice as whole.

You do not want to store the tax rates as a calculated field in your table - because when you do have to change - it will ripple thru every prior invoice and update the totals.

Tax fields should be a LOOKUP based on a tax table.

Hope this helps

Stephen

[David Nelson]

Hi Stephen,

Thank you for helping. Then you say that the taxes should be fields in the LineItems table and not records? In this way, they can be set up as a Lookup to a tax table and also their values will change automatically when the product price changes on the same record? The dollar which should be calculated is ProductAmount * 13% for sales tax, ProductAmount * 7% for state, etc. Doesn't a lookup just look up a value from the tax table? So the lookup would only give me 13% or 7%.

I cannot understand how tax table would work. Are there any examples I could pick apart?

[Ocean West]

Nothing handy - tax table would be some key fields, jurisdiction, state, effective date, rate - as tax calculations don't rest on zip codes, i'd try to find some other field to key off of.

You would have to have a rate for each tax ( one for state/city/county )

2 Left Handed Widgets 100 ea, CityTax: Yes, CountyTax: Yes, StateTax: Yes, CityTaxRate: .05 , CountyTaxRate: .05, StateTaxRate, .10

each tax rate would be a column. then extend it out to multiply the rate x productAmount.

[David Nelson]

I have tried using an invoices demo file by Comment. I have attached it. I created a tax table and flag certain products as taxable. But fields can't relate to the tax table. Once I select a Product, I will know whether it is taxable or not by just looking to the Products table.

How can I tell the Sales Tax field to look up the record in the tax table for TaxID 1 to get the 13%? If a product is taxable, all tax rates apply. There is no 'by zip code' or dates. I just need to look up the tax rate, multiply it against the actual product and fill the field (or record).

Something does not feel right with multiple fields holding same thing, namely money but I'm okay with it if that is how it should be. But how to make a field relate to a record in tax table?

InvoicesDemoChanged.zip

[comment]

See if this helps. Note that the tax rate must be auto-entered from a global field or looked up from a Rates table - so that each invoice stores the rate applicable at that time.

InvoicesDemoChange2.zip

[David Nelson]

Hi Comment,

Thank you for the file adjustments on your file. I see and understand the LineItems and Products but I am stuck on the Fields in Invoices.

So the Tax Rate field in the Invoice is an example of one tax, right? So it would be renamed permanently to SalesTax so it can hold the rate so it will not change. Then the calculation would be: Sum ( LineItems::cTaxableAmount ) * SalesTax which would give me the Sales Tax for this invoice for all taxable products.

Then I would create a field called StateTax and also a calc for it and do the same thing for all of the taxes? How to get the tax rates ... since I have no relationship, I could load the TaxID and rate into global variable, maybe when file starts since there are few. Then when records are created, it would parse from the $$taxTable and fill each of the fields with the rate. I think I could get this to work.

So none of these taxes will even exist in LineItems as records, right?

[comment]

Well, if I were a mathematician I would say it doesn't matter: if it's taxable, it's taxable - and a sales tax of 6% combined with a state tax of 4% is the same thing as "assorted taxes" of 10%.

However, I assume you want to keep a record of how much you charged for each type of tax, so yes: you need to lookup each rate into its own field and calculate each amount in its own calculation field, before adding it all up.

How to get the tax rates ... since I have no relationship

You could keep them in global fields of a preferences table. Global fields can be referenced without a relationship. If your solution is served, you would need to open it in single-user mode in order to change a rate.

[David Nelson]

Here is what I did to the file. I created Cartesian relationship from Invoices to Tax table then set the Invoices::TaxRate to auto-enter:

List ( TaxTable::Rate ) with Uncheck do not replace just in case. This way it would be automatic and relationship to TaxTable must remain unsorted so the order of the TaxID matches the value order in the field.

And then cTax changed to:

Sum ( LineItems::cTaxableAmount ) * ( "." & Evaluate ( Substitute(TaxRates ; ¶ ; "+" ) ))

If they want to know an individual tax line we can give them that with GetValues() being equal to the taxID whenever they want to break it out. I can even put it on bottom of invoice as conditional format with Let() I am sure. I attached the file again.

I believe this is what you meant for me to do by using the global field.

WOW. I was just responding and read your response. And I was wondering how badly and how often they want to see the individual rates.

Comment said, "and a sales tax of 6% combined with a state tax of 4% is the same thing as "assorted taxes" of 10%."

Yes but if rates change, we would lose the knowledge of the individual rates. So that is why I decided to store as lines in the field.

InvoicesDemoChange3.zip

[comment]

Don't you ever need to answer a question like "how much sales tax did we collect in 2011"?

[David Nelson]

Yes, I can bet they will ask for Sales tax for 2011. I was trying to save fields and only create them when asked figuring I can generate them on the fly fairly fast and I thought like you did, who cares about the individual values.

The report I saw from AccPac has each tax summarized like all the other categories, down alphabetically as TAX: SALES, etc which, to duplicate, I think would mean putting them as records in LineItems since that report shows summarized invoice lines.

I am not sure if it is important to them. I will have to wait until Monday to check. As always, I appreciate the time you'all give here. Stephen, Comment, thank you both.

[LaRetta]

Hi David,

I know you are waiting for clarification on the requirements but I wanted to add my perspective. The reason you don't see a lot of examples of attaching Tax Tables is because it is specialized to each business and there are many approaches and requirements. I prefer to keep all money within a single table - LineItems.

If you tax each individual product line instead of the Invoice level then those taxes are available for reporting a product's true cost even when you separate the lines from their invoice for summarized reports by Product Type in LineItems, for example. On the concept of taxes being individual records in LineItems ... even with the advent of script triggers, I would not risk using them to keep several tax rate records in synch with one, or potentially several, products on an Invoice.

If the taxes are fields in LineItems instead of records, taxes update if the product changes because they are in the same record. And yes, it means you can have empty fields if the item isn't taxable but most LineItems are products anyway and empty fields are cheap.

In the attached, modified Invoices demo that you provided, I show how I would approach it. In this example, all rates are the same for all taxable products. I loaded global variable and parse it like you were thinking but how the rates are determined and looked up isn't important - it can be direct lookup from the tax table, looked up from the Products table, global fields in Preferences file or variable. Only use variable if the list is very small, like in your example. Tax Rate tables also can contain either a Status field or date range, as Stephen suggested, so the right rates are looked up according to the Invoice date and old Rate records are then preserved. But by storing the tax value within the product's record, rate is known without preserving it. And the tax values SHOULD change every time the product changes ... right up to the point of freezing the Invoice.

Since the taxes on each line are stored, strict rules on modifying Invoice lines must be in effect, i.e. if month-end has closed (or whenever you determine that an Invoice is finalized) then records are frozen permanently by Privileges. That holds true in any system really. In this file, I just included a Freeze field in LineItems which prohibits update of the auto-enter (replace) calculations. If you do not and you ever have to migrate frozen data, and if you accidentally specify 'perform auto-enter', you could accidentally change their taxes. Between Privilege restriction (even for Admin) and the auto-enter Replace calculations checking for 'Freeze', the data will remain unchanged.

InvoicesALT.zip

[David Nelson]

Hi LaRetta,

I see how the tax would be better in the LineItems and maybe that is what I was seeing in AccPac. The example I gave was a bit simplified but not by much. I can duplicate the auto entry so it works as you describe and it only taxes correctly on those products.

Are we not creating multiple same-type fields which is not normalized relationship? Does three count as many? I guess the same question applies to Comment's approach.

So I still need to check whether the taxes need to be records to provide reporting [grouped by sales tax type?] as they may require. If not then this seems easiest, less number of fields and in-depth detail at line level if needed. Anything I might be missing here? I do not know how to compare these techniques except that the number of fields seems less. When two of the best from this forum provide two different approaches then the choice is daunting.

Well, if the tax lines need to be records, the questions are no longer relevant and the choice less meaningful but I still would like to know. This is just one of a thousand such decisions I see I'll be facing through this design. The more I understand each decision hopefully the less I will need to ask. I think I own real estate in the Relationships section here.

If you both say triggers are too hard so taxes can be records then I know they will be too difficult for me to pull off. I hope they don't ask for it. Thank you both for your valuable input.

[LaRetta]

Hi David,

Nobody on this earth is better at understanding relational logic than Comment so it is rare that I suggest alternate methods. It isn't that one approach is better than the other but rather one (mine) provides deeper detail and the other (Comment's) provides simplicity and automation. Most of the work I have done has come from (or required) more detailed tracking of taxes (as product+tax totals). I just wanted to add depth to your possibilities if line detail is needed.

Are we not creating multiple same-type fields which is not normalized relationship?

Yes we are. Neither of our methods adhere to the utopian normalization (which is a goal not an expectation). Taxes aren't usually lines on an invoice (check your invoices to find out, I can't recall on AccPac) but taxes are usually considered 'outside' the Amount field so the tax values can be tracked at either Invoice or LineItem level.

And of course update of the tax values will not naturally occur if the taxes are records - that would require script which opens more possibility of error. If you can leave the data requirements to the natural evaluation within FileMaker (via auto-enter, proper relationships and allow-creation) then scripts can be skipped ... and so much the safer.

[comment]

If you both say triggers are too hard so taxes can be records

It's not about being hard or easy. The purpose of normalization is to avoid redundancy, i.e. the same data being stored at more than one place. When changing data in one place (the ProductID in LineItems) requires modification of stored data in another (a Taxes table), that purpose is defeated.

Regarding placing the tax fields in LineItems vs. Invoices: it's not a very big difference. If only some of your products are taxable, then LineItems might be a better-suited location. OTOH, what do you do if you want to grant a discount on the invoice level?

[David Nelson]

I am in trouble. They want reports summarized maybe with and maybe without the details by category. The categories are the accounts.

category amount

Freight $25.00

Samples $650.00

Label Fee $11.00

Sale Discount $11.21

Products $324.00

Sales Tax $37.00

City Tax 22.00

So this means everything as lineitem with triggers and scripts. And yes to discounts as lines. They want everything as lines so each can be grouped by category in single report. I am in deep trouble here. Thoughts, suggestions, prayers? :hmm:

[Ocean West]

When you say "they want the reports" is that meant reporting is done outside the use of generating invoices?

Store data as necessary for creating invoices, but when you want to get month end summary totals perhaps

a server side script can populate a summary sales table?

Perhaps an alternate approach is for reporting purposes using the VirtualList Technique.

[David Nelson]

Thank you everyone for ideas. Taxes vary by product type and taxes are considered important in allowing discounts and such so I have decided to go with LaRetta's idea of taxes being fields against the product. Because of some other complex reporting items, it looks like I will need virtual table anyway so I can replace those reports.

[LaRetta]

I am glad that you aren't having to script those taxes as lines; it would be risky. You can write static data (such as posted invoices and lineitems) to tables, as suggested by Stephen. Since this data can never be changed, it only needs to be written once. This balance between written static data and current aggregate is usually the best way to go to keep the speed up in your solution.

Also search for FastSummaries to help speed the process.