Repeating Fields or Relational Table?

[brwnis]

I'm putting together a db to create contracts in selling air-time (broadcasting). Each contract includes the air-time purchase (called "Flight"), the fee for the purchase, and calculations having to do with the breakdown of where the material is to be broadcast.

I need a way to be able to list an identifier for each flight, and have relevant info (air-dates, number of broadcast sites, etc) about that flight be pulled from a related table. I also need to be able to reference the air-dates that are pulled to include in an invoicing portion of the contract.

I have been able to accomplish this to a certain extent using repeating fields, but was running into problems inserting calculations into the fields that were repeating (fee per broadcast site). The first field would run the calc but each field following wouldn't pull all the info needed for the calc. I was recently advised that I might "insert" a related table instead of using repeating fields, but I have no idea what that means.

I have been using Lookups in my repeating fields as I need to preserve the information as its own contract. Am I trying to do too much?..Help..?

[Vaughan]

Ditch the repeating fields and use a relational structure.

Also, lookups are very slow in FMP 7. Auto-enter calculations are a direct substitute and are much faster.

[brwnis]

OK that's the same answer the last guy I talked to gave me, however, I'm not sure how to do a "relational structure" (Bear with me here - I do realize this is the whole basis for FM; just having a conceptual problem getting around this I think). You (and he)didn't give much extra advice so I'm assuming this must be pretty simple, so please forgive me if this is a remedial question:

Each contract could have as many as 13 or 14 or more (or as few as one) flight, and I want to be able to input the Flight identifier, and have it pull up the related dates, locations, etc. for that flight. Also in each line (flight) item I need to be able to input a fee, and have it calculate a per-location fee. I then need the invoicing portion of the contract to be able to base the invoice dates on the on-air dates from each flight, and invoice amounts on the calculated fees.

Do I just load up the contract layout with 13 or 14 "Flight" fields, "Start Date" fields, "End Date" fields and "Fee" fields? And then 13 or 14 "Invoice Date" fields that look at each of the "Start Date" fields and "Invoice Amt" fields that look at each of the "Fee" fields? If I do all that will I be able to use the slide function or something so I don't have a ton of blank space for 1 or 2 flight contracts? I briefly tried that but couldn't get all the information to pull up correctly. However, if I knew I was barking up the right tree I could spend more time and figure it out.

As far as the lookups go, it was the only way I could see to get a snapshot of the information as it was pulled from other tables at the time the contract was made. Is the auto-enter function you mention a script or something? Again, please forgive the ignorance; I'm teaching this to myself and am under an unfortunately tight deadline! Not the best design environment.

Thanks again for your help!!

[SlimJim]

I am not sure I have your basic structure but it sounds like:

Contracts Table with each contract having an ID (and presumably other details)

Flights Table with each Flight having an ID

A flight can only "belong" to one Contract - is that correct?

But a Contract can contain many flights.

On this basis you need a contractID field in your Flights table and then match the Contract table with the Flights table using the ContractID field in each table. (If you have not done this before then you use the Relationships graph in the Define Database dialog and use drag-n-drop between the two tables)

This relationship will allow you to view all the flights related to a contract in the contracts table using a portal. When you come to printing out contracts you do this from the Flights table (the new layout wizard will help you to do this)

[brwnis]

Almost. But actually each flight could belong to many contracts, and each contract could have many flights. This is where I get hung up. I was considering using a join table but I've never done that before - does anyone have any advice? Thanks again!!

---

Rach