Hello,

I am a bit of a newbie so any help on this would be greatly appreciated as I am in a bit of a mess. Basically I have 2 tables, a clients table and a jobs table. The clients table has a portal to hold multiple contacts. What I am trying to do on the jobs table is look up a variety of data from the clients table via 2 drop down lists. Eg. when you start a new job, you have a drop down list for clients name which once you select the client name it looks up the related address etc then once the client is chosen you have another drop down list for contact name which only shows the relevant contacts for that client and again when selected it looks up that clients contact details. I have got the client one working but not the contact one and due to my lack of understanding I have got to the point where I am blindly trying things without knowing what I am doing.

So any help to point me in the right direction would be fantastic also any advice on whether this is the best way of doing it would be really helpful.

Thanks

James

Test.zip

I'd do it this way:

Test2.zip

Hi,

Thanks for doing that it is exactly what I was trying to do. Is it possible to briefly explain why you need the extra T.O please.

Also do you think this is a good solution to my problem? I did have 2 portals to select client and contact but it didn't feel quite right, I think the user needs to enter the field and make the choice as this seems more official, so to speak.

Thanks again

James

why you need the extra T.O

First you select a client for the job. This makes the job related to one client and ALL of that client's contacts. Now you want to select one contact out of this group. To make this contact - and only this contact - related to the job (so you can see this contact's details) you need the second relationship, using a second TO of Contacts.

Alternatively, in version 11 only, you could use a filtered portal to Contacts to show only the selected contact - but that has its own limitations.

I did have 2 portals to select client and contact but it didn't feel quite right

That's a purely design decision. You can select from a value list, from a portal or from a pop-up window in list view - none of these is inherently wrong or right. In any case, the relationships will be the same, since these are not about making the selection - they are needed to get the related data AFTER the selection has been made.

Thanks for the reply Comment, it makes perfect sense.

You have been a great help.

Thanks again

James