7 Replies Latest reply on Aug 25, 2010 3:33 PM by philmodjunk

    need help with calc field



      need help with calc field


      Database #1 contains doctor name & assistant name.

      Database #2 is related based on assistant name & contains a field for assistant type.

      I am trying to create a calc field in Database #1 that enters assistant type based on the following conditions:

      -if assistant name is blank, enter "faculty only"

      -if assistant name has no match in Database #2,  enter "faculty only"

      -if assistatn name has a match in Database #2, enter the corresponding assistant type


      My first attempt at a formula worked fine but that was before I decided to include condition #2--

      If ( IsEmpty ( assistant name ) ; "faculty only" ; database#2::assistant type )


      This is what I was going to use to capture all 3 conditions but I'm not sure what logic to use for conditon #2 (i.e., a name that is not in the related file)

      Case ( IsEmpty ( assistant name ) ; "faculty only" ; ??? ; "faculty only"; database#2::assistant type )