I have, I hope, a relatively simple question but need a little help getting on the right track. I am importing multiple-choice tests into a FM database and will use the DB to score and report on test results. There are over 150 questions in each test and the answers are in the range of "A"-"E" for each question. I need to convert the A to a 0, B->1, C->2, D->3, and E->4 for each question in order to develop a cumulative numerical score. What's the most straightforward way to accomplish the lookup? Thanks, Dave

Define an other calculated field

score=Position("ABCDE", responseField,1,1)-1

Dj

Dj,

Thanks for the reply and for the nifty lookup technique. So just so I understand correctly, I would set up 170 fields to store each individual imported letter answer and then another 170 to store the calculated numerical result? That seems straightforward enough. I was thinking about a script that would iterate over the fields on import and convert them in place but I didn't see a way to loop through the fields generically without specifying each by name.

Unless I look at a repeating field to store the answers?

- Dave

Those 170 "fields" should be really 170 records each holding only one answer.

Now defining only one supplementary field ("score" with above calculation) and defining one auto relationship on the answer field (selfRel:answer-->answer); you will gain access (without any additional scripting) on score per type of answer thru the calculation Sum(selfRel;:score).

In alternative you could define an summary field total as sum of score field and then access individual answer type score by performing find on desired type.

In case you have to calculate the answers score on some additional criteria (like for different people that ran the test) you'll need simply to add those criteria in your auto relationship.

Dj