2 Replies Latest reply on Feb 12, 2014 6:51 AM by TaraGray

# Calculation problems - searching content of a field in IF statement

### Title

Calculation problems - searching content of a field in IF statement

### Post

In my database, I have a table for student info and a table for their registrations.  There can be many registrations for one student. I have a layout in the Student table in which I have created a field that needs to show one value for the student (A for active, or R for Retired) depending on if the student has any active registrations, or none.

I have tried doing the calculation:

IF (RegistrationStatusFieldName = ''A''; ''A''; ''R'')

This shows a value of either A or R in the destination field, but it seems to arbitrarily choose ONE of the Registrations to look at instead of looking at all the Registrations for the given student and determining if any of them are A (then should show value A).  If even one registration for that student is A, then the value shown should be A.  If ALL the registrations are R, then the value shown should be R).

Sorry if my explanation isn't clear... Can anyone help please?  How do I tell it to look at ALL the registrations for the student before determining the value?

Thank you SOOOOO much!

• ###### 1. Re: Calculation problems - searching content of a field in IF statement

When you set up a calculation to refer to data in a related table, it refers only to fields in the first related record. The data in all other related records will be ignored.

To get the results that you want, you need to use a function that can pull data from all related records. Both the List and ExecuteSQL functions can do that.

If ( IsEmpty ( FilterValues ( List ( Registrations::StatusFieldName ) ; "A" ) ) ; "R" ; "A" )

• ###### 2. Re: Calculation problems - searching content of a field in IF statement

Thank you!  Worked like a charm!  I've been trying to figure this out for days; it's been haunting my sleep.