10 Replies Latest reply on Jul 27, 2012 9:32 PM by russbrad

    Arbitrary record sorting with promote/demote

    russbrad

      I'm trying to set up a system where a user can re-order items in a portal in any way they want.

       

      I've set up a variable in the parent table called %componentCounter which counts the number of associated child records. Then in the child table I've got a field called %listPosition which keeps track of where in the list the current record sits. The theory is that the user can then use Promote & Demote buttons in the portal view to move the records around.

       

      Now I have two problems:

       

      1 If the list becomes corrupted by a user deleting a record or some other unforseen eventuality, like manually adjusting %listPosition, I need the system to be robust enough to be able to rebuild the order. It could perhaps order the items by record creation date or alphabetically, then recreate that sorting in the %listPosition variables, for example. I don't know how to do this.

       

      2 I can write a function that adjusts the %listPosition field of the current record, but then finding the record that is one above or one below the current record is something I haven't yet been able to do. I've tried searching for the record with the same parent id and the particular %listPosition value that I want using Perform Find, but it always tells me that no matching records could be found.

       

      Essentially, I need a way to traverse through the portal records in the order that they are currently displayed, or a fast way of searching for and accessing the next/previous record in the list.

        • 1. Re: Arbitrary record sorting with promote/demote
          beverly

          I have an auto-enter serial number field called SortOrder in all tables. This is not the "ID" and is NOT used in any relationships. It's strickly for sorting and thus can be changed. The value of the number doesn't matter, except that they sort as you need. Here is the script I have used for a particular portal for many many years. Your TO name will be different, of course!

           

          SWAP_portal_rows.jpg

          Beverly

          • 2. Re: Arbitrary record sorting with promote/demote
            comment

            russbrad wrote:

             

            I'm trying to set up a system where a user can re-order items in a portal in any way they want.

             

            It seems to me your system is built in such a way that any user can promote or demote a record, and this will affect all other users. Is this really what you want?

            • 3. Re: Arbitrary record sorting with promote/demote
              russbrad

              Yes, it's for creating quotes and I want the user to be able to create a quote and reorder its components to appear the way they want to the customer.  The vast majority of quotes will never have more than 10 components.  The user in this case is the company that is providing the quotes.

              • 4. Re: Arbitrary record sorting with promote/demote
                russbrad

                Thanks, I didn't know about Go To Portal Row.  I'll give this a go tomorrow and let you know how I get on.

                • 5. Re: Arbitrary record sorting with promote/demote
                  beverly

                  I used this method for re-ordering quote items. And because a script is used to "re-order", it can be tied to permissions and/or date and/or any other criteria. Once it's "set", it can't be "unset" by the next person.

                   

                  The same scripted method can be used in a List view to reorder items.

                   

                  Also, you might take a look at the example file by Excelisys using FileMaker ability to drag and drop objects with script triggers. You can re-order without the arrows, and you can move any portal row to any other position (sort order). <http://www.excelisys.com/filemaker-tips-tricks-demos-downloads.php> "Drag and Drop..."

                   

                  Beverly

                  1 of 1 people found this helpful
                  • 6. Re: Arbitrary record sorting with promote/demote
                    comment

                    russbrad wrote:

                     

                    The user in this case is the company that is providing the quotes.

                     

                    What I meant is that one user of your solution is going to set the order for all the other users. To put it another way, if the saleperson is modifying an item, then the manager won't be able to perform a reorder.

                     

                    Anyway, have a look at:

                    http://fmforums.com/forum/topic/60325-manually-reorder-portal-records/#294075

                    • 7. Re: Arbitrary record sorting with promote/demote
                      russbrad

                      Michael Horak wrote:


                      What I meant is that one user of your solution is going to set the order for all the other users. To put it another way, if the saleperson is modifying an item, then the manager won't be able to perform a reorder.

                       

                      Well, the order of components in a quote is, at least conceptually, a feature of the quote itself, so it makes sense for other users to be able to see the way the quote has been put together.  Also, the components are edited on the main quote creation screen, since components are just a handy way of grouping items in a quote (Kitchen, Bathroom, Option 1, Option 2, etc).  So if a manager and salesperson are fighting over data in a quote, that's more of an operational issue for the organisation.  I don't see how it's different to editing any other piece of data in a multi-user environment.  If there is a particular technical reason why this would be a problem, I'd be interested to hear it.

                      • 8. Re: Arbitrary record sorting with promote/demote
                        russbrad

                        Thanks, that did work in the end, and because it doesn't require the numbers to be contiguous it's a lot more robust than the system I had thought of.  It completely addresses issue 1 in my question without requiring any extra bookkeeping, so that's a plus.

                         

                        The code didn't work for my solution immediately because the portal I'm working with is actually a self-view portal (which I probably should've mentioned at the start).  Navigating between records on a self-view portal requires a bit of extra bookkeeping like so:

                        Edit Script Select Component (IBIS2)_2012-07-22_14-06-29.png

                        This is because navigating to a new record means navigating to a new view, so the portal isn't the same portal, and you need to tell it which row should be active.

                         

                        So I added those steps everywhere in the script that required it, and now it works perfectly:

                        Edit Script Swap Component Rows (IBIS2)_2012-07-22_14-09-01.png

                        • 9. Re: Arbitrary record sorting with promote/demote
                          comment

                          russbrad wrote:

                           

                          I don't see how it's different to editing any other piece of data in a multi-user environment.  If there is a particular technical reason why this would be a problem, I'd be interested to hear it.

                           

                          It's not necessarily a problem, just a detail that requires clarification. In a multi-user environment, some user actions can be meant to affect the current user only. This is particularly true for actions that change the way data is displayed, rather than the data itself.

                          1 of 1 people found this helpful
                          • 10. Re: Arbitrary record sorting with promote/demote
                            russbrad

                            Well, thanks for taking the trouble to clarify.  I definitely want the changes to affect all users.