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?

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.

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