I have a tricky one that I could use some help with. Structure wise, I have a Staff table and a Comms (Activity Log) table. There's a heap of other tables, but I'll only mention the relevant ones here. There is also a Groups table as well as Group Members. A staff member can be a member of multiple groups (eg Admin, Finance etc.).
A comms record can be assigned to a staff member, a group or multiple groups. I have that part mostly working. From within a comms record a person can choose to assign the record to a staff member, which they can choose from a drop down list of staff or they can choose group(s). If they choose groups, they can see a portal showing all groups (currently six but likely to expand). When they click the Assign button on the portal row, it adds a list of the Staff ID's for that group into a text field called Staff ID List. If they click Assign on a different portal row, it appends a list of the Staff ID's for that group to the same text field. It is now highly likely that some staff ID's will exist multiple times in the list. That's not really important.
A portal from the Staff table to the Comms table based on Staff ID to Staff ID List will still show the related comms records because FileMaker's cool like that. That's all good. It's a simple process for the end user and the data relates correctly. Here's where my problem comes in. If the user (or someone else) decides that a particular comms record shouldn't actually be assigned to one or more of the groups, I have to have the ability to remove the group and all of it's members from the list of Staff ID's. Remember that a person belongs to multiple groups and removing that group from the record might still mean that their Staff ID has to remain in the list as part of another group. I can't simply use a substitute or similar function because it would strip out all occurrences of that staff ID.
I did consider stripping out the contents of the staff ID list field and starting again with the remaining selected groups, but I worry about the effect that will have on other users eg comms record suddenly disappears from their list then reappears. I'm open to suggestions as to how to solve this because it's bogging me down. I'd even be happy to ditch the whole setup and start again. This part isn't live (yet).