Relationship question

[James Patrick]

Hello,

I have a relationship problem, I have been playing around with it but just can´t get it to work.

I have 3 tables contacts, clients and jobs. As a client can have more than one employee, I store the employee details (name, telephone numbers) in the contacts table and have a relationship set up through an id number. This info is viewed through a portal on the client table. The client table also hold the company details (name, address), this all works fine.

What I am trying to do is on the jobs table, I have a drop down list, which is a value list holding the id number and company name, when you select the company name it automatically fills in the address from the clients table. I have this working by setting upa relationship through id number to company name. But I can´t get the second bit working which is I want a second drop down list,in the jobs table, called contact name which looksup from contacts, only the people from that company you have previously chosen, and when selected fills in the telephone details in other fields.

I just can´t work out how to set this relationship up, it´s a bit above me, any help would be greatly appreciated.

Thanks

[bcooney]

Like this?

CondValueList.fp7.zip

[James Patrick]

Hello,

Thanks for your help and taking time out to make the example, this is just what I am looking for, and a slightly different approach to how I was thinking. One thing I want to do is have telephone numbers stored as well as client name, I have added it to the portal and to the jobs and contacts table, but when you select the contact name from the drop down list it only inputs the first number, do I have to set up another relationship?

I have attached the updated file, thanks for your help I am finding this really difficult.

CondValueList_Copy.zip

[comment]

Try it this way.

CondValueList_Copy.fp7.zip

[James Patrick]

Thankyou, that is exactly what I was trying to do, I knew there would be a simple answer.

Thanks again for taking time out to help

James

[James Patrick]

Hello again,

As usual I have solved one problem only to create another. The solution from the earlier post works fantastically but the problem I have is that on the company name drop down I need the field to be an auto complete field due to the fact that the list is very long. The problem is the field is not giving me the option, I think because it is linked by id.

I have tried all the ways I can think of with no luck. Does anyone know if this is possible or do I need a re think?

Thanks

James

[comment]

Maybe something like this would suit you:

CondValueList_Copy.fp7.zip

[James Patrick]

Hi Comment,

Thanks for the reply it was much appreciated. Going through these replies has helped me to get a much better understanding of relationships, which I am finding quite difficult.

Thanks again

James