subtotals by category

[JoBo]

hello all,

i am new to filemaker pro 7 and accidently came across this very interesting website.

i have already tried to get some help to my problem at comp.databases.filemaker, but there's still no satisfying solution in sight. as there seem to be some pros out here, i have decided to bother you as well ;-)

basically i have the following problem:

i am building an invoicing database. it's already working quite neatly with customer, products, invoice and lineitems table linked to fill in data automatically.

for fiscal reasons, i need to be able to print invoices that list different tax rates and amounts of different products seperately. i.e., i have to calculate a subtotal for each tax rate for the according listitems.

since there may be one or more tax rates in an invoice, i would like to find a dynamic approach, instead of "hard-coding" summary fields for every possible tax rate. (the rates may be subject to change. in that case i would have to redesign the database.)

maybe you will get a better picture if i show you, what i have got so far and what i still need:

the following (simplified) tables are already working fine (set up with relations to products and customers and according lookups)

Invoice

-------

ID

InvoiceNo

CustomerID

Date

Invoice_LineItems

-----------------

ID

InvoiceID

Quantity

Article

PricePerUnit

TaxRate

example data:

Invoice

-------

ID...InvoiceNo...CustID...Date

1.....000-000......1..........05/22/2005

Invoice_LineItems

-----------------

ID...InvID...Qty...Article......PpU......TaxRate

1.....1..........3......Flooboo....1,20.......7%

2.....1..........1......Shnoops...2,00.....16%

3.....1..........2......Noofoo.....3,00.......7%

4.....1..........4......Foowhoo...2,20.......0%

and i need a print-out like that:

Date: 05/22/2005

Invoice No.: 000-000

Customer No.: 1

Qty...Article..........PpU......Price...TaxRate

-------------------------------------------------

3.......Flooboo.......1,20.....3,60......7%

1.......Shnoops......2,00.....2,00....16%

2.......Noofoo........3,50.....7,00......7%

4.......Foowhoo......2,20.....8,80......0%

.....................................------

net 0%...........................8,80

net 7%.........................10,60

tax 7%...........................0,74

net 16%..........................2,00

tax 16%..........................0,32

.....................................------

total...............................22,46

.................................======

my big problem is the last part of this invoice: the listing of tax amount summaries before the grand total. i don't know how to implement that kind of "subsummary by category". additionally, it would be nice if the the print-out showed only occuring tax rates in order to avoid list items like: net 0%=0; net 7%=0, tax 7%=0.

the only solution i can think of right now is to calculate the tax amounts for every possible tax rate in the line items table and create summary fields for those within the invoice table. but if the rates change i will have to add more fields.

that cannot be the appropriate "filemaker way", can it?

any help is greatly appreciated,

jo

[comment]

This is not easy. The "appropriate Filemaker way" is to show this information in subsummaries. But subsummaries are either above or below the body part, and you want these 'stand-alone' - AND you want to show the body part as well.

I am saying this to show that no good solution is possible. One could perhaps make these tax summaries available as 'live' calculations, and still there would be a problem of where to stick the portal.

With that in mind, I settled for a scripted solution. In fact there are two of them. The second one is very simple, but the print quality may not be the best. AFAIK, this was better in previous versions, where a copy in Preview mode would result in a vector image.

taxSummary.fp7.zip

[JoBo]

wow,

cool, this is exactly what i had in mind. thank you very much.

i'm not really done unterstanding all your scripting yet, but the result is pretty impressive.

i have spent last night figuring out a solution as well.

i think, it's the kind of "live calculation" you mentioned.

unfortunately it is only working theoretically.

maybe you can point out my mistake:

i added another table TaxSummaries containing:

TaxRate = all possible tax rate values

TaxSum = summary of according line items

InvoiceID = global, to be initially set by a script

linkInvoiceID = If ( NOT IsEmpty ( TaxSum ) ; InvoiceID ; "" )

this table is linked to my lineitems table through

LineItems::TaxRate = TaxSummaries::TaxRate and

LineItems::InvoiceID = InvoiceID::ID

so for any given InvoiceID all records in TaxSummaries calculate the sum of the respective tax rate or return nothing, if the particular tax rate doesn't occur. additionally the linkInvoiceID returns the InvoiceID or nothing.

now i have linked another instance of this table to my Invoice table by InvoiceID::ID = TaxSummaries:linkInvoiceID,

in order to have a portal of TaxSummaries in my Invoice table, that only shows tax rate items that have a non-zero value assigned to their respective summary field.

but filemaker won't let me do so, because the linkInvoiceID calculation has to be unstored, since it depends on the TaxSum field calculating it's value from LineItems and thus can't be used in a relation.

with your way, i don't need this solution anymore.

it just kind of annoys me that i don't seem to be able to get this concept to work.

(i have just spent too much time on this one to just let it off the hook so easily ;-)

maybe i have just forgot to tick one filemaker option to make this thing work.

well, for now i'll be trying to make your scripts work in my data base.

if you ever come to berlin, germany and are up for a drink, please send me an email.

thanks again,

jo.

[comment]

I see several problems with your attempt:

First, you want to link a TO of the summarising table to the invoice using a global InvoiceID - but you cannot have a global on the right side of a relationship. Likewise, the calculation of the key depends on fields from the left-side TO. Third, and perhaps more important - how would this be used in practice? It's only a "calculator", displaying the breakdown for the currently selected invoice. I'd think you'd want something more permanent, that sticks with the invoice.

Initially, I also tried to do this with relationships and calculations. I thought perhaps there should be a table with all tax rates currently in effect, and a LineItem - or Invoice - should lookup all of them, and put the data in the correct pigeon hole.

But then if the tax rates changed, we would be left holding completely useless information. After all, only one tax rate applies to any single item, and if an Invoice didn't have any items @ 16% tax rate, why should the number 0.16 be stored there forever - when tommorow it might be 0.15 or 0.17?

So my conclusion is that this is a problem of data DISPLAY, not data organization. All the data you will ever need is already there, and can be reported via subsummaries. All that's needed is to shift it to another location on the layout.

[JoBo]

yes, i see your point.

but at the moment the method with another linked table would simply be much easier to understand for me than your script, which i am still not completely through with yet :-)

btw, i'm not sure if i would really mind having a tiny extra table with five or six tax rates and calculations. it could be used for popups to determine the given tax rates at the data entry, so it could even make a little contribution to the whole system.

well, now i have got your cool (and actually working) solution at hand, which i will eventually understand and put in all of my data bases.

thanks again,

nice week end,

jo

ps. dummy-question-alert: what is a TO?

[comment]

It's not that difficult to make the calculations. The problem is that they have to be in a portal in the Invoices layout. If you want to print the Invoice from the Line Items table, then they will not be very useful.

FWIW, take a look at the attached (the Invoices Copy layout).

I have also implemented the tax rate pop-up - but it is a completely separate issue. I have added some more comments to the existing scripts as well- perhaps it will help you to decipher what I have done.

So far, I like the 'Print Invoice 2' script best: it's the most clean and effective solution, with minimum resources used.

taxSummary.fp7.zip