1 2 Previous Next 16 Replies Latest reply on Nov 4, 2015 3:58 PM by golife

    Portal sorting problem moving detail records up or down


      I am looking for help. PLEASE ))).


      I am developing a simple portal representing project team members as shown for each project.

      The customer wants to define the order in which team members appear within the portal. So, he can move portal rows up or down, and they will appear in the order defined by the user.


      Also I am using the technique "hide object" when there is no associated project-id in the last row where users would usually enter a new portal row and thereby enter a new team member record. So, all entries are done using a popover.


      Now, I have a field "Serial row number" visible (1...n), and the user shall be able to move a team member up or down in this order, and all row numbers then will update and show the new position of teach team member.


      The sort order is defined in the portal and is not touched (wish of customer).

      The portal sorting is set to this portal row number field.


      Up- and down-buttons allow moving portal row records up, or down.


      My technique here is to get the currently active row number, and I am saving it to a row number variable $rowNum

      Then I re-number all portal rows from 1...n so that the current order is established in a correct way.

      I commit this step and refresh the portal.

      So far it works fine.


      The next step is to go to the previously active row number, to the detail record the user wants to move up (or down).

      So I am using "Go to portal row [$rowNum] which selects this detail record.

      Also this is working.


      To move the current record up in the portal order and to keep the sorting order maintained, I must place it before the previous portal row.

      Also this is working as I am setting the row number field to ($rowNum-1.5).

      The record record is now correctly placed one row up, while the previous record now takes the position of the original current portal row.


      I am saving this step using another commit.


      But here already problems appear though, with or without a previous commit. Because in the example of 7 rows, and depending from which row I am starting with, the new portal row numbers presented in the portal row number field are not consistent. Sometimes they are correctly showing, sometimes they change invoking the same script again and again. (I am excluding the case of first row at this point, and also excluding the case of moving down in the portal.


      Why is this inconsistent behavior appearing?

      And then, again I want to renumber all portal rows with this sorting serial number to not present fractions of numbers. Logically this should be easy since going from first row in the portal to the last row through a loop simply should set the correct row number field value from 1...n. And also the record which we moved up would receive a new serial number representing the portal row number.


      I tried everything from committing, refreshing, stepping away from the portal and stepping in again, the new numbering also does not seem to work consistently in the same expected way.


      What am I doing wrong?


      Here is a script (which I use in variants to test all kinds of scenarios).




      // Portal sorting is set to field "rowNum" of the project team table instance


      Go to Object [ Object Name: "portal_project_team" ]

      Set Variable [ $r; Value:Get (ActivePortalRowNumber) ] // The currently user-selected portal row


      #Serialize ===========================================================

      // Since from previous activities, deletions, etc we can not be sure that the sorting numbers are in correct order

      // I am re-numbering all records according to the current sorting order.


      Go to Portal Row [ Select; First ]

      Set Variable [ $i; Value:1 ]

      Set Variable [ $n; Value:Count (PROJECT_Team::fid_ProjectNumber) ]



           Set Field [ PROJECT_Team::rowNum ; $i ]

           Set Variable [ $i; Value:$i+1 ]

           Go to Portal Row [ Select; Next; Exit after last ]

           Exit Loop If [ $i > $n ] // Should not be needed here

      End Loop


      #Serialize END =======================================================


      // Now I found that I have to commit, otherwise continue with additional editing the new sorting numbers get lost.


      Commit Records/Requests // Test: with or without this script step

      Refresh Object [ Object Name: "portal_project_team" ] // Test: with or without this script step


      Go to Object [ Object Name: "portal_project_team" ] // Test: Reentering the portal as focus went to another portal test


      Go to Portal Row [ $r ] [ Select; No dialog ] // The originally saved portal row with the record to be moved up

      Set Field [ PROJECT_Team::rowNum ; $r-1.5 ] // Moving record in the sorting order before the previous record/row


      Commit Records/Requests // Test: with or without

      Refresh Object [ Object Name: "portal_project_team" ] // Test: with or without


      // And now after committing I want all records in the portal to be re-numbered using a the sorting number.

      // But problems appear already even before this last step.

      // Especially when the user is repeatedly pushing the record-move-up button in the portal, even when pushing in the same portal row.

      // Since the sorting numbers would be re-assigned going from portal row 1 to n, those numbers are not appearing as expected.

      Something may be going on in memory? Refreshing or not, committing or not... I tried everything, and all has a definite influence, but there is no consistency in how the sorting numbers are entered through the system.

      In the end, this portal must be error-free and stable and always work as expected. I am thinking that instead of a automatic sorting in the portal, it might be better to sort associated records in the related table instead and assigning new portal row numbers there.

      The problem may be that here there is an underlying dynamic sorting - which is usually also desired - but during this operation of moving a record up (or down) it should be disabled. I could not find a way of doing that.

      (I can not resist in giving a last comment about scripting with FileMaker: The art of developing with FileMaker seems to be the skill of finding and applying work-arounds, since there is not too much a scripter can define and programatically do, always trying to trick FileMaker into doing things that is normally would not do.

        • 1. Re: Portal sorting problem moving detail records up or down

          I've encountered this need a number of times, myself. I've done something similar to what you're doing in the past, and gotten it to work, but my latest approach takes a bit of a different approach. Here's a brief (?) description of the basics. Rather than have you parse out our conventions for passing multiple parameters and so forth, I'll just outline the principal moving parts.


          Our "SEQ" script takes four parameters:

          • fld = the fully qualified name of the field containing the sequence number
          • key = the primary key of the record to be moved
          • lst = the list of primary keys for the records in the list to be adjusted, in their current order
          • pos = the position to which the "key" record should be moved


          I have a relatively simple custom function (mostly for convenience) that determines the position of a string in a list. The function is x_lst_position ( lst ; value ), and returns a number.


          SEQ first checks that a key and fld have been provided, and x_lst_position ( lst ; key) doesn't already equal pos. (In those cases, no action is necessary/possible.)


          Next, we set $ins to a modified lst, with key moved to the desired position.


          Then, we loop through the records (either in the portal, or in a "processing" window) setting fld to x_lst_position ( $ins ; primaryKey ).


          (An aside regarding the processing window option: we prepend a TLA, or three-letter-abbreviation, for the table to the UUID in our primary keys, and each table has a main layout. We also name all our primary keys the same, "__ID". Thus, we can have a simple script to find a list of keys, and the script "knows" what layout to use and what field to search in. You may prefer to have a different script for different sequencing tasks, or may need to add layout and/or table parameters to the script.)


          This approach isn't that radically different, and I'm sure there are still ways to improve it, but it works well for us, and doesn't involve setting and using non-integers and such. My previous approaches involved more "moving parts", so I find this approach easier to work with and follow.


          Regarding your final comment, I hear you. Over time, though, I've let go of a lot of my assumptions about what FM will or won't "normally" do. I try instead to make my "workarounds" reliable and repeatable, so that at least in our solutions, I can think of them as "normal" functionality.


          I've grown to actually like how FM makes easy things easy and hard things possible. Alternately, making the hard things easy would make the easy things harder, in that we'd have to specify a lot of things that we now take for granted. Or, we'd have to introduce some rigid requirements into our basic development, such as naming conventions and key definitions, and give up some flexibility, so those "harder" functions would work reliably. I like that FM is flexible when I want it to be.


          Chris Cain


          • 2. Re: Portal sorting problem moving detail records up or down

            Perhaps you would say that a hammer tricks a nail into penetrating a 2X4.

            Or perhaps you learn the basic skills and language of the trade.

            Or you could say it is important to understand how things work; and sometimes there is more going on than the first simple observation might suggest.

            • 3. Re: Portal sorting problem moving detail records up or down

              Thank you Chris


              I have to wrap my mind around your solution.


              Actually also I had thought about some of the concepts separating out the sorting list using primary ids together with the sorting key for sorting and then applying this to the portal.


              And since your solution works then i see no reason to apply this on our side. SO THANK YOU ).


              I completely agree with your design goals and ideas.


              Roland )



              OFF TOPIC:


              Regarding your general comment: Well, yes, to an extend I agree.

              On the other side, did you ever had a grip on LiveCode? It is NOT more difficult than FileMaker, but allows in principle to do many more things you want, and using an easy to use English-style scripting language. It does not have to be harder to be able to have full control. And even naming conventions are up to you as the user and not enforced, but I also agree to have rigid naming conventions. Why should it not be possible to control any widgets/controls behavior in much more detail?


              LiveCode just does not provide an "out of the box" database application environment. And therefore it is harder to set up the basics.

              But when it comes to more complex situations, I still find FileMaker to be the art of "going around". Well... what else can you or I do? )


              I would come up with a long wish-list to the FileMaker guys. And after 30 years of being in the market with millions of users (?) - there should be enough money to do more than what they provided so far - maybe "stealing" from what others did with high-level XTalk languages such as LiveCode.


              To do what we are doing here, for example moving up a record in a portal list, does not take more than 10 minutes using LiveCode using a grid view there. All is under control with a few lines of "code". But - well - I see the difficulty as FileMaker would probably have to be refactored from bottom up to allow for "free scripting" and giving access to all object's properties, doing what you want in a much easier way.

              But we should not compare apples with bananas. And there are wish-lists everywhere. So, let us go ahead...)

              • 4. Re: Portal sorting problem moving detail records up or down

                Sorry, there is a mistake: I wanted to say that there is no reason to NOT apply your solution ).

                • 5. Re: Portal sorting problem moving detail records up or down

                  See attached example for a method of moving items in a portal; including control over the jump size.

                  (how many rows to move at a time)

                  • 7. Re: Portal sorting problem moving detail records up or down

                    Well Bruce, yes, that works really great and has a very nice strong robust feeling about it.


                    I takes my late evening  time to have a look, and it is exactly what is needed by us. !!!!


                    The only thing I noticed, if you delete a detail record then the sorting number is not immediately updated. So, there remains this "hole" until user is moving child records again. Since we can not count that he will be doing this, there should be a way of renumbering the "Child::sort field". What do you think?


                    What happens when you create new detail records in the portal? I did not want to go that far yet. Of course will do it tomorrow.


                    How many likes shall it give to you?)))



                    Now, then there is a next challenge, I am also engaged, but could not find a solution yet - not having mastered those details above ). What about numbering in a hierarchical way. If you have a product assembly where products are parts of other products, a hierarchy is born. It is nicely shown as a decimal ordered list, a kind of tree-view. Well, everywhere there are such hierarchies. Just think of the chapters of a book with sub chapters and paragraphs, or taxonomy, or whatever.


                    To present this in a portal, it would be nice or even needed, to number in a decimal-ordered way. I saw the solutions in portals to show hierarchical lists, but so far I never found this where numbering is also updated with any change.


                    Well - it is not just numbering. It also would or could mean to move nodes from one place to the other and inserting the anyway and thereby moving the whole child record to another place. And then all is renumbered.

                    (And the super super bowl would be to allow this using drag-and-drop even showing insertion marks. I guess, at least with the native FileMaker tools there is a limit doing this. )))).











                    • 8. Re: Portal sorting problem moving detail records up or down

                      Those are solvable problems, but I think you should work for a while on those for yourself. Or perhaps others will chime in.

                      • 9. Re: Portal sorting problem moving detail records up or down

                        We'll try. "I" will try, or my collegue will try, to be more specific. But learning from you and others is nice as it saves countless hours of trial and error, and it already provides a solution for general needs. You are giving service to everybody here. I might be able to do so in future, or continue to just raise questions. )

                        • 10. Re: Portal sorting problem moving detail records up or down

                          Let's recap.

                          When you delete a portal row, you want to run the renumber script.

                          Therefore, create a script that deletes a portal row and then runs the renumber script.

                          Attach the script to the delete button in the portal row.


                          Frankly, this does NOT seem like a workaround.


                          I do not give much credence to your statement that "everything in Filemaker is a workaround."

                          • 11. Re: Portal sorting problem moving detail records up or down

                            Bruce, I know this of course and had no doubt that it would be so - I mean regarding deleting or creating.


                            It is late night here, and I was just reflecting my happiness with your solution. I did not have the time to look into the scripts yet. I did not mean it in any way other than just noticing it, and I should not have even mentioned it as it was self-explaining.


                            All else is up to imagination ).


                            Thank you very much. )

                            • 12. Re: Portal sorting problem moving detail records up or down


                              I am uploaded your solution with this little addition, and it just took me 5 minutes to make the resorting based on the deletion. You solution looks very elegant and it is very easy to use it.

                              So, in my eyes, the original question is solved here. And thanks for all the input.


                              • 14. Re: Portal sorting problem moving detail records up or down

                                I have created a portal sort feature which allows the drag and drop of portal rows. I have created 2 techniques, one which "swaps" the position of the line you drag with the line to are dragging into and one which will move the dragged line into place and re-orders the subsequent items (which obviously takes longer).


                                It's not pretty, it's a little clunky and therefore, I'm not posting up the solution (just yet). I'm looking for ways to make it much neater, much nicer...


                                I know Nightwing also have a portal sort solution which is a lot neater than my solution. I just thought I'd try it a different way.


                                Drag and drop is much quicker way of re-arranging things, but I have found that it usually required a fair bit of work to make it an easy function for the end user. Majority of my clients don't really care too much about re-arranging things in the portal, most are happy enough just to type in the sort number and have filemaker sort it for them.

                                1 2 Previous Next