9 Replies Latest reply on Aug 25, 2009 2:59 AM by ricklhamilton_1

    Change and retain the order of records in a Layout

    ricklhamilton_1

      Title

      Change and retain the order of records in a Layout

      Post

      This question may be a bit complex for a forum dialog - but the forum has been great support already so here goes:  :)

       

      The situation and Problem:

       

      I'm building an app that automates the creation, editing and printing of Airplane checklists. The construct of the data is simple

       

      Checklist  -----> ChecklistArea------>Checklist task item

       

      preflight -------> Cockpit ----------> Item 1

                                                           Item 2 and so on.

       

      In the table checklistTask:

       

      checklistID  (number)

      checklistAreaID (number)

      taskSequence  (number)

      taskItem   (Item to do)  (Text)

      taskCompletion (type of complete..CHECK,ON OFF etc) (Text)

       

      Within each combo of Checklist and Checklist area the sequence [taskSequence]of the detailed item is of utmost importance to the flow.

       

      I want to build a Layout that allows the user; first see how the tasks are sequenced. (Easy enough). But I want them to be able to move items UP or Down in the sequence and then "commit" that change to the database.That would mean finding an elegant way to mass update the taskSequence number value based on how the user has moved items up or down in the list. 

       

      For now - I have a simple Layout that uses a portal to show the checkListTask item in the order set in the taskSequence field. But I doubt the Portal object has the capability to move items up or down in a list - Only to sort them. 

       

      The hacky way to do this is to add a new temp field and allow the user to enter numeric values into that to represent the new sequence. But there are too many items to make that practical...I'd like for them to be able to highlight an item and then click an up or down button to move it ahead or behind where it sits today.

       

       The question:

       

       I don't need someone to write a white paper in the forum on how to do this. Just maybe point me in the direction of an existing example or reference a Layout Object that may help me create this capability or identify a template where this exists. My guess is that I will need to make some changes to the table structure as well - I'm OK with that too...

       

       

       

       

       

       

       

       

        • 1. Re: Change and retain the order of records in a Layout
          comment_1
            

          ricklhamilton wrote:
          That would mean finding an elegant way to mass update the taskSequence

          I think that all that would be required is for the moved record to exchange its rank number with the record above/below it.

           

           

           

          BTW, there is no need to have a  checklistID field in the checklistTask table: the task is related to its parent area, and the area "knows" who its parent lis is.

          • 2. Re: Change and retain the order of records in a Layout
            ricklhamilton_1
               Yes - That is what is required. But what object in FMP 10 will allow you to display a series of records and move them? One way I suppose is to continue to use the Portal Objet - then add buttons that preform an action to the selected record (UP/DOWN). Then have the button action "flip flop" the values based on it's existing number +1 and or -1.
            • 3. Re: Change and retain the order of records in a Layout
              comment_1
                 Filemaker does not have built-in capability to move records. This must be scripted.
              • 4. Re: Change and retain the order of records in a Layout
                etripoli
                  

                ricklhamilton wrote:
                Yes - That is what is required. But what object in FMP 10 will allow you to display a series of records and move them? One way I suppose is to continue to use the Portal Objet - then add buttons that preform an action to the selected record (UP/DOWN). Then have the button action "flip flop" the values based on it's existing number +1 and or -1.

                 

                That would seem to be the best way, and what I was going to suggest.  As records are created in the portal, they would get the 'order' assigned in a hidden field.  Then, when a record in the portal is selected, and the user clicks the 'Up' button, a script decrements the order field of the current portal field, and increments the previous portal field.  Not having done this, I see the potential for an issue in how FM will sort the records in the millisecond that two portal records have the same sort order number.  In any case, have the portal sorted by the Order field, include a refresh (if needed) at the end of the script.


                • 5. Re: Change and retain the order of records in a Layout
                  ricklhamilton_1
                     Thanks to both of you for that. I am going to give this a try. But in order to keep the timeing correct as you suggested - I have started by adding a field to the record tempSeq. That way if the record is selected I can put a value in the temp field that is one less (or greater for down) and then update the same temp field in the previous record the have a subscript update the current seq from the tempSeq and then finally re-sort the portal view. It will be interesting to see how fast this can be done - or not.. :)
                  • 6. Re: Change and retain the order of records in a Layout
                    comment_1
                      

                    Filemaker will not re-sort while the script is running. At least not when you do this in the related table - I haven't tried it in a portal (in general, it's best to operate directly in the child table than to fiddle around with portal rows).

                     

                    I believe it's better to exchange the rank numbers, rather than add/subtract 1. This makes things much simpler when you add/delete a record. The initial rank for a new record can be simply its serial ID.

                    • 7. Re: Change and retain the order of records in a Layout
                      ricklhamilton_1
                        

                      Ahh yes. The table of the records I want to move have an initial sort order of the SN of the record. But the display Sequence is critical to the application so I thought it best to add some validation to the display sequence field. As such "No blanks" "No duplicates" are allowed. There for I was not sure I could just "swap" the values. I'm still new to FMP so I am not sure when it commits records in a script and when field validity checks are done - so I created a temp field that might help me get around this issue AND provide a way to track "last" sequence so that I can set the list back "undo" sort of if the user does not like the changes they just created.

                       

                      Your comment about using Portal view is interesting. It will be clunky to be showing the initial list sort in a Portal and then having to  jump to unique layout to do the re-sorting - but I will test that out. If the "move" up or down is not fairly instantaneous it will not be well received....

                       

                      • 8. Re: Change and retain the order of records in a Layout
                        comment_1
                          

                        I don't see why you need a temp field - you can use script variables to hold temp values. The "undo" feature sounds rather ambitious to me: if they don't like what they did, they can click the opposite button.

                         

                        Keep in mind, however, that this method changes the sort order for ALL users alike (and will fail if another user is editing one of the affected records).

                         

                         


                        ricklhamilton wrote:
                        It will be clunky to be showing the initial list sort in a Portal and then having to  jump to unique layout to do the re-sorting

                        I believe it's clunky for a script to run in a portal. Portals are user-interface devices. The developer can (and should) work directly with the data.

                         

                        • 9. Re: Change and retain the order of records in a Layout
                          ricklhamilton_1
                            

                          I would imagine that scripts are clunky in portal views. I agree the undo is ambitious. I will give this a run through and see how it works best.

                           

                          Thanks for all your advice.

                           

                          Rick