Do you have one table for "members" and a different table for "memberships"? Linked by a primary key (unique identifier) defined in members?
If so, you can set up a "self join" between memberships and a new occurrence of the same table using this foreignkey field.
Memberships::_fk_MemberID = MembershipsSameMember::_fk_MemberID AND
Memberships::constCurrent = MembershipsSameMember::MembershipStatus
You create MembershipsSameMember on the relationships tab in Manage Database by selecting it and then clicking the duplicate button (two green plus signs). This does not duplicate the actual table. It just creates a new reference to your existing membership table. constExpired would be defined as a calculation field that returns "Current" so that the relationship only matches to Membership records with the same membership ID that are Current.
When you perform a find, you can then add an Omit type request on the end of your find that specifies an "*" in MembershipsSameMember::_fk_MemberID and your find will filter out these non-applicable membership records.
I think that may work. I don't have a "members" table, but "individuals" and "groups" each have a unique primary key that links to "memberships". I am guessing you would suggest I combine them both into the join table you're proposing... correct? I'm going to work on this and report back. Thanks!
A single table of records for all members would seem a better design, but what I am suggesting will still work as long as you have a unique ID to use in the self join.
Okay this will work, but I need some help on the calculation for constExpired. Do you have an example of the syntax/operators/functions I should use?
It has to return a value that exactly matches the value that marks a membership record as "Expired". If you use a value list where the text "expired" marks the membership as expired, then constExpired is just the same text in quotes. (And make sure to select "text" as the return type.)