Change your relationship to just:
Table::County = County Information::County
You should then remove the county code and region from the Table named table as it is redundant info that you don't need, but before you do, you'll need to review your layouts, scripts, calculations etc. and replace the table copies of these fields with the corresponding fields in County Information.
PS. And if "count code" should be an auto-entered serial number field defined in County Information, I'd use it instead of County in this relationship.
Phil said, "You should then remove the county code and region from the Table named table as it is redundant info that you don't need,"
... and instead just now place the County fields directly onto your table layout for display. Be sure to turn off entry to those fields. You can also just place merge fields from the County table instead of fields themselves.
Thank you Phil and LaRetta for the speedy replies. However, I didn't really understand what either of you meant, except I did change the relationship of the tables like Phil suggested. I spent the rest of the afternoon yesterday trying to figure that one out. During that time, I came up with another idea that I thought would be easier to implement. The funny part is I ran into trouble there too. Ha! So, let's try again. I'll explain a little more first.
I have 11 "Regions" and 254 "Counties" and 254 "County Codes" (the counties are broken down into regions with no overlap). Currently the data is manually input in to all three fields of the layout. So, I thought I would have the first input be a drop down box for the eleven "Regions" and create a relational value list that would limit the second input to a drop down box for the counties that belonged to the region selected by the user in the first input. Then, "County Code" would auto-populate based on the second input.
I followed the steps on pate 4-27 to 4-28 of the FM 11 Training Series, but could not get it to work. Can I get a relational value list with the current table structure? I hope I have explained that correctly. Reminder: I am new and don't know much about scripts.
You need a different relationship for your conditional value list.
You need to add this relationship:
Table::Region = CountiesByRegion::Region
To create CountiesByRegion, open Manage | Database | Relationships, select Counties by clicking it and click the button with two green plus signs. Then double click Counties 2 to open up a dialog box where you can change the name to CountiesByRegion. Then drag from Region to region to create the above relationship. (You have not created a new table, just a new Tutorial: What are Table Occurrences? of Counties so that you can create a new relationship between the two tables without affecting the original relationship.)
Now you can define your value list of counties to list values from CountiesByRegion; select "Include only related values starting from Table".
BTW, you can very probably use the tables tab to rename "Table" to be something more descriptive. Just make a back up copy first before you try it. There only a very few specialized features (likely not used in your database) which might "break" if you change a table or field name.
Thanks Phil. I'll give it a shot.
Thanks Phil! I got the relational value list to work, but I am struggling with the next step in getting the county codes to auto-populate based on the selection of the county from the relational value list. Is it better to use a script OnObjectExit from the relational value list or a calculation from the Manage | Database | Field | Options? Honestly, I couldn't get either one to work. Thanks in advance for your patience and cooperation.
No scripts or script triggers are needed. Just open the Table layout in layout mode and use the field tool to add the county code field from County Information to your layout. WHen you select a county, you'll see the count code appear in this field automatically. You can add any field you need from the County Information table to your layout and they will function in this same manner.
That doesn't really work. This is happening on input of a new record in a data entry layout. The only thing field three "County Code" could "read" is field one "Region" or two "County". Also, I've got another challenge with "Region" and "County" now. When inputting a new record and "Region" is selected from the drop down list, the proper counties display in "County" as it should, but if another region is selected, the original counties remain displayed. Therefore, if a mistake is made on input, the data entry person would have to delete the record and begin again. I switched some things around in the value list specify fields for value list window, but can only get it to work when I switch it back to the way you originally suggest. . I appreciate your help. Have a great weekend.
It works, I use this method all the time.
When you select a County in the County field, this establishes a link to a matching record in the County Information table and any fields from County Information that you have added to your layout will automatically display the correct data from the matching record in that table.
From what you describe, something is set up incorrectly on your layout. Let's step through the basics and see where things went wrong:
- The layout you are working on should list "Table" in Show Records From in Layout Setup...
- The Region field on this layout should be from "Table". Double click it while in layout mode and confirm that "Table" is listed in the top of the Specify Fields dialog that pops up when you do this.
- The County field should also be from "Table".
- The County field should be formatted as a drop down list or a pop up menu and it should use your conditional value list of counties for the specified Region.
With that all setup, you can select a region in the region field and then when you pull down/pop up the value list in County, you should only see the list of counties from the selected Region. If you select a new region and re-enter the county value list, it should change to list the counties from that specified region, but please note that this will not change a previously selected county. Correcting this does not require deleting any records, you simply need to select a different county from the new list that appears when you pull down or pop up the list. (And a validation rule can be set up to pop up an error message if the user selects a county and then changes the Region without also selecting a different county.)
Thanks Phil. I double checked everything and I have it set up the way you described, but I still have those two issues. I did notice something that's behaving a little weird. When the region field is selected from the drop down list, then the county is selected from a drop down list. This works just fine, but the counties are displayed in the drop down list along with their respective regions, but when input into the field, only the county is listed. Could there be something going on with the tables that is causing the problem. I am unable to get a screen shot in w/o starting a new thread. If you think I should create a new thread with a screen shot for you, let me know. Thanks again, Emmett
You can upload screen shots to a file sharing site and then post the link to them here in you next reply. You might even want to upload a copy of your file instead of the screen shots.
"This works just fine, but the counties are displayed in the drop down list along with their respective regions, but when input into the field, only the county is listed."
This sounds like a drop down list where the County is displayed in column 1 and the region is listed in column 2. If so, this is expected behavior.
If you are first selecting the region in one field and then using a conditional value list to select from the counties for that region, I don't see why you would set up a value list like this nor would it matter that the field doesn't show the region as this would be a value selected in the region field before you ever selected from the County field's drop down.
If you are not using a conditional value list, but just a list of all counties, grouped by Region in that second column of the value list, you can add the Region field from County Information to display the region name once you exit the county field.
Everything looks correctly set up and looks like it is working correctly.
Since you have already selected the region, why do you want to display the region in the second column of the County value list? That seems redundant here and you can remove this column from your value list and it will still work correctly here.
I am glad to hear it appears to be set up correctly. But I do not want the region codes to display again on the drop down list, I mentioned them because it was not expected and I thought it may be related to the underlying issue. There is still a problem with making changes to "Region" from the drop down list and that's is with keeping the mouse over the drop down list and clicking on say 004, but then changing your selection immediate to 005, the orginal counties from 004 remain displayed, and "County Code" doesn't display from the related table. I've gone through it several times and made some different selections, but I always end up back at this spot. Maybe I checked or unchecked the wrong selection somewhere?