How to set value in field #2 depended from value in field#1

Posted on 2014-10-30
Last Modified: 2016-07-17
My example:
Table 1

Table 2
::IDFk (from Table 1::ID)
::NameFk (from Table 1::Name)

I want to set values ::IDFk and ::NameFk in Table 2, but I want just to choose from PopUp list box value (list of Table 1::Name) and value in field Table 2::IDFk should be set up by itself depending on value I will choose from PopUp list.
Question by:Marcin Dudzinski
  • 2
LVL 25

Accepted Solution

Will Loving earned 500 total points
ID: 40415073
First, set NameFK to Auto-Enter the Name value from Table 1 using on a relationship from Table 2 to Table 1 based on relating Table 2::IDFk to Table 1::ID

Then create a Value List based on Table 1, selecting in the Value List dialog, the Table 1 ID in the left column and the Name value in the right column. Then click the option to "Show values only from second field". Set this as a popup menu on the IDFk field in your Table 2 layout. This will give you a popup menu which displays only the Name but when a name is selected, it populates the field with the ID. Once the IDFk field is populated, the Auto-enter will populate the NameFK field with the related name.

Here's an example: Specify Fiels for Value List
Now that answers your question, but I have a few comments:

1. Unless you have a need to actually copy the Name to Table 2, don't. Just use the relationships based on ID to display the name on the layout or wherever. Once the ID to ID relationship is established you can use it to display any fields in Table 1.

2. Use the same field name for the ID in both tables. And in general, avoid spelling variations in fields that have the same content. You'll find that distinguishing which field is the foreign one is unnecessary and the different names can confuse things and make it harder to determine that you've got the right fields in the relationship. Just use something like CustomerID or ContactID, or if you want to Identify is as a key,  you can use the convention of adding a prefix like "zk_" for "zk_ContactID". The "k" identifies it as a Key field and the "z" forces it to the bottom of the field list so that all keys are grouped. As your relationships get more complex and you have more keys, you'll find it much easier to keep track of things if name like things in different tables the same.
LVL 25

Expert Comment

by:Will Loving
ID: 41714766
Complete answer provided including screen shot along with some free educational advice about recommended database building practices.

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question