Some of you may have been following my other thread but since it wandered away from being relevant to the define fields forum, I decided to start a continuation thread here.

So...

Here is my first draft, it is not pretty, but it should show the direction I think I am trying to head.

I have some questions but I will first post what I have so that others may look at it before I ask my questions.

Again thanks to all that have helped me get this far.

contractMgt.zip

Ok, so on with the questions.

I have the three tables (adS, clientS, contractS) the clientS table consists of client info (name, address, phone), the adS table contains ad info (adsize, area, amount, etc) and the contractS table is (will be) a join table that joins the various other (not created yet) tables.

I am not really sure that I have my relationships set up correctly to do what I need?

In the clientS table I would like that to be the "main" file that the user will use to perform most functions (data entry, reports, invoicing, etc). The main layout should just display the client info and a basic list of contracts (with no detail) this sort of works but there is a possiblity that one contract number can appear mulitiple times and this is not what needs to happen. Clicking on the contract number will bring up the contract details (either on a separate layout or by opening the related table). This kind of works by using the contractS table in the portal, but the problem I am having is that the records are created in the adS table and they are not automatically created in the contractS table?

I guess what I need to know is how do I get the records to be automatically created across the two related files (adS and contractS)?

Hi Molly, i haven't looked at your sample, but your question sounds pretty clear. One thing i've done in a similar situation: Go to the client record. In a field on that layout (a global field, call it Temp01 or something) i enter/select the name of the ad. I then click a button which (A) creates an ad with that name and ( creates a contract linking the client with the ad. This button calls a script in clientS, which calls a script in adS (creating the ad), which in turn calls a script in contractS (creating the contract to join the client and the ad). The reason for the global temp field is so that you can access the entered ad name from the related file (contractS) through a universal-1 relationship.

HTH,

Jerry

It looks like your relational structure needs work. This is what I understood from your description of how it's supposed to work:

A Client can have many Contracts

A Contract can have many Ads

I also made these assumptions:

A Contract can only be linked to one Client

An Ad can only be linked to one Contract.

If this sounds right, then the relational structure would look something like the diagram below.

In practice, data entry for the Client and some related Contract info can be done in the Client file. You could click a button on the Contract portal row to pull up more details about that contract (there are a couple ways to do this), where you could then enter Ads through a portal.

Hope that helps.

Thanks Jerry, I had not even thought about doing it that way. I have done something very similar to that in another system I had worked on, but with all this relationship stuff I had not even considered that approach.

Thanks again for the help Ender.

Yes the cardinalities you have specified are correct.

The ERs look good as well, but here is where I am getting sort of lost, I can look at and understand the ER but actually building the FM solution to match the ER is sort of throwing me off.

Would the adID be an autoentry field (serial number, or concat key of contractID & clientID, or some combination thereof)?

The underlined fields are primary keys. To link those up use:

In Contract, have Client ID as a foreign key to Client.

In Ad, have Contract ID as a foreign key to Contract.

In Ad, have Client ID as an alternate key to Client.

The primary keys are usually auto-entered. The foreign keys can be entered automatically by record creation through the relationship, or manually entered in the table. The Client ID field in Ad will probably be a lookup from the Contract's Client ID.

Sounds about right. Thanks for setting me straight, I do appreciate the help.