Auto Enter: Changing existing values

[HPage]

Hi there,

I have a database where I have a field for Gender. The field can be entered by selecting "Male", "Female" or "Unspecified" from a pop-up menu. I am importing data from an existing database (excel file) where the fields were only entered as M, F and U. How do I tell Filemaker to automatically change the letters to words? (Eg. "M" becomes "Male"). I am sure a simple calculation should do the trick, I tried using a Case-statement, but it didnt work, maybe my implementation was wrong.

Help would be greatly appreciated. Thanks! :

[Lee Smith]

Make this your calculation for the Auto Enter, and deselect "Do not replace existing value of field (if any)"

Case ( Gender = "M" ; "Male"; Gender = "F"; "Female"; Gender = "U"; "Unspecified")

HTH

Lee

[HPage]

Its not working. And when I click the pop up box and select a value manualy it only shows an empty field.

(Say there was a "M" in the box, and I select another value manually the box becomes empty.)

Any ideas of what I could be doing wrong?

Edit: I changed the field so that it is an Edit box instead of a popup menu, now it works, but it only changes if I select the edit box and press enter again. How can I get it to work so that the popup box field gets updated automatically?

Edit: Ok, I changed the field to an edit box, and then reimported all the data, everything worked fine. Thanks for the help. Is it impossible to make this work with a pop up field? If not, how do I do it?

Edited October 10, 2006 by Guest
New Info

[Lee Smith]

I see what you mean, change it to this:

Case (

Gender = "M" ; "Male"; Gender = "F"; "Female"; Gender = "U"; "Unspecified";

Gender = "Male" ; "Male"; Gender = "Female"; "Female"; Gender = "Unspecified"; "Unspecified")

Lee

[HPage]

Thanks a million! That works perfectly. I appreciate the quick responses.

[HPage]

I used that method and it works fine thanks, but is there no shorter way? I need to do it with much longer value lists now, so instead of:

Case (

Gender = "M" ; "Male"; Gender = "F"; "Female"; Gender = "U"; "Unspecified";

Gender = "Male" ; "Male"; Gender = "Female"; "Female"; Gender = "Unspecified"; "Unspecified")

is there easier no way to tell filemaker that if its not one of the M, F, U values it should just take the new/ keep the old existing value?

[BobWeaver]

You can use this formula:

Let([

pos=Position(ListA;Gender;1;1);

ValueNo=1+PatternCount(Left(ListA;pos);¶)];

Case(pos;GetValue(ListB;ValueNo);Gender))

where ListA and ListB are two globals with a list of the values separated by returns. ListA contains "M¶F¶U¶" and ListB contains "Male¶Female¶Unspecified¶". You can then extend these lists to contain as many items as you need.