Ok, so my problem is this.

I am trying to create a simple relationship within a table so that I can look up multiple values. My database is full of plant scientific names, and the problem I am encountering comes up when I reach a field that does not have a subtaxon name (empty field).

So my fields are:

Family

Genus

species

subtaxon

nativity

My goal is to automatically produce a value for nativity once the plant name has been entered into the record. I currently have my base table linked so that it breaks down each level hierarchally.

If I create the relationship through the subtaxon, then empty fields will not produce a value when I attempt to 'lookup' the Nativity.

I am still a beginner, when it comes to forming calculations, so I'm not sure if that would help in this situation.

I'm sorry if this is a little unclear, but any assistance would be much appreciated. Thank you in advance.

Mike

I don't understand the science of this but it seems that what you are saying is that given a name you choose/lookup? a family, a genus, a species, a subtaxon and a nativity. However, there are nativity values with no subtaxon. The first notion that drops into my head - frequently dumb - is not to have empty fields in subtaxon. So if there is no subtaxon then write "nil" into the field and once you have the list that you want remove the "nil"s.

Ok, so I figured out how to do it. I know my initial explanation wasn't clear, but thank you to those of you who looked at it.

I ended up creating two different relationships within the table. One only linking two of the fields, and the other linking all three.

My final calculation was this:

If (IsEmpty ( JOTR_Species_List 5::Subtaxon );

Lookup ( JOTR_Species_List 5::Nativity );

Lookup ( JOTR_Species_List 7::Nativity ) )

thanks again.