Material db design question

[danfoshee]

I'm not really sure if this is the right forum, but I'm not even sure which one to put it in, I'm so confused! (If not, please tell me; a hint on where TO post it would be helpful. If it's a case of RTFM, let me know which part of the manual I should go over, or if there's another printed resource I should look into.)

BACKGROUND

I have a series of FileMaker 5.0 databases--hosted by FileMaker Server 5.0--used in conjunction with a third party product that uses FileMaker as its data storage and report generator. I've built upon that to also provide job/project tracking. So I've got the IntelliTrack db for all my labor & time data storage, and report running; the JobTrack db for all the individual job & project information, including client info and materials' costs.

Now I want to start splitting off some of the jobtrack db's functions (yes, I know, bad way to do it, but I had a deadline) off to smaller, more specialized db's--like for client contact info and materials--using relationships. My problem/question is with the materials db I'm creating:

I've assigned a Materials Invoice # (MatInv#) as a key to a record, and the user can input ONE item of a materials list, referencing the Job# (that is, the user inputs the Job#, which kicks off a lookup to fill in the job info, an auto date/time stamp, then the user fills in the material's description and cost).

The ONE item per MatInv# is the problem; ie, some jobs might have a whole list of materials, and I don't want the users to have to fill in, say, 10 different invoices (each with different MatInv#'s) for that same Job#. What I want is for the user to be able to add another list item, as needed, onto that MatInv#.

I'm stuck in the idea of having ADD and REMOVE buttons next to the Materials Description (MatDesc), which takes the user to a similar layout as the first, but with two MatDesc fields (MatDesc1, MatDesc2, etc.), or to the previous layout in the REMOVE instance, with a clearing out of the field added. But since I might have to do up to 10 or 15 fields and layouts, I'm not too keen on that approach (I'm not adverse to it, but I want to keep things as simple and as elegant as possible; yes, I am a Mac user).

I tried repeating fields, but could only get the first instance of the first record to show up on a report, and I'm seeing on this and other FMP lists that repeating fields aren't a good idea to use anyway. There's GOT to be a better way, but what?!?!

Thanx in advance,

dan

[LiveOak]

If I understand what you want to do, you need one more file. You need a file where one record is a Material Invoice (by number) and a related transactions file (one to many with the Material Invoice) where a record is one line item on a Material Invoice. Please DON'T use repeating fields or multiple fields for the line items!

Your stucture might look like

Jobs --one-to-many--> Material Invoices --one-to-many--> Material Transactions

-bd

[danfoshee]

LiveOak:

I think I understand the structure you lined out; thanks! But how would I do the add/remove items in the Transactions file? I'd like to avoid setting up 15 different layouts (exaggeration, but still...) if I can.

Could/Should I use a portal in the Materials db that points back to the Transactions file? I ask, b/c I'm not that familiar with portals; I've read about them, think I understand the basic concept, but have never set one up, not like this.

[danfoshee]

Went out on a limb and made the third file, Material Transactions, then made a portal. Works great!!