5 Replies Latest reply on Oct 9, 2016 7:15 PM by MarcDolley

    Group Members Removal

    MarcDolley

      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).

       

      Marc

        • 1. Re: Group Members Removal
          philmodjunk

          What you have is a many to many relationship. There are two ways to do that in FileMaker, a return separated list and a join table. Each can be just as easy for the user to use as the other, the interface used to control what's in them can even look much the same in many cases, but Join tables are much more flexible to work with for something like this as you can use a relationship with Go To Related Record or a scripted find to find a record or records in the join table and delete them much more easily than finding and stripping data out of a return separated list.

           

          That said, here is one calculation that will strip all instances of a specified ID out of a single return separated list:

           

          Let ( [ TheList = ¶ & YourListFieldHere & ¶ ;

                    newlist = Substitute ( TheList ; ¶ & ValueToRemoveHere & ¶ ; ¶ )] ;

                    Middle ( newlist ; 2 ; Length ( newList ) - 2 )

                )

           

          The extra returns (¶) make sure that if you need to remove a value such as "1" from the list, it doesn't remove it from values such as 12, 13, 21, 11 etc.

          • 2. Re: Group Members Removal
            MarcDolley

            I get where you're coming from Phil. Unfortunately, the important data resides in the wrong table for this to work. One comms record has to be viewed by multiple staff as members of groups. They are all viewing and updating the same record, not each one having their own record. It's an unusual situation which I haven't encountered in my 20+ years as a developer.

             

            The calculation you supplied will strip 'all' instances of a specified ID from the field. I only need to strip the first instance of the ID and leave subsequent instances in the field.

             

            Marc

             

            EDIT: Thinking about this further, it's not actually a many to many relationship. It's a whole set of Many to One relationships. Many staff records relate to one comms record and a different many staff records relate to a different comms record and so on.

            • 3. Re: Group Members Removal
              beverly

              Position() uses the instance of a match. That with Replace() may be all you need.

               

              Sent from miPhone

              • 4. Re: Group Members Removal
                philmodjunk

                If you are using the field in a relationship to match records, you have to remove all instances of a given value before you will get a change in how your data is linked.

                 

                As I read your description, you are linking one comm record to many staff records and also many staff records to a given comm record. If I have that correct, this is the very definition of a many to many relationship.

                • 5. Re: Group Members Removal
                  MarcDolley

                  philmodjunk wrote:

                  If you are using the field in a relationship to match records, you have to remove all instances of a given value before you will get a change in how your data is linked.

                  Not exactly. Let me give you a simplified example.

                   

                  Group 1 consists of PersonA, PersonB and PersonC

                  Group 2 consists of PersonB, PersonC and PersonD

                   

                  Both of these groups are assigned to a particular comms record. At some point, someone decides that the comms record no longer relates to Group 1. So I need to remove PersonA but leave Persons B and C because they are part of Group 2. Confusing I know.

                   

                  philmodjunk wrote:

                  As I read your description, you are linking one comm record to many staff records and also many staff records to a given comm record. If I have that correct, this is the very definition of a many to many relationship.

                  I understand that on paper, it appears to be a simple many to many relationship. However, in a practical sense, it doesn't work that way. Having said that, you did manage to put me on the right track. I created a new table sitting between the comms and groups tables called commgroups. It just contains the primary keys for the comms and groups records.

                   

                  This has simplified both the creation and deletion processes. Creating simply involves creating a new record in the table with the relevant keys. Deletion involves deleting the related record and then using a simple SetField() to replace the Staff ID's in the list in the comms record.

                   

                  Thanks for your assistance and for being a sounding board as I worked through this.

                   

                  Marc