1 Reply Latest reply on Apr 17, 2014 3:46 PM by philmodjunk

    Scripting a find request



      Scripting a find request


           Hello everyone,

           I have a database for a weight loss clinic in which we want to find a list of patients who were overweight as of their last visit.

           I created a script:

           Enter find mode

           set field date (first...last)

           sorts records (by person id in descending order and date in descending order)

           Go to first record


           set variable $id (person id)

           set variable $date (date)

           go to record (next exit after last)

           if (people id = $id and date > $date)

           omit record

           end if

           end loop

                   This loops through the records and omits all records but the newest one for each person who visited within the selected dates, I then run another script that loops through these records and omits records where the person has a BMI (body mass index) lower than what the user specifies.

           Go to record (first)


           if (BMI < 40 )

           omit record

           end if

           go to record (next, exit after last)

           end loop.


           The problem I am having is that not all records that meet that criteria are omitted, after running the script i get a record count of 120 for example and if I run it again I get a record count of 102 and I end up running it 5-6 times before getting down to the correct list of records. Is there an easier way to do this? is there something wrong with my script?


           Thanks in advance for any help

        • 1. Re: Scripting a find request

               It's hard to say without looking at the actual data and the complete details of your script. The devil is truly in the details.

               But there are ways to find the "most recent visit" record for each person without needing to use a looping script and also to omit records where the BMI is less than 40 without using a looping script for it.

               Finding most recent visit only records requires a sorted self join and a calculation field that "Flags" most recent visit records with the value 1. Then you simply perform a find for a 1 in that flag field.

               Define this self join relationship:

               YourTable::Person_ID = YourTable 2::Person_ID

               Double click the relationship line between these two occurrences of the same table and specify that YourTable 2 be sorted in descending order by Visit Date. (and visit date must be a field of type date, not text.)

               Now define a calculation field with number selected as the result type:

               YourTable::VisitDate = YourTable 2::VisitDate

               Most recent visit records will return 1 (True) in this field, 0 in all records for previous visits.

               Perform a find for 1 in this unstored calculation field and you'll find only those records for the person's most recent visit. If you also specify ">40" in the BMI field, this will be all records for most recent visits where the BMI is 40 or larger.

               Note: if you have a lot of records to search, the search for most recent records will be slow because the "flag" calculation field cannot be stored, but this should still be faster than a looping script. You may be able to speed that up by performing a find for all records with a BMI greater than or equal to 40 first, then return to find mode and constrain the found set to those with a "flag field" value of 1.