1 2 Previous Next 26 Replies Latest reply on Jan 14, 2012 4:08 PM by comment

    Random record selection

    blb1

      I need to create a script/function that will make a random selection monthly from employee records. I have an employee database and to accomodate contracts we need to do random drug testing of a specific number of employees each month. Each employee needs to be checked twice a year. I have talked with several consulting companies and no one has a filemaker solution. We are using Filemaker 11 and Filemaker 11 server. Any ideas? Suggestions? Thank you

        • 1. Re: Random record selection
          comment

          blb1 wrote:

           

          we need to do random drug testing of a specific number of employees each month. Each employee needs to be checked twice a year. 

           

          These two requirements are somewhat contradicting. If the selection is truly random, then you cannot guarantee that each employee will be checked twice a year. The "Filemaker solution" will be fairly trivial once you clarify what exactly it's supposed to do.

          • 2. Re: Random record selection
            Kantala

            I'm sure there are many ways to do this, but I did it like this:

             

            1. Assemble all the required employee IDs into a variable
            2. Use the 'Random Pick' custom function found here to pick one of those IDs. 

             

            Once an employee undertakes a drug test, you will presumably record the date and result in a related record.  As part of step 1, you could exclude any employees who have been tested twice in the last 12 months, so you can ensure that they are not tested more than that.

             

            In order to ensure everyone is checked twice a year, I guess you would need to calculate the number of tests needed based on the number of employees, and simply run the 'choose an employee' script that many times.  In theory, as the employees 'available' decreases, then everyone should be tested.

             

            I'll put together a test file to check and upload it if it works correctly.

            • 3. Re: Random record selection
              comment

              The problem with your method is that an employee that has been tested in January and in March is free to take drugs for the next 9 months...

              • 4. Re: Random record selection
                Kantala

                Absolutely!  That is perhaps a flaw in the concept of only allowing an employee to be tested twice in a 12 month period...

                • 5. Re: Random record selection
                  RayCologon

                  blb1 wrote:

                   

                  I need to create a script/function that will make a random selection monthly from employee records.  I have an employee database and to accomodate contracts we need to do random drug testing of a specific number of employees each month. Each employee needs to be checked twice a year.  I have talked with several consulting companies and no one has a filemaker solution.  We are using Filemaker 11 and Filemaker 11 server. Any ideas? Suggestions?  Thank you

                   

                  Hello blb1,

                   

                  This would be a relatively straightforward requirement if you were to clarify what it is you actually mean by several of the things you say.

                   

                  As has been pointed out, truly random selection would require that any staff member could be tested, whereas you presumably mean that the selection should be randomised from among a subset of staff who haven't already been tested twice for the current year?

                   

                  You also haven't clarified what you mean by year. Do you mean per calendar year? Or are you talking about the previous twelve months including the current month (a 'rolling' year) - or the financial year, or some other twelve month period?

                   

                  Beyond that, it would be helpful if you indicated what form you want the result of the process in - eg. do you want to produce a printed list of selected names each month, or do you want to determine an annual test schedule in advance etc?

                   

                  Regards,

                  Ray

                  ------------------------------------------------

                  R J Cologon, Ph.D.

                  FileMaker Certified Developer

                  Author, FileMaker Pro 10 Bible

                  NightWing Enterprises, Melbourne, Australia

                  http://www.nightwingenterprises.com

                  ------------------------------------------------

                  • 6. Re: Random record selection
                    jbante

                    The problem with fixing that problem is that by minimizing the maximum length of time between tests of any given employee, you'd have to test that employee in a regular pattern of once every 6 months. That predictability would defeat the purpose of the screenings being random. Requiring each employee to be tested twice a year and requiring that the selection be random are not necessarily contradictory.

                     

                    Here's how I would approach the problem, along similar lines to Kantala's approach:

                    1. At the beginning of the year, assemble all the required employee IDs into a variable — each employee ID should be in the list TWICE.
                    2. Uniformly randomize the order of the list.
                    3. Break the list up into chunks for each month of the year.
                    4. Check that the order satisfies some minimum discrepancy condition, i.e., no ID shows up in the same month twice. If the list fails this condition, return to step 1 and repeat.

                    This results in everyone being tested twice, and the selection is satisfyingly random. Security of the stored selection schedule is key. Alternately, you can avoid the requirement for storing the schedule for the whole year by remembering when employees were tested last, removing one or two copies of each tested empoyee's ID from the list in step 1 as appropriate, and only using the data from the chunk for the current month of testing selections. On second thought, you're likely to hire new employees subject to drug testing in the middle of the year, so this alternate approach is probably the way to go anyway. I can put a demo file together tonight if I'm not being clear enough; just let me know.

                     

                    We can resolve the conflict between Comment's concern and mine about the predictability of testing if the policy is that each employee must be tested at least twice a year, but potentially more often. Simply make an additional random selection of a few additional employees per month, without regard to how many times the employees have already been tested.

                    • 7. Re: Random record selection
                      comment

                      jbante wrote:

                      1. At the beginning of the year, assemble all the required employee IDs into a variable — each employee ID should be in the list TWICE.
                      2. Uniformly randomize the order of the list.
                      3. Break the list up into chunks for each month of the year.
                      4. Check that the order satisfies some minimum discrepancy condition, i.e., no ID shows up in the same month twice. If the list fails this condition, return to step 1 and repeat.

                       

                      I believe you could achieve the same result much more simply by assigning each employee two random months.

                      • 8. Re: Random record selection
                        jbante

                        Except that if you did that, you can't guarantee that the testing workload would be uniformly distributed throughout the year — or at least not unless you do basically the same procedure using months in place of employee IDs.

                        • 9. Re: Random record selection
                          comment

                          jbante wrote:

                           

                          you can't guarantee that the testing workload would be uniformly distributed throughout the year

                           

                          Yes, that's true. Anyway, uniform distribution (on any axis) and random do not go well together - so this is all moot until blb1 clarifies his business requirements.

                          • 10. Re: Random record selection
                            jbante

                            There's no inherent conflict of interest between "uniform distribution" and randomness. The Random function returns uniformly distributed values. Colloquial understanding of randomness usually implies uniform distributions — each combination of lottery numbers is supposd to be equally likely, for example. There is, however, a conflict between randomness and low discrepancy: if we require that an employee can't be re-tested until at least 3 months after his or her last test, this increases the predictability of the selection.

                             

                            As you said, this is moot without more details of business requirements, which will need to state what needs to be random, and what doesn't. Until then, I'm operating under the presumption that the number of employees to test each month does not need to be random, but the selection of which employees will be tested does. Holding the number of tests per month constant does not have to make the selection any more predictable.

                            • 11. Re: Random record selection
                              comment

                              jbante wrote:

                               

                              The Random function returns uniformly distributed values.

                               

                              In large numbers, yes. If you have a sufficiently large number of employees and you assign each one two random months, then the testing workload will be uniformly distributed throughout the year.

                              • 12. Re: Random record selection
                                jbante

                                It will be asymptotically approximate, yes; but we can't guarantee that it will be exact in practice.

                                • 13. Re: Random record selection
                                  MicheleOlson

                                  jbante wrote:

                                   

                                  It will be asyptotically approximate, yes; but we can't guarantee that it will be exact in practice.

                                  OK. Jeremy. You stumped me with that one.

                                   

                                  What is "asyptotically"?

                                   

                                  Thanks,

                                   

                                  Michele

                                   

                                  • 14. Re: Random record selection
                                    comment

                                    No, not with small numbers. And if you assign random employees to a month (instead of random months to an employee), you cannot guarantee uniform distribution of tests from an employee's point of view. It all comes back to the same core issue, no matter where you start.

                                    1 2 Previous Next