4 Replies Latest reply on Aug 27, 2009 3:38 PM by etripoli

    case statement

    Sidhu

      Title

      case statement

      Post

      Hi Guys,

                I have 2 tables Students and Results.

      In Results Table i have 2 feilds, Supplementary and marks. In Students table i have a feild called final. 

       

      Now i need a calculation for the final feild, like

       

      Case(

       

      Supplementary = "";

       

      Sum( Results::marks);

       

      )

       

      For the above sum results i need if Sum(Results::marks) < 40;

       

      "Fail"

       

       Sum(Results::marks) > 40; 

       

      "Pass";

       

       Sum(Results::marks) > 80;

       

      "Distinction"

       

      So basically i need to satisfy more than one condition in case statement. is it possible.

       

      Thanks in advance. 

        • 1. Re: case statement
          Orlando
            

          Hi Sidhu,

           

          For the Case statement to calculate the mark try the following

           

          Case ( 

          Sum ( Results::Marks ) > 80 ; "Distinction" ;

          Sum ( Results::Marks ) > 40 ; "Pass" ;

          Sum ( Results::Marks ) < 40 ; "Fail" ;

          "" )

           

          No sure how you need the Supplementary field to affect the mark, but if you give me more details I can work it into the above for you.

           

          I hope this helps 




          • 2. Re: case statement
            LaRetta_1
              

            And, Sidhu, If the mark is exactly 40?  In Orlando's calc it would produce blank.  Is that what you want?  And if < 40 and > 80 and > 40 is accounted for, there would be no need for default result unless the poor 40 person is supposed to be there because ALL other results are accounted for.

             

            Might one of those 40's be 'less than or equal to' and skip the default result of blank, which isn't required in Case() anyway because default result produces null by itself. :smileyhappy:

            • 3. Re: case statement
              Sidhu
                

              Hi LaRetta,

                            Thanks for your time,

               The reason for supplementary feild is, if a student is making 2nd attempt, then the user will check supplementary feild in Results table. and i have another feild in Students table for supple, which will calculate the sum when there is value in supplementary in Results table.

               

              field Supple = 

              case(

              Not IsEmpty(Results::Supplementary);

              sum(Results::Marks)

               

              In Results table each student ID will have 3 records in i.e. (Test, LAB, Quiz) and i want to add all these marks. 

               

              So depending on the values in supplementary (Which is 1 or "") i want the feilds in Students to sum up the marks.

               

              So if the Student writes the exam twice, first time i want to sum up the values in marks feild, and put that value in Final field in Students,

               

              for the 2nd attempt i want to sum up the value in marks feild and put that value in Supple field in Students. 

               

              thanks,

              Sidhu 

              • 4. Re: case statement
                etripoli
                  

                You need a calculation field in the Results table, =Case ( Isempty ( Supplementary), marks )

                Then, sum this field in Students, applying your conditions:

                 

                Let ($grade = Sum ( Results::cn_marks ), Case ( $grade < 40, "Fail", $grade < 80, "Pass", "Distinction" ) )