I have a company record which has a sales exec field. I want to force users to select an appropriate sales exec from a value list (a pull down menu). I have implemented the value list and pull down menu, but I don't want to store the person's actual name in the company table. I want to store the ID attached to the name from the sales exec table. I would also like the sales exec field in the company table to display the sales exec name based on the id. So far, the only thing I have been able to accomplish is storing the person's name. But this could pose problems down the road if someone's name is misspelled etc. Can any one point in the appropriate diretion to solve this issue?

Patrick

Your thinking is perfect, always store IDs not names.

So, add _kF_SalesExec to the Company Table. (This is the suggested standard field name convention for a foreign key).

Then put this field on your Company record. Set it to a popup and assign the Value List "SalesExec" .

The Value List "SalesExec" uses two fields: _kP_SalesExec and SalesExecName. Set the value list to only display the second field, "SalesExecName."

Now, when the user enters the _kF_SalesExecID field via the popup, they are setting the SalesExec ID, but they are seeing the SalesExec Name.

Thanks for the response. Is the option to display a second field an 8.0 feature? Because when I set up the value list, the only option it gives me is to display _kP_SalesExec and then it allows me to optionally display the second field SalesExecName.

Patrick

Unfortunatley yes, you can only "hide" the real values in FM 8+.

.. Assuming you will only ever have one sales Exec with any particular name, which in most cases is a fair assumption if you use the first and last names, you could always have an exec name and an ID look up field over a relationship based on the exec name..