4 Replies Latest reply on Jul 7, 2015 9:04 AM by weedonpaul

    How to separate lists

    weedonpaul

      Title

      How to separate lists

      Post

      I have a list of over 12000 contacts, over 2000 of those belong to one or more lists. In the past I had a field with a list of all the lists they belong to. For example contact1 may belong to 5 lists so in that field there will be the name of all 5 lists. I have learnt that this is not a good way to manage this. so I have redone this separate tables. So that I can change the records to the new system I want to extract the contact ID and the list field into excel, but is there away to split the list so that instead of 1 contact ID having 5 items it has the same contact ID 5 times with each item on the list separated

      I realise that explaining this is like swimming in syrup, but hopefully you can follow my problem 

        • 1. Re: How to separate lists
          philmodjunk

          Don't see why you want to export to Excel for this.

          I think that you originally used a return separated list of values in a text field as your way to list which which groups a given contact might be a member of. This can work quite well but is not as flexible as setting up a "many to many" relationship such as:

          Contacts-----<ListMember>----Lists

           A looping script can pull the data from the original text field one value at a time and generate both the records in LIsts and in ListMember without need for exporting to Excel.

          • 2. Re: How to separate lists
            weedonpaul

            The many to many relationship is exactly how i want it now, so feeling chuffed that I'm on the right lines

            What would such a script look like?

             

            • 3. Re: How to separate lists
              philmodjunk

              First, more relationship detail:

              Contacts-----<ListMember>----Lists

              Contacts::__pkContactID = ListMember::_fkContactID
              Lists::__pkListID = ListMember::_fkListID

              For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              Note that none of these "ID" fields store a contact name nor do they store list names. They should be either auto-entered serial numbers or text fields with auto-entered Get ( UUID ) values. For this script and many interface designs, "allow creation..." needs to be enabled for ListMember in the contacts to ListMember relationship.

              To the above we add a new Tutorial: What are Table Occurrences? and relationship just for the purpose of moving this data. You can remove this TO when the job is done:

              Contacts::TempTextField = Lists|Create::ListName

              Lists|Create is the name of a second occurrence of Lists and "allow creation..." must also be enabled for it in this relationship.

              Now we can script:

              The script needs two loops, one "nested" inside the other. The outer loop loops through the records in contacts, the inner loops through the values in your text field. I am assuming two things here: 1) Your text field that lists your lists for a given contact is named contacts::ListMembers. 2) This field lists the names of lists and not ID numbers

              #Starting from your Contacts layout...
              Show All Records
              Go to Record/Request/Page [First]
              Loop
                  Set Variable [$K ; Value: 1 ]
                  Loop
                       Exit Loop If [ $K > ValueCount ( Contacts::ListMembers) ]
                       Set Field [ Contacts::TempTextField ; GetValue ( Contacts::ListMembers ; $K ) ]
                       #Create a new record in Lists if one with this name does not already exist.
                       Set Field [Lists|Create::ListName ; Contacts::TempTextField ]
                       Commit Records
                       #Create linking record in ListMember
                       Set Variable [ $ListID ; value: Lists|Create::__pkListID ]
                       Set Variable [$ContactID ; value: Contacts::__pkContactID ]
                       Go to layout ["listmember" (ListMember) ]
                       New Record/Request
                       Set Field [ ListMember::_fkContactID ; $ContactID ]
                       Set Field [ListMember::_fkListId ; $ListID ]
                       Go to Layout [ original layout]
                       Set Variable [ $K ; value: $K + 1 ]
                 End Loop
                 Go to Record/Request/Page [next ; Exit after Last ]
              End Loop

              • 4. Re: How to separate lists
                weedonpaul

                That is awesome I have to do the kids dinner now but I'll get that working this evening. BTW your assumptions were correct.