I am a new user to FileMaker, and am running 6.0 v2. I am trying to convert a limited range of text answers (strongly disagree, disagree, neutral, agree, strongly agree) into corresponding numberic values (1,2,3,4,5 respectively). I cannot figure out if there is a function that will allow me to assign numeric values to these limited text responses (TextToNum doesn't work for this), or devise another clever way to convert the data. Help would be so appreciated!

Thanks!

The unsavvy database manager Alex

Hi GuideMe,

Your calculation needs to be something like this:

Case(YourFieldHere = "Strongly Disagree", 1,

YourFieldHere = "Disagree", 2,

YourFieldHere = "Neutral", 3,

YourFieldHere = "Agree", 4,

YourFieldHere = "Strongly Agree", 5)

Note: your answers need to be exactly the same in both the Field and the Calculation above.

HTH

Lee

Lee,

Your code works!! Thank you a thousand times over! That will save me hours!! You're the best!

Alex

One more question: Do you know what the code would be to eventually convert the numeric values back into the original phrases? (1 back to Strongly Disagree, etc) I have to eventually convert the numbers back into the original format before submitting the database to my supervisors...

Just reverse the two components.

Case(YourFieldHere = 1 ,"Strongly Disagree",

YourFieldHere = 2, "Disagree",

YourFieldHere = 3, "Neutral",

YourFieldHere = 4, "Agree",

YourFieldHere = 5, "Strongly Agree")

However, in this case the results needs to be "Text"

HTH

Lee

I figured that it would be something close to that, but I was using the quotation marks improperly ((YourFieldHere = "2", Disagree) instead of (YourFieldHere = 2, "Disagree")). Again, it works! Thanks so much!! You are fabulous!

Alex

Hi Alex,

That's because text requires quotations in order to distinguish it as text.

At least you were seeing the basics of the calculation, and that is a good sign.

Lee

Not to detract from the solution outlined above, but FWIW, an alternative way to acheive the latter outcome would be:

Choose(YourFieldHere - 1, "Strongly Disagree", "Disagree", "Neutral", "Agree", "Strongly Agree")

Same result but a bit quicker and more compact...

Apologies for the intrusion on this thread but I'm blown away by the -1 and had to say something. It removes the unnecessary 0 place which I thought limited the function!! And I also didn't realize that Choose could hold more than three values. So, in reality, anything can be in the 'Field - 1' position, such as another calculation and Choose can be manipulated any way one wants! I thought it was quite limited and I am wrong! Choose is becoming a favorite of mine and even If() is back in my favors. Thank you.

Linda

And Choose() is quicker than Case()

You have to know how to write it first though.

Although Choose() is faster than Case(), as Ray (and then Ugo) indicated, I wanted to see how much faster.

I looped through 100,000 lineitem records and filled a number field (n) with 1, 2, 3 or 4. I then tested setting a text field (TEXT) which was empty to begin with. I used a global timer to accurately capture it. The Set Field[] calculations tested individually were:

Case( n=0; "Zero"; n=1; "One"; n=2; "Two"; n=3; "Three"; n=4; "Four" )

Choose( n; "Zero"; "One"; "Two"; "Three"; "Four" )

Choose() finished 21 seconds ahead of Case().

I recently found this tid-bit which explains the comparison beautifully ...

Imagine what the computer must do for these abstract calcs:

Case( x1, y1, x2, y2, x3, y3, x4, y4, ... )

1) Solve expression x1

a) It was true: solve and return y1

: It was false: Solve: Case( x2, y2, x3, y3, ... )

- Solve expression x2

A) It was true: solve and return y2

: It was false: Solve, Case( x3, y3, ... )

etc.

Choose( n, y0, y1, y2, y3, y4, y5, ... )

1) Solve n to a number

2) Solve and return y[n]

So any Choose() call will only evaluate a maximum of two expressions (in FM7), where the Case() may require many more, depending on what the data is.

One can not always use Choose() over Case() however. Now that in FM7 short-circuiting is possible, considering the order of evaluation (branch prediction) is also important. I completed a test on Case() and branch prediction here in case anyone is interested in really optimizing their execution times.

LaRetta