I have several scenarios where I want the user to be able to select a nice friendly string from a drop down list, but I don't want to store the string they select in the record.... rather, I want to store a tiny little unique ID (that happens to establish a relationship to another table).

Since the drop down lists in these cases are long and I want to use auto-complete, this has proved difficult.

What I do now is have a temporary match field w/the drop down list. User makes the selection, and this establishes a relationship w/one TO.... call it Fetch.

Then an ID field (still in original table) does some kind of look up in Fetch to get the corresponding ID.

Then the temporary match field empties itself.

If it worked, the result would be that the record is now related by the ID and not by what the user selected in the dropdown.

Is all this making sense?

But I can't seem to get it to work. What happens is that when the match field clears itself, the ID field recalculates and erases itself also. It does this even if "Do not eval. if all ref. fields are empty" is checked, though I can't figure out why.

Is there a better way to let a user select a friendly value (via auto complete dropdown) but establish the relationship with a data-efficient value?

Is this thread applicable?

http://fmforums.com/forum/showtopic.php?tid/172810/

Since the look up is based on the temporary match field it will automatically refresh when you change the temporary match field. You need to use an auto-enter which does not change when you delete the contents of the temporary match field.

Case(

IsEmpty(Fetch::ID); ID;

Fetch::ID

)

This calculation does nothing to the ID field if there is an empty Fetch::ID and otherwise looks it up. This allows the user to change his/her mind over the choice of temporary match field.

Thanks for the feedback. I'll have to try this calculation... sounds like it will do the trick.

Thanks!

Yep, this works.

Is there some reason that IsEmpty(Field) works better than Field=""?

Seems like I tried the calculation you posted here only with Field="" but that didn't work....

Just curious.

Your code works perfectly. Solves many problems for me!

Spoke too soon. Actually, when I put the calculation in place to clear the temporary match field, it seems to do the same thing as before.

I must be doing something wrong, but I don't see what.