Digital-Clay Community Forum

Is it possible to implement the following:

I have 2 tables - Members and Memberships.

In the Memberships table there are two fields: (1) Member, linked to one Members; (2) Family Members, linked to many Members.

If the Membership Type is 'Family' the primary (paying) member is in the Member field, and the rest of the family members are in the Family Members list.

In the Members edit layout, I have a tab to show Memberships. For a single members, it will show his current Memberships record and the history of previous memberships.

What I want:
If the Member I am accessing is a Family member, but not the Primary member, (meaning that the search done in the previous scenario will fail since this member will not be found in any Memberships:Member fields) I want the system to lookup the Family Members field in Memberships, find the Membership records where this member is part of the family, and have those displayed in this member's Memberships tab.

Also, since we are talking about the whole history of the Memberships for this member, it is possible that some of them were Family and some are not, so either way, we have to look at both, and pull all results.

In programming talk:
- look for member in Memberships:Member
- get temp list of results
- look for member in Memberships:Family Members
- get list of results
- merge with first list
- display combined list

Thanks for the help,
Shai

What you are talking about is a union query which isn't currently possible.

An alternative is to use two filters and OR them together and also make them optional joins if needed. But this would mean entering the second filter value manually instead of it being 'unioned' automatically in the tab without user interaction. A future feature may help in this case but for now...

My recommended solution is to redesign. Three tables:
Subscription linked to Members via a Membership table
The Membership table has links to Subs and Members and a boolean field that marks whether he is primary.
This way you can use the same link to see all members in a single list and you can optionally filter on primary.
From a Member form, if you click on new you get a new Membership record where you can select an existing sub or add a new one
From a Sub form you can also open a Membership record and select an existing member and whether he is primary or not.
This is also a much more classic table structure that allows you to expand easily and add more types of memberships by adding more fields to the membership table.

Thanks.

I like the structure, but am struggling with the process.

When I create a new member, then click on New Membership, and from there I want to select existing Sub (your scenario) - how would I recognize the Sub as belonging to my primary holder?

One way I can think of is that when I mark a Member as primary in a Membership, an automation event sets the Name value for the linked subscription with the Member's name (a) is that implementainable (I like to make up words (b) is there a better way that I am not seeing here?

Using the primary member name is a good approach. This is easily done with a single Automation rule whenever a membership is added with primary=yes or whenever the primary flag is changed to yes.

I would also consider concatenating the date of the membership to the name to make it more unique.

What you use for the Name field to identify the subscription is obviously up to the users/system and what they need.