AnsweredAssumed Answered

need help with calc field

Question asked by denno on Aug 25, 2010
Latest reply on Aug 25, 2010 by philmodjunk


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 )