single result from multiple record entries

[Sholly]

Probably not a good topic name, but I couldn't think of anything more descriptive. Here is my story:

Every day I follow chimpanzees. I follow one from 7am to 12pm and another from 12pm to 7pm. During that time, I collect many samples of their urine. I test each sample for various hormones, one being LH (to check for ovulation).

I have a table for urine (Urine Samples) that includes information specific to each one of those samples (what color the urine is, whether there was LH hormone in the sample, etc). I also have a table that summarizes days (Follows). It has information that pertains to an entire follow (how many other chimps they met that day, whether they were pregnant, etc). Each record in the Follow table has a "follow ID" that is composed of the Date of the follow and the name of the chimp (BAC4/27/2007). "Follow ID"s in this table are unique identifiers. Each of the urine records also has a "follow ID#" composed of the same information. So, there are frequently more than one urine record with the same "follow ID" because they were taken on the same day. The 2 tables are related via "follow ID".

What I need to do is to group the urine records by "follow ID" and get one value as a result. I want to know whether a female was positive for LH hormone that day. She may have 3 samples, 1 positive and 2 negative. I need the field to do something like this:

group by follow id,

if (any)LH = Positive then "positive"

if (any)LH = Negative AND there are no positives, then "negative"

in (any)LH = N/A AND there are no positives AND there are no negatives, then "N/A"

I'm sure I've done this completely wrong, but alas, that is why I'm here.

sholly

[comment]

Try a calculation field in the Follows table along the lines of =

Let ( 

samples = List ( UrineSamples::LH ) 

;

Case (

not IsEmpty ( FilterValues ( "Positive" ; samples ) ) ; "Positive" ;

not IsEmpty ( FilterValues ( "Negative" ; samples ) ) ; "Negative" ; 

not IsEmpty ( FilterValues ( "N/A" ; samples ) ) ; "N/A" 

)

)

You didn't specify a result for the case where none of the above is true.

[Sholly]

Thanks for the reply.

In your solution, what does "samples" refer to?

Never thought about what to do if none of those parameters were true, but good point. Would need to leave it blank.

[comment]

"samples" is a variable I defined using the Let() function.

Never thought about what to do if none of those parameters were true, but good point. Would need to leave it blank.

Well, that's good because that's what it does.

[Sholly]

After fixing my screwed up relationships, it worked. Thanks a million! I should be able to apply this logic to several other fields I am trying to create.