3 Replies Latest reply on Jun 27, 2014 10:30 AM by philmodjunk

    Calculation for Creating Found Set of records

    sccardais

      Title

      Calculation for Creating Found Set of records

      Post

           Every month, we want to send 1/12th of our customers a Customer Satisfaction survey.

           We also want the list to represent a pro rata cross section of our customers based on the product they are using and their size based on the number of their customers (students) they track with our system.

           We have 6 versions of our product 5 customer "size groups." This gives us 30 potential subsets. We'd like to take 1/12th of each and export to a spreadsheet. 

           For example, assume Group 1 is Product 1 and Size tier 1 and it has 100 members. We would take 8 at random from this group setting a temporary flag in the individual records. We would continue this process for the remaining 29 groups.

           I plan to use the Case function to put each customer into one of the 30 groups. Assuming that works ...

      How can I randomly choose and flag 1/12th of the people in each group?

            

            

            

        • 1. Re: Calculation for Creating Found Set of records
          philmodjunk

               I don't quite see a need for the case function calculation here. Think of this like a summary report with sub summary parts.

               You can sort your records by version and "size group" to group your records under 30 sub summary layout parts. A summary field that "counts" a never empty field, if placed in that sub summary part will tell you the number of records in that particular group.

               You could also perform a find to pull up a found set of just one such group at a time. Then Get ( FoundCount ) can also count the records for you.

               To make a random selection, I'd add a number field to the table and use Replace Field contents with the Random function to update this field with a random number. If I then sort the records on this field, they are sorted into a randomized order--just like shuffling a deck of cards.

               So consider this broad outline of a scripted solution:

               Find all of the records you want to put through this process.

               Use Replace Field Contents to assign a random number to all of the records.

               In a loop:

                   Perform a find to isolate one group in a found set.
                   Sort them on the random number set.
                   Go to record/request/page [ Div ( Get ( FoundCount ) ; 12 ) + 1 ]
                   Omit Multiple Records [ No dialog ; Get ( FoundCount ) ]
                   Use Replace Field Contents to set a "flag" field to a value to mark the remaining records, a randomly selected 1/12 of the group.

               Repeat for each of the next 29 groups

          • 2. Re: Calculation for Creating Found Set of records
            sccardais

                 Thanks very much. I see what you mean about not needing the Case function.

                 The script outline is very helpful but I don't quite understand what's going on in the two lines below. Can you elaborate? 

            Go to record/request/page [ Div ( Get ( FoundCount ) ; 12 ) + 1 ]
            Omit Multiple Records [ No dialog ; Get ( FoundCount ) ]

                 I'm sure these steps are related to isolating 1/12th of the records in each group but I'm not following the logic -- and I want to understand rather than just copy your work!

                 Thanks again.

            • 3. Re: Calculation for Creating Found Set of records
              philmodjunk

                   The calculation determines the "dividing line" between the records to retain in the found set and those to omit. The functions can be looked up in FileMaker help if they are unfamiliar. They compute the record number of the first record to omit so that go to record can go to that record in preparation for omitting records.

                   Omit Multiple records omits the number of records specified starting with the current record. If the number specified is larger than there are records from the current record to the end of the found set, all of the records from the current record to the end of the found set are omitted.

                   Get ( FoundCount ) will always be a value larger than the 11/12'ths that you need to omit and so this will omit all the records from the current record to the end as needed.

                   Example:

                   Say you have a found set of 24 records.

                   Div ( 24 ; 12 ) + 1 = 3 so the go to records step makes the third record the current record.

                   Omit multiple records then attempts to omit all 24 records--the found set. But since there are 22 records from record 3 to record 24, just those records are omitted, leaving 2 records, 1/12th of the total behind to "mark" as records of customer to whom you want to send your survey.