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 )