This sounds like something that can be solved with a conditional value list.
You select the sail number in field 1 and then a drop down in field 2 lists only vessels in your boat table with that sail number. Once you've selected a vessel in field 2, you can display data from the Boat table by adding fields directly to the finisher layout. That sound like what you want here?
Here are some links that may help:
Forum tutorial: Custom Value List?
Thanks for the reply! That helped a bunch. However, I'm still stuck a bit. I was able to follow the steps in the knowledgebase article and set up the relationship. I'm able to select the sail number in field 1, then field 2 is correctly listing all the vessels with the sail number from field 1. The problem arrises from getting another field value from the Boat table after selecting the correct class. I'm trying to place the boat's performance rating in the finisher layout along with the sail number and boat class. If the sail number is unique, the correct rating is displayed in the layout. However, if, for example, there are 2 types of boats with the same sail number, the rating displayed in my finisher layout doesn't necessarily correspond to the boat class and sail number. It seems as though the rating that is showing up is simply the first rating of the boats identified by the sail number entry.
I think I answered my own question. I was able to modify the relationship between the Boat and Finisher table to ensure that both the Finisher Sail Number and Finisher Rating were related to the Boat Sail Number and Boat Rating.
Thanks again for the help!
You need a separate relationship so that you have one for your conditional value list and one on which to use so that you can add yoru rating.
The first thing you need is a way to reliably identify the boat record you want to select.
From your conditional value list setup, it would seem that Field 2 lists values from a field in the boat table that unqiuely identifies each boat. If so, you need to add a table occurrence of Boats so that you can add another relationship to it based on field 2:
Finshers::Field2 = SelectedBoat::Field2
To create SelectedBoat, you select Boat in Manage | Database | Relationships, then click the button with two green plus signs to create a new table occurrence of it. You can then double click this new occurrence box to open up a dialog box where you can rename Boat 2 to SelectedBoat if you want.
Then add the rating field from SelectedBoat to your layout.
Well, that didn't work. When I added the 2nd relationship between the tables, the boats' rating corrected themselves. But when I then try to add another finisher, I can select the sail number but "no values defined" comes up in the Class field.
I'll try your previous suggestion. Thanks for your input.
You need two relationships, one for the conditional value list and one to display the related data from the boats table after you have made your selections.
If "table occurrence" is a new term, you may want to read this thread:
You are awesome! That did the trick. Thanks again!
I'm back with another scenario. Some of the scoring I'm trying to do is simpler than the above -- recording finishes in an order only by sail number and class. I have the race table (race number, class, date, etc.), then a related finisher table (class, sail number, position) and finally the Boat table (class, sail number, email, etc.). I was trying to place a portal in the Finisher layout that showed all the records from the Boat table, and was hoping to set up triggers so that the user could select the portal row with a mouse click (or touch on FM Go) of the finishers in the order they finished. Selecting the portal row would perhaps automatically populate the "Finish position" field and trigger some conditional formatting to indicated the boat has been scored.
What I can't figure out is how to populate the portal rows automatically. I have a field in the Race table called "Class", and I'm trying to populate the portal with records from the Boat table based upon the Class designation.
Any help is much appreciated.
Don't know what table your "finisher" layout is based on, but you can use X instead of = in your relationship if you want all records in the boat table to appear in the portal.
Finisher::anyfield X Boats::AnyField