5 Replies Latest reply on Dec 10, 2013 9:44 AM by philmodjunk

    How to create a record sequence



      How to create a record sequence


           I have a solution that imports a set of daily surgery appointments and then sorts them by surgeon for display on iPads.  The patients are originally sorted by their surgeon and their set times, but sometimes during the day we have to "shuffle" them a bit.  I would like to figure out away to (1) assign a sequence number for each patient under each surgeon and display it with their individual record, and then (2) if I get a new patient or if we decide to move a patient up or back in the sequence, re-number them and display the new sequence numbers.

           Any ideas?    I think I could do it if I only had one surgeon, but adding in the multiple surgeons moves it out of my league.  TIA!

           Dan Simonson

        • 1. Re: How to create a record sequence

               I've been wanting to do something like this.  I took a shot at my idea then used some triggers and scripts to automate it.  I'm not a pro developer at all so you or someone else may want to optimize it.  I've tested it out quite a bit, and it seems to work quite well.  But, test yourself before you implement it.

               I'm using the following fields in my example:  Doctor, SurgeryTime, SurgeryOrder, and Sort.

               I created the Sort Field to aid in the sorting.  Sort = If ( IsEmpty ( SurgeryOrder ) ; "zzzzzzzz" ; SurgeryOrder )      I am sorting the list by Sort, Doctor, and then SurgeryTime.  The reason I have the "zzzzzzzzz" in there is that if all the SurgerOrder fields are empty and you decided to go from sorting by Doctor/SurgeryTime to a numerical order, FileMaker will place the empty fields above and numerical field.  Adding the "zzzzzzzz" places the empty fields below the numerical fields in the sort.

               Once you have the SurgeryOrder fields filled you can adjust the order by selecting the the preferred placement from all the available records.  This is done by going into Layout Mode and creating a Value List that represents the SurgeryOrder field values.  Double-click on the SurgeryOrder field to see how that is done.

               Next, adjustment is accomplished by using Triggers upon entering and changing the value of the SurgeryOrder field.  Check out the triggers and then look over the scripts.  Essentially, if you are at say order X and you want to move it to N, you select N but the script will make that a value of (N-.5) if N<X so that it goes below the current number N.  Then all values are ordered in sequence and you get your expected result.  For, selecting a number above the current number .5 is added.  Number "1" got weird on me.  So, I customized the selection of the 1st position in the script.

               Anyway, explore the attached file.  If you want to go by the SurgeryOrder then click in there and press enter.  Then move things around.  If anyone is up to improving this please do.  One improvement I see would be to put in a number in the SurgeryOrder field to start and let that be the position in the order.

          SurgerySchedule (Created in FM 10)


          • 2. Re: How to create a record sequence

                 Cool!  I'll give it a shot.  Thanks very much.



            • 3. Re: How to create a record sequence

                   Won't changing a patient's position in the order involve a change of their "set time"? If so, just editing the time fields can reposition their location in the order.

              • 4. Re: How to create a record sequence

                     Phil, you are a genius!  Why the heck didn't I think of that?  I'll just have my users edit the time fields.  This will also work for add-on patients, we will just give them a time that is after the last time.  

                     It's a bit messy, because they will have to figure out the correct time to enter, and it will involve entering 3 or 4 digits instead of one, but it will certainly work with the data I have.  I'm going to try it and see if it works out better than Braxton's setup.

                     Thanks to both of you...



                • 5. Re: How to create a record sequence

                       If I were you, I'd try to set up some buttons that Move patients up or down in the order by modifying the value in the time or timestamp field by a set amount to move them up or down the scheduled order instead of having them manually enter a new time.