Update fields based on value list selection

[cuttysark]

Hi,

I'm pretty new to FM, so I hope this is the right forum.

I have 3 tables in my database: Jobs, Companies, and PIC (Person in charge). Each Job is performed by one Company. Each Company has several PIC's.

Therefore, I have related Jobs and Companies through the "Company Name". Companies and PICs are related through the "Company ID#".

The user looking at the Jobs database can select a Company for the Job by pulling down a drop-down list with a value list based on the "Company Name" field. The appropriate information fields about the company then display as static fields in a portal.

However, I want the user to also be able to select one of the the PICs from that Company to deal with the specific Job. I tried making a value list drop-down menu showing related values (the PIC's surname) - the list shows up fine, but when I select a PIC's surname, all it does is change the name of the first PIC attached to that Company to whatever surname I selected.

What I want it to do is update the display of other fields to show the details of the 2nd, 3rd, 4th etc PIC, based on the surname selected in the drop-down menu.

Can anyone offer a suggestion on how to do this?

Many, many thanks in advance.

Cuttysark

[cuttysark]

OK, I managed to figure it out myself.

I needed to make another occurence of the PIC table, and added to the PIC table a unique ID number as a primary key. I then added the same ID number to the Company table as a foreign key, and created a relationship between the two.

All is working fine now!