In the Groups table, create a calculation field, type text, as
IsEmpty ( Group_members::id /* primary key */ ) ;
List ( Group_members::id_user /* foreign key for user */ )
Create a new table occurrence (TO) of Users, and relate that to Groups via
Groups::cListOfMemberIIDs ≠ Users_notInThisGroup::primaryKey
Base your non-member portal on the Groups layout on the new TO.
There are several ways.
Using X relation on Groups between (copy of) Users (on any field) and portal filter
FilterValues ( List ( Group_members::<<key for user>> ) ; AllUsers::<<key for user>> ) = ""
This will become slow if Users or members in a group have huge records.
erlost, thanks, this almost works, but it shows all the users, even the ones that are already members.
user19752, thanks but this just shows one user repeated multiple times.
The tables and relevant fields are:
User_Group: pk, fk_user, fk_group
So User_Group is just a join table for the many to many relationship between Users and Groups.
On a form for Group I need a list of members and a list of non members for the selected group.
The answers given are almost there, but not quite (maybe I copied wrong) if anyone can point me to an example I can download and look at it would be appreciated.