1 Reply Latest reply on Sep 9, 2013 4:15 PM by philmodjunk

    Resolving Calculation Issues

    bluorchid

      Title

      Resolving Calculation Issues

      Post

           There are several fields in the questionnaire that have response values of "0-3" and the other fields are "yes/no" response.  For the fields having a numeric values,  0 or 1 are considered a "yes" response...  2 or 3 are considered a "no" response. For all the fields - Yes =1 and no = 0. 
            
           Additionally the fields with response values 0-3 are summed two ways:
           1) by the value entered
           2) by the corresponding yes/no value
            
           Here are examples of how I calculated the sums:
           Example one:
            
           FinancialScore:
           Sum (Financial22; Case ( Financial21  ≤ 1;1; Financial21 ≤ 3;0; "?"))
            
           Example two:
            
           EducEmploymentScore:
           Let ([
                Ed18 =  Case (EducEmployment18 > -1 and  EducEmployment18  ≤ 1;1; EducEmployment18 ≤ 3;0; "?");
                Ed19 =  Case (EducEmployment19 > -1 and  EducEmployment19  ≤ 1;1; EducEmployment19 ≤ 3;0; "?");
                Ed20 =  Case (EducEmployment20 > -1 and  EducEmployment20  ≤ 1;1; EducEmployment20 ≤ 3;0; "?")
           ];
           Sum (EducEmployment11;EducEmployment12;EducEmployment13;EducEmployment14;EducEmployment15;
           EducEmployment16;EducEmployment17;Ed18; Ed19; Ed20 )
           )
            
           The difficulties I am having are:
           1)  When a new record is opened, values are shown in the summed fields (FinancialScore & EducEmploymentScore) before any data is entered.
            
           2)  For the fields having initial response values 0-3, if an alpha character is entered by accident a value of 0 is entered into the field.
            
           Is there a way to resolve these without writing a script? If not, I am unsure how to do the script for issue number two.
            
           Appreciate any assistance

        • 1. Re: Resolving Calculation Issues
          philmodjunk

               1) and why is this an issue? But if you are using fields of type calculation and the check box ("do not evaluate if all the referenced fields are empty") is selected, you should not see any values in such fields until you enter data into a field and the record is committed.

               2) You should use a value list format that does not permit the entry of unacceptable values.

               And the large number of fields all in one record suggests that your basic design could be improved with the use of related tables where your questions are loaded in one table, one record for each question and your responses to them are loaded in a related table, one record for each answer.

               See this thread for a discussion of that design approach: Need aid on generating a report from a survey layout.