Relating by ID, but display by name?

[purplemaji]

Is it possible to relate by an ID, but display by name when selecting the record to relate to? For example: Order related to Customer by CustomerID. When the user creates a new order I want them to be able to select from a drop down list the customer name that they are going to link the record to. But when they select the name, I would like it to store the ID related to the name, not the name.

In other languages/development environments, it is possible to set the display/bind properties of a drop down, so that I could set it to name/id, and name would display, but the value is the number. In filemaker, I have found that you can show two fields at a time, for instance I can show the id and the name in the drop down list, but when the user selects, the id or the name will get entered. I can't enter one and display the other.

Hopefully someone can understand what I'm getting at. If not, I can try to clarify. It seems like others would have wanted to do this, but it seems like every time it has come up, the solution I've seen is to just not bind on the id, but instead the name, but I want to use the id because it is more guaranteed to be unique than a first name/last name combo, etc.

Thanks in advance.

-pm

[Reed]

When you define value lists from a field value, you can also display a second field. That way you can see the corresponding name when choosing the ID.

If you want to then only see the name, not the ID, after the ID is stored, you can just place the related field on top of the ID field, but set the field behavior not to allow entry to the related field.

[RalphL]

I am going to sugest an alternate. I prefer starting in the customer table. Select the customer there and then create a new related order. I generally use lookups to fill in the customer information in the order record. This way they do not change if the customer info changes.

[purplemaji]

So there is no way to not display the ID at all when making the selection? I can get pretty close to the functionality I want from your suggestion (thanks!) but would still prefer it if the user didn't see the ID at any point in the process, as they may be confused as to why when they click in the field the name changes to a number with a strange drop down.

Any further clarification greatly appreciated.

Thank you!

-pm

[Matthew F]

I think that Ralph's solution should do what you want without displaying the CustomerID. By entering a customer name the CustomerID also entered via a lookup, but it does not need to be displayed on the layout. You'll need to be sure the customer names are unique to avoid mixups.

[MoonShadow]

By relating the Customer Name and setting the ID as a lookup from this relationship, you would create your value list based upon this new relationship (all values) and select the Customer Name only which will display in the popup. When the Customer Name is selected, the ID will fill itself in. Keep in mind that your standard key relationship should still be based upon ID to ID. The name relationship is simply for the popup and insertion of the ID.

[shortmatt]

How can you have 2 relationships seperate from one another (one being ID to ID, the other customer name to customer name)?

----

Matt Short

FM7, WinXP (not by choice)

[North2Alaska]

I have used a technique where you use two TOs to do exactly what you are asking. It is not my technique, but found at <http://www.filemakermagazine.com/Article527.html> I have found several techniques there that have more than paid for the small signup fee ($25 US or something like that.) Here is the summary from the article:

Synopsis: You may have seen this problem before. You want to provide a popup list and you also want to normalize your data. You make the jump to using a join file and numerical ID key values. Then you run across the big disappointment where your value list displays as

3 Cat

1 Dog

2 Lizard

Users don't make choices based on the numbers, but FileMaker insists on using them with normalized data. You just want to show Cat, Dog and Lizard without the numbers in the value list. Well, fix what FileMaker should have done properly in the first place. With this powerful FileMaker 7 technique you'll quickly know how.

[MoonShadow]

How can you have 2 relationships seperate from one another (one being ID to ID, the other customer name to customer name)?

You will find that, with FileMaker, you can have many relationships all for different things. You can even had a self-join (relationship) to gather information from within the same file/table. And you can even use the same ID in two different relationships referring to different fields within the same file.

For instance if you have an Employees file. You can re-use the same EmployeeID in two places - one for the EmployeeID and one for the ManagerID - although they are the same IDs. A new self relationship from the ManagerID to the EmployeeID will provide all employee information for your managers - you don't need a separate Manager file/table. And you can make and break relationships on the fly, depending upon your needs. There is no end to possibilities with FileMaker.

Confused? Good. Allow your mind to fly outside the box ... you will never be sorry.