Calculation comparing related records in same table

[jsword]

Hello!

I am trying to write a simple calculation that is proving to be more difficult than I thought.

I have a real estate database with the tables: properties, transaction, and people. A property can have multiple transactions associated with it, and each transaction has people attached to it (buyer, seller, broker). I am trying to flag all the properties (in the properties table) when a certain person is linked to any of the properties associated transactions, call this person "Bill". Taking it one step further, I would like to display a "C" when the Bill is associated with a transaction with the status of "Closed" and a "L" when Bill is associated with a transaction with the status of "On Market". That is easy enough and I have that portion working (in the transaction table). The problem is I want the "C" to be prioritized, meaning if Bill is associated with two or more of the associated transactions for one property, and one of those transactions status is "Closed", and the other is "On Market", I would like a "C" to be displayed in the property table. 

The way it is set up now, I can only seem to look at the most recent transaction (based on closing date) and display either a "C" or "L" depending on the status of the most recent transaction. Sometimes the transaction with the status "Closed" is more recent and vise versa.

How can I set this up so that the calculation looks at all related transaction records, determines if any are flagged with either a "C" or an "L", and if there are both, display the "C"?

Any help would be appreciated!

[comment]

I am not sure I fully understood your description, so this may or may not work for you: The expression =

FilterValues ( "Closed" ; List ( Transactions::Status ) )

will return "Closed" when at least one related transaction has a closed status, nothing (an empty string) otherwise. You could use this, for example, as =

Case (
not IsEmpty ( FilterValues ( "Closed" ; List ( Transactions::Status ) ) ) ; "C" ;
not IsEmpty ( FilterValues ( "On Market" ; List ( Transactions::Status ) ) ) ; "L"
)

 

 

[jsword]

It worked perfectly! Thank you. The FilterValues expression was what I was missing. Thank you for the help!