I often want to lookup related entities based upon search criteria. Currently, I want to lookup the Event records associated with a contact only if they attended the event (as opposed to just registering).
One way to do that would be to have two Event collections in Contact - one for “registered events” and a separate one for “attended events”.
You could also create a Formula field with
filter() to make such a Lookup, as long as all of the required filter criteria are members of the Contact and Event DBs.
This is my current solution!
However, now that I’m looking into smart folders for report creation, I find myself looking to eliminate these in-between fields to simplify entity relationships.
I wouldn’t hold your breath for native support of auto-linked relationships that can have a more-complex definition than simple field equality.
Will the Formula approach not work? That gives you a filtered version of the existing “complete” collection. But I don’t know what you’re trying to do in this report…