Hi. Don't get up.

I have a questionnaire (in FM8) that has about 30 questions in it with each question assigned the same radio button'd value list (named Choice) in the same table (named Audit) "YES" = 3 pts.; "NO" = 1 pt., and "N/A" = 0 pts. Some of the questions are grouped together by subject so let's examine the simplest one: Training, which has only two questions which will be subtotaled. So, if the first question is answered "YES" (3 pts.) and the second questioned is answered "NO" (1 pt.), the Subtotal will be--you guessed it--4.

Here's the calc I've come up with for the Subtotal field:

Sum (

If (Twelve_Month_Training_Module = "N/A";0)&

If (Twelve_Month_Training_Module = "NO";1) &

If (Twelve_Month_Training_Module = "YES";3);

If (Ramp_Ops = "N/A";0) &

If (Ramp_Ops = "NO";1) &

If (Ramp_Ops = "YES";3

)

Now, this calculation works, but it looks...well...klunky. Is there a more elegant way of coding it? Seeing I have a lot of cutting-and-pasting of the calc for the rest of the questions, if there's a more elegant/faster way of coding this I'd appreciate the education.

Thanks in advance for your reply!

Edited November 27, 200619 yr by Guest
Removed space from "Module"

Well, ... This should really be done using a case statement

Secondly, you probably should have just put all your questions in a related table... i.e. Questions all belong to specific surveys...

If you refuse to change your relationships for whatever reason... I've written a small recursive custom function that will go through all the fields you specify and evaluate them.

(Don't worry, this was more for my sake than your's... I haven't really written one before but this one seems to work).

Have a look at the attached file.D

temp.zip

You can do it by creating tables for surveys, questions and answers separately. Answers should be entered like:

Field1(answer)-Field2(points)

Answer1-3 points

Answer2-2 points

Answer3-0 points

So when user chooses answer you can pull answer points through lookup and store it as question point(s). Then you perform search on specific survey and sum all survey question(s) point(s).

HTH

... or just use Sum(RelatedQuestions::PointField) where point field is a field in the question table that evaluates to Case(Question = Yes ; 3 ; Question = No ; 1) ?

Don't confuse the UI with what really happens. Set up your layout with the various fields so that the radio buttons just look like 'Yes','No' etc. but have them actually set as 3,1 and 0. You can do this by using a vertical format for the field and making it so narrow the numbers don't show and then put text next to the radio buttons. Your calc is then simply

Twelve_Month_Training_Module + Ramp_Ops.

Seriously though, it should be in a related structure

Wow, you all gave me quite a few avenues to explore--all of them interesting and, of course, ones I never would've thought of. I'll have to play with them and will follow up later with questions and/or results. Thank you VERY much for your time!

Why? The part of the problem described is just a simple flat file.