1 Reply Latest reply on Feb 25, 2015 7:25 AM by philmodjunk

    Filtering for unique values in a fixed number randomized list to get one of each



      Filtering for unique values in a fixed number randomized list to get one of each






      I have a group of 40 people who will meet with another member in that same group based on a random selection.

      I created two tables with the same 40 people alphabetized and the same serial number ID from 0 to 39.

      A looping Random script function on a List view layout creates the equivalent of:

      John Doe                meets with        Peter Pepper

      Peter Pepper          meets with         John Doe   etc.

      Until the number of meetings is created.

      What I need to do and haven't figured out is how do I create 40 meetings but each record that will show in the list view only has one each of the member ID or numbers 0 thru 39, since they are randomized.

      I have the feeling that I need to do many, many loops and eliminate duplicates until there are only one of each.  I just don't know how to do the process to keep it going until there is actually one of each in each column of members.



        • 1. Re: Filtering for unique values in a fixed number randomized list to get one of each

          It would seem to me that if you add a number field and use the Random function to put a random number inside this field (Replace Field Contents can do this for the entire set of records), you can simply sort your records on this number field to get a random order.

          You can then loop through your records once to set up a meeting record that links it to the current record as the person "hosting" the meeting and to the remaining 39 as the attending guests. With the right relationships or ExecuteSQL() calculations in place, you wouldn't need to loop through these 40 records more than once to do that.

          Say you call your table of 40 people, People and your meetings table Meetings:


          People|Host::__pkPeopleID = Meetings::_fkHostID
          Meetings::_fkHostID ≠ People|Attending::_pkPeopleID

          People|Host and People|Attending are my names for two table occurrences based on the People data source table.

          Come to think of it, I could actually do this with no loop at all if I a) assign random numbers, b) sort records and c) use import records to create a set of new records in Meetings with __pkPeopleID imported into Meetings::_fkHostID.

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

          Caulkins Consulting, Home of Adventures In FileMaking