database design

[justcosmic]

i'm slowly driving myself mad with frustration from trying to get a working group of db's for my small business: I have files for clients, work records, goods, job pricing, and invoicing, plus a simple book keeping file showing in and out payments.

The thing is, I do a lot of invoices that consist of labour(from work records)only, and so i want the details to appear in the work records file, the job pricer file, and then finally the invoice file, plus the invoice total and number to appear in the book keeping file. Sometimes i need to have several items(either labour or goods) on an invoice, & sometimes I need just a general over all description. I've looked at the FMP solution framework, but its too far away from what I need, & I've done weeks of work on my db....but I'm at a brick wall now for days....any help gratefully recieved....

[Fitch]

If you don't have too many work records and goods per invoice, you could use portals, one for each of those related files, plus a description field where you could type in whatever you want. Or you could use several invoice layouts, choosing the appropriate one as needed.

If you tend to have a whole lot of line items, then you might consider either consolidating labor and goods into a single file, or using a separate line items file that would contain both types of records. You would then print invoices from the line items file, but you'd probably still browse and work with them via the invoices file.

www.fmfiles.com might have some example closer to your needs.

[justcosmic]

Thanks very much for the tips....and I'll have a look at fmfiles.com too.

I'll let you know how I get on...

1 Question to add... is there a way to make portal rows expand according to the amount of data in them?....i ask because sometimes my job descriptions are big, but i would like them to reside in the work records file, not the invoice file....thanks again for any help you're able to give

[Fitch]

Portals can shrink based on the number of rows, but the row height is fixed. That's why it's generally preferable to print from the related file, where you can set objects to slide up and reduce the size of the enclosing part.

[justcosmic]

Forgive my ignorance....but does that mean that I have to have the entire invoice layout in the line itmes file for printing, or does it print part from one file and part from the other...& if so, how ? .....sorry too many questions!

....I am aslo thinking of using your idea of having different layouts in the invoice file(I've been working on that kind of idea) and having different portal line sizes in each....i.e. one which is for a single itme with long description, one which is for 2 items, and so on....the only thing is it starts to get really over complicated....surely filemaker should have a simpler way of achieving all this?....i read a question on this forum about tables within fields...i didn't quite understand as it looked complex....but what ever the solution, surely there are some standard templates or something to deal with these kinds of things?

...thanks again for your input...

[Fitch]

Only one layout per print command, one layout per page. You cannot combine parts from different files/layouts onto one page. And yes, I am advising you to print the entire invoice from the line items file. Put the billing address etc. in a header part, and the line items in a body part, summarize as needed, with a trailing grand summary. It works very well. You can make a script in line items that goes to the proper layout, sorts, restores page setup, and prints. Then call this script from the invoices file, returning to invoices when it's done. That way you can have your invoices file for browsing and data entry, but printing will happen "behind the scenes" by switching to line items and back. It's all seamless to the end-user. You ask again for templates, have you looked at fmfiles yet?

[Vaughan]

"Put the billing address etc. in a header part..."

Try using a Leading Grand Summary above the body part for this information.

[Akela]

"Put the billing address etc. in a header part..."

Try using a Leading Grand Summary above the body part for this information.

I've got the same problem. Can't slide objects in the portal. I'm stuck to one and one order line.

For a year I've used my home made invoice system.

(Sure this is complex. And when my native language is not english, it takes time to learn the terms.)

I can't see how to make a print layout without using a portal.

A list layout is not useable because all the fields I have to use in the head and bottom.

Norwegian invoice contains a transfer form in the lower part and shall contain

name and address of the customer and term of payment and so on.

Have I to admit that my problem is unsolvable? Or do the v7 make it better?

[Fitch]

Akela, you can use the invoice or "parent" file to do data entry for the invoice fields that would appear in the header and footer. But then, use a script to print from the related line items file. You can indeed use a list layout in the line items file. In fact, that is the whole point of this method. Put the related fields from the invoice file in leading or trailing grand summaries and/or header and footer parts. You don't need FileMaker 7 for this, it's the standard method we've been using since version 3.

[Akela]

Thank you! Now I understand. Wow. Why didn't I ask this a long time ago...