How to show related fields in another table.

[nareyramos]

Hi there,

I have these two tables, Projects and Clients, which are related. Now, I want to be able to see client info such as ClientPhone, ClientFax and ClientEmail in my Projects layout (bottom part in grey) when I select that specific client (ClientName) from a value list that gets its values from the ClientName field in the Clients Table. Don't know how to do that. Can anyone help? I am attaching the DB. Thanks.

 

NR

 

Test.fmp12

[comment]

See if this helps:
http://fmforums.com/topic/99275-displaying-fields-from-one-record-in-another-record/#comment-451716

--
P.S. You should not match by name. Names can change.  Use a meaningless ClientID instead.

[LaRetta]

Hi Narey,

Your current relationship is:

Projects::ProjectID = Clients::ProjectID

I would assume one Client can have many projects?  If so, your Projects table should hold the ClientID instead.  Check Comment's demo provided in his link and you will see how your relationship should be configured.  :-)

Your value list should be based upon (left pane) Clients::ClientID and then (right pane) specify 'also show values from' and select Clients::ClientName.  One further question:  Can a Project have more than a single Client?

Also, place the field Projects::ClientID on your Projects layout for selection with this above value list attached.  It will display the Client name for selection but insert the ClientID into the Projects::ClientID field.

One last thing ... you have project address lines.  I will make an assumption that you want those address lines to fill in with the Client's current address information?  If so, you can set them as auto-enter 'looked up value'.  The reason you wish to insert client data into the Project is for history purposes.  If the client moves, you want the project to reflect the Client's address at the time of the project.

There may be other situations where you always want the current 'parent' information to only display on a child layout.  In those instances, simply place your parent fields on your child layouts.

Edited March 9, 2016 by LaRetta