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:


      List of possible answers:

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


      Then add up all the fields

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


      Answer = 15


      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


          Then add them up.

          • 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.



            • 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...


              Add another table "AnswerCodes" to your database with fields: NumericValue and DisplayValue.

              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.


              Use value list Answers for your radio button.



              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.



              • 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