View of the tables in question one and "finance terms" also shows what my value list tables look like
Q1. A pop up menu is the simplest way to get a field formatted with a value list setup to display names but enter ID numbers to show the name once you exit the field. It looks fine to me, but if you want to use other options this is certainly possible. Another option is to use a drop down list instead of a pop up menu. You can take the name or description field from FinanceTerms that serves as "field 2" in your value list and put it on top of the drop down list formatted field. Give it an opaque fill color to hide the drop down list field and use Behavior in the Inspector's data tab to block access to this field when in browse mode. When you click on or tab into the drop down list field, it pops in front of the name/description field and deploys your value list. When you select a value in the list, it disappears back behind the name/description field and that field now shows the corresponding name or description--matching the value selected in the value list.
You might also be able to adapt the method used in this demo file:
FileMaker 12 or newer users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7
I have never, ever seen a problem occur due to setting up a table with a primary key that later turned out to not be needed for that table. I have often seen issues occur when a table did not have a primary key and needed one. On the other hand, adding a Primary key to a table that did not originally have one is pretty simple in FileMaker.
It's important to understand what makes the ideal primary key.
The ideal primary key is:
- Always unique in the table where it is defined.
- Never, ever changed once assigned to a new record in the table where the key is defined.
- Never includes any additional "encoded meaning" beyond that "unique identification" in 1.
- Is implemented in as simple and "bullet proof" a fashion as possible.
That's why most properly set up primary keys in FileMaker databases are auto-entered serial numbers and a smaller subset use Get ( UUID ).
But keep in mind that this is for the main "back bone" relationships that define the structure and function of your database. You may find yourself using, such as illustrated in my demo file, special purpose match fields that do not use actual primary keys to get the job done. Whenever you find yourself thinking about using a field in a relationship that cannot meet the definition of a primary key, ask yourself: "What might go wrong if I later have to change the value of this field?" Match fields defined in relationships used to find records, for example will most likely not have an issue with that and using a name field for that special purpose use will then be OK to use.
Thank You - Very Helpful as always.
When I make my pop up lists for my info on my layouts... should I make it populate the primary key and show the related type... or should i just make it populate the type. Do i really need it to populate the primary key? I really can't think of why that would be a big deal for what I am doing.
If you don't either set up the value list to enter the Primary key or set up the Primary Key to be looked up from the related table (the trick used in the demo file), then you are not using the Primary Key to link records in one table occurrence to another. Essentially, you'll be using your name field to link records--with all the possible problems that you avoid by using an auto-entered serial number as your primary key.
Note: Are there times that I "break the rules" and use a name field in place of an ID number? Yes. But I do so with full understanding of the potential consequences and with systems set in place to handle them and thus protect database integrity. Typically, I might choose to use a name field when the potential list of name values is very small.
thanks - you are on the ball as always