Entering ID based on other field Selection

[sethpomerantz]

Hi all

I am working on creating a project management database. Here is my simplified Table setup:

PROJECT                     CLIENT                       CLIENT_ID|PROJECT_ID

   -PROJECT_ID              -CLIENT_ID                  -CLIENT_ID

   -PROJECT NAME         -CLIENT NAME             -PROJECT_ID

When someone enters a new Project I want them to be able to select from a list of client names , and then have the correct Project_ID and Client_ID inserted into the Project_ID|Client_ID table. How would I go about doing this, or is this even the right kind of table setup ?

-Seth Pomerantz

[Chuck]

On the layout for the Project, show the CLIENT_ID field from the CLIENT_ID|PROJECT_ID table, and attach this field to a value list that gets all of the client IDs from the CLIENT table. Set up the relationship between PROJECT and CLIENT_ID|PROJECT_ID to allow the creation of related records.

Now, when you click on the related CLIENT_ID field and select a client, a new CLIENT_ID|PROJECT_ID record will be created.

Chuck

[justcosmic]

....a related question...

I am working in FMP 5, and use value lists from fields quite alot, but I find that it can be a bit "Bugy" at times...sometimes I use a field from a related file that may be a simple calc joining values (usually text)from 2 other fields

(to give a unique name for example). Sometimes values dissapear & then reappear, dont work properly etc. Also I have found I cant select the first letter(from keyboard) in the same way as you can from a custom value list(jump to entries beginning with the letter you type)....

...well, 24hrs later I think I have sussed out whats going on

...I have created another field in the "source" file, and a script to copy the contents of the "calculated" field. I then use this "copied" field as my value list, and viola! it works fine!....I think the lesson is:"dont use calc fields as value lists...."

....anyway, any input gratefully recieved...