Nested Case calculations

[TMC09]

I need help with this calculation please. I need to designate which counselor the student has according to the students last name.

I have a calculation field that only looks at the left 2 letters of the last name. That is called "left last name".

Then I created a field and used this calculation... but it only is calculating the first part(grades 9-12) and not the second (grades 6-8)

 

Case(Grade Level="09"  or Grade Level="10"  or Grade Level="11" or Grade Level="12"  or Grade Level="9";

Case(
left last name>="A" and left last name<="E"; "Counselor 1";
left last name>="F" and left last name<="Le"; "Counselor 2";
left last name>="Li" and left last name<="Rh"; "Counselor 3";
left last name>="Ri" and left last name<="Z"; "Counselor 4";" "; 

Case(Grade Level="06"  or Grade Level="07"  or Grade Level="08" or Grade Level="6"  or Grade Level="7"  or Grade Level="8";

Case(left last name>="A" and left last name<"L"; "Counselor 5"; "Counselor 6"

))))

 

Any help would be appreciated.

[Steve Martino]

A couple of potential issues I see is the use of "06" and "6".  Are these text fields?  A little nervous about have fields like that.  Also, you shouldn't have field names with spaces, for many reasons...

Try this in data viewer (can't test without a file), you'll have to change left last name to a full reference of YourTable::left last name.  Same for Grade Level.

Let([
   @name=left last name;
   @grade=Case(Grade Level="09"  
               or Grade Level="10"  
               or Grade Level="11"
               or Grade Level="12"  
               or Grade Level="9"; 
               1;2);//determine which group
   @Con1=Case(@name ≥ "A" and @name ≤ "E"; "Counselor1";
              @name ≥ "F" and @name ≤ "Le"; "Counselor2";
              @name ≥ "Li" and @name ≤ "Rh"; "Counselor3";
              "Counselor4");
   @Con2=Case(@name ≥ "A" and @name < "L"; "Counselor 5";"Counselor 6");
   @Con=Case(@grade=1;@Con1;@Con2)
    ];

@Con

)
  

 Edit:  Fixed typos.

Edited July 16, 2018 by Steve Martino

[Steve Martino]

This way may be more accurate:

Let([
   @name=left last name;
   @grade=Case(Grade Level="09"  
               or Grade Level="10"  
               or Grade Level="11"
               or Grade Level="12"  
               or Grade Level="9"; 
               1;2);//determine which group
   @Con1=Case(@name ≥ "A" and @name ≤ "E"; "Counselor1";
              @name ≤ "Le"; "Counselor2";
              @name ≤ "Rh"; "Counselor3";
              "Counselor4");
   @Con2=Case(@name ≥ "A" and @name < "L"; "Counselor 5";"Counselor 6");
   @Con=Case(@grade=1;@Con1;@Con2)
    ];

@Con

)

[TMC09]

Thank you for your prompt reply.

The grade levels are stored as numbers. I removed the spaces from the field names. I tried your calculation and it worked!!! Thank you so much!!

[Steve Martino]

5 minutes ago, TMC09 said:

...The grade levels are stored as numbers...

Then why do you have both 9 and 09?

If they are just numbers, then:

Let([
   @name=left last name;
   @grade=Grade Level;
   @Con1=Case(@name ≥ "A" and @name ≤ "E"; "Counselor1";
              @name ≤ "Le"; "Counselor2";
              @name ≤ "Rh"; "Counselor3";
              "Counselor4");
   @Con2=Case(@name ≥ "A" and @name < "L"; "Counselor 5";"Counselor 6");
   @Con=Case(@grade>8;@Con1;@Con2)
    ];

@Con

)

[TMC09]

That was an error on my part. I removed it since then.

Thank you!