4 Replies Latest reply on Apr 19, 2012 12:55 AM by ChrisG

# Assign a numerical value to text

Dear FMP Guru's

I have a questionnaire where only fixed values can be entered using radio buttons. There are 5 values per field. Each value gets a score beginning with 1 and ending with 5.

What I want to do is convert all the answers to the correct numerical value and then add it all up. I am trying to avoid using a case statement as it becomes very cumbersome.

I was thinking of doing something like this:

A = 1; B = 2; C = 3; D = 4; E = 5

Then add up all the fields

Sum ( TextField1; TextField2; TextField3; TextField4; TextField5 )

If TextField1 = A; TextField2 = B; TextField3 = C; TextField4 = D; TextField5 = E

Is it possible to do something like this?

Thank you

Chris G

• ###### 1. Re: Assign a numerical value to text

The Code function was introduced in FileMaker 10. The result of Code("A") is 65, B is 66 etc. So for each question you can get the number you want with:

Code( yourField ) - 64

• ###### 2. Re: Assign a numerical value to text

You could add a calculation field that uses a case statement or Fitch's method and then total up the numeric fields.  Since the fields would be in the record this would be a stored value and could be indexed.

Once you have both the numeric and text values,  you can display or manipulate the data very easily.

HTH
Bruce

• ###### 3. Re: Assign a numerical value to text

You might consider displaying A through E on the radio button control but have 1 through 5 stored in the database field.

This would scale better as you add more questions. You won't have to add additional calculations for each field to translate the results. Each new response field will already have a number between 1 through 5.

This would be done by...

Populate this table with 5 records corresponding to A through E. (e.g. Numeric Value = 1, DisplayValue = A)

Create a new Value List, Answers with the settings.

Use value from field: Numeric Value in the table AnswerCodes.

Also display values from second field: DisplayValue.

Show values only from second field.

Ideally as you scale to more questions, you would put these questions and responses as entries in a separate table. Display them in List view. You would then Sum() the related values instead of summing individually named fields.

-Tom

• ###### 4. Re: Assign a numerical value to text

Thank you all so much for your input. You have given me some ideas to experiment with.

kind regards

Chris