1 2 Previous Next 21 Replies Latest reply on Jan 15, 2014 11:06 AM by erolst

    Re-Sorting Portal Records After Update


      This should be very simple, but I'm missing something:


      I created a new database from the Customers starter solution. I added one table, Phone Numbers, because I wanted to store several (unlimited) numbers per person.

      The table has the same ID as the customers table has, plus a description, phone and formatted phone field. In addition, I added a numeric "Seq" field, which is simply the order I want to show the numbers in.

      The relationship specifies to sort the records by the Seq field, as does the portal that I added on my customer detail form.


      The portal is in a tab control panel, and has columns for Seq, Description and Phone. When I view the portal it's properly sorted and shows the 2 numbers (Seq 10 & 20) in the proper order. Now when I want to change the order, I modify the "10" Seq to "25", and it should then re-order the portal records, right? I expect the entire record must be saved, so I tab to the next portal record, and nothing happens.


      If I go to another tab page and back, the records are still in the same order ("25" then "20"). However, if I move off the layout (go from Customer Details to Customers) then move back, the records in the portal are now in the proper order ("20" then "25"). Ideally, this re-ordering should happen on a timlier basis. I tried using script triggers, triggering a refresh window script step, but this doesn't seem to help.


      Is there any easy way to refresh the portal records? Thanks...

        • 1. Re: Re-Sorting Portal Records After Update

          Use the "refresh window" script step with the "flush cached join results" checkbox checked.


          Changing a value in a portal does not necessarily redraw the portal.


          You can tie this action to a script trigger so that when a user modifies a value in that field, it forces a redraw of the screen/portal.


          You'll most likely need to add "go to object" as well in your script, as your portal is in a tab control.

          • 2. Re: Re-Sorting Portal Records After Update

            Thanks Mike.


            It makes sense that the portal wouldn't redraw by itself, so I'm using a script.

            I am using refresh window with that checkbox checked.  Presently that's the only action in the script.


            The issue seems to be how to trigger that script.  The OnRecordUpdate doesn't seem to be available from the portal, just from the layout itself.  But, I'm not really updating the record on the layout, but the record in the associated table.  How do I trigger the script from a record changing in the portal?



            • 3. Re: Re-Sorting Portal Records After Update

              You need to use a field-level trigger on one of the fields in the portal row.


              Record-level triggers only work in the context of the current layout/table, which you are NOT editing when you are changing related data in a portal.


              Right click on a field in the portal and select "set script triggers", play around with those there to activate your script.


              (also wouldn't hurt to read up on all the triggers and how they are fired, and in what order!). Check this out as a starting point:


              • 4. Re: Re-Sorting Portal Records After Update

                Mike, that is the perfect answer.  I did OnObjectSave and called the RefreshWindow script.  This is SO close.  There is one slight issue (which I will live with if needed):


                Assume 2 records:

                Seq 10, description "Home", phone "8055551212"

                Seq 20, description "Cell", phone "8055553333"


                They show up in that order.  In first row, I change "10" to "25".  The moment I tab off the field, the portal immediately redraws itself to:

                Seq 20, description "Cell", phone "8055553333"

                Seq 25, description "Home", phone "8055551212"


                Unfortunately, my cursor is now sitting in the "description" field of record 1, the one I wasn't editing (well technically, I was editing record 1, but it's now record 2).  I suppose FM is smart enough to know I was on "record 1 field 2" after tabbing off the field.  It would sure be more user-friendly to move the cursor to the proper record number (and field number). 


                I'm thinking somehow remember the Seq number value (the new value), then do a find on that value in the portal, then jump to that record, field 2?


                Any ideas, or a topic to browse?  Thanks...

                • 5. Re: Re-Sorting Portal Records After Update

                  You can return to a portal row's position.


                  set a variable at the beginning of your script using the "set variable" script step, to store that field value (25)

                  Set Variable $position = table::OrderField


                  Then after your other actions, you can use the "Go To Portal Row" script step, specify "first",


                  then you need to loop to compare:



                      Exit Loop If  ($position = OrderField)

                      Go To Portal Row (specify "next" here, and check "exit after last" as a safeguard)

                  End Loop


                  Basically, you're telling filemaker to go to the portal (first row), then go to each row and exit when the field matches the variable you set.


                  Make sense? Glad to help.

                  • 6. Re: Re-Sorting Portal Records After Update

                    So here's what my script now contains:


                    Set Variable [$seq; Value: Member Phones::Seq]

                    Refresh Window [Flush cached join results]

                    Go to Portal Row [First]


                      Exit Loop If [Seq = Member Phones::Seq]

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

                    End Loop


                    (had to type this all in. If I select all script steps then command-C, the paste option isn't available in Firefox.)


                    This seems to be just what you described.  But the same thing happens, it goes to field 2 of the "ordinal record number" I was on.  Seems to be ignoring the loop entirely...

                    I'm assuming that since this is triggered after object update, the new value of Member Phones::Seq is now "25", right?

                    • 7. Re: Re-Sorting Portal Records After Update

                      It looks right, I don't really have a testbed for this so I'm going on memory.


                      Do you have a copy of FileMaker Advanced? If so can you open up your script debugger (tools menu), and step through your script? You might figure out where it's going wrong. If not, if you could put a demo file together, it might help further diagnosis. You are welcome to message me privately.

                      • 8. Re: Re-Sorting Portal Records After Update

                        Thanks, I just discovered that while waiting for your answer.  There's definitely a problem having nothing to do with the script.

                        I had changed the records so they were:

                        20 Cell #######

                        25 Home #####


                        Now I went to row 2 and changed "25" to "10" and tabbed out of the field.  The script started and paused at first step.

                        While stepping through the scriptI notice that it properly exits the loop, without moving to the next portal record, but then still the old record position is selected anyway.


                        This happens whether I'm changing from "10" to "25" or from "25" to "10".  In either case, the loop seems to execute properly and exit at the right place, with the right portal record selected, but then the "old" record becomes selected automatically.  By "old" record, I mean the old ordinal position in the portal (which is of course the place where the changed record used to be).

                        It's almost like FM is remembering which portal row number I was on, and making sure I'm still on it after the operation, regardless of whether I moved to another record via script.


                        Maybe in retrospect I should have hidden the seq field and just exposed "Up" and "Down" buttons next to the portal and put my script steps there to resequence, then select the proper portal record...

                        • 9. Re: Re-Sorting Portal Records After Update

                          Sounds like you're experiencing the order of operation in FileMaker.


                          It DOES remember your place in line. Remember the action that fires a script trigger will continue to fire AFTER the trigger script, unless the result of the trigger was false (represented by a result of "false" or a boolean result of 0 in Filemaker).


                          Try adding "Exit Script - result = "false" " to the end of your trigger script. See if that forces FM to stop what it's doing.


                          You could also add a Go To Field after going to the first row, but before your loop, if you did want the tab to still fall into the next field.

                          • 10. Re: Re-Sorting Portal Records After Update

                            Okay Mike, you da man!


                            That worked like a charm.  I did need to put Go To Field, else the cursor is left blinking in the Seq field.  This wouldn't be the worst thing, but still it's more UF to put the cursor in the next field.  I will mark your answer as the correct one.


                            Next on my agenda is to auto-resequence the records so if I have 10, 20, 30, 40... and I change 10 to 25 in order to move it down one,

                            the script should then cycle through the new list of sequences (20, 25, 30, 40...) and renumber them back to 10, 20, 30, 40... in that order.


                            Doing that would eliminate the need to show the sequence number at all, and I could go to Move Up and Move Down buttons instead.


                            Anyway, thanks for the help, it was awesome...

                            • 11. Re: Re-Sorting Portal Records After Update

                              well, you could add a loop before your navigation loop to set this, but there might be better ways to have up/down navigation buttons without the looping.


                              IE this:


                              But the scripting is considerably more complicated.


                              If you just wanted to reorder after the refresh you'd do this:


                              Refresh window (check flush cached join results)

                              Go To Portal Row – First


                                 Set Variable $num = $num + 10 //note – adds 0+10 for the first time in the loop to = 10

                                 Set Field – table::OrderField = $num

                                 Go To Portal Row – Next (check "exit after last")

                              End Loop

                              …etc… already covered steps.


                              I'd also advise placing an "allow user abort – off" step at the beginning, so a user can't escape the renumbering process.

                              • 12. Re: Re-Sorting Portal Records After Update

                                Hey Mike,

                                You are right, the referenced article was quite a bit more complex.  Since I know that in a contacts-type database, the sequence numbers won't ever get very high (who has 100's of phone numbers?), I was thinking of a loop to renumber all of the records by adding, say, 1000 to their Seq value, then another loop similar to the one you propose.  This takes longer, but as said, how long could 5 or 6 phone numbers take?  Also, it has the advantage of not being complex when deciding which seq. numbers to use.  If I add a new record it will set its default to 999, then call the resequence script. If I delete a record, it will just call the resequence script (after actually deleting the record, of course).


                                If I tap the Down button, it will simply change the Seq to Seq+15, then call the resequence script.  Ditto for the Up button (Seq-15).  Shouldn't even need to worry about reducing the Seq of the first item or increasing the Seq of the last item, as they will just get resequenced by 10's anyway, right?  That would avoid needing to test for value of the lowest / highest Seq.


                                I may try this out as a learning experience.  Thanks...

                                • 13. Re: Re-Sorting Portal Records After Update

                                  Ok, got it (can't figure out how to copy and paste steps here, so here's a picture of them).



                                  • 14. Re: Re-Sorting Portal Records After Update

                                    rmittelman wrote:


                                    Ok, got it (can't figure out how to copy and paste steps here, so here's a picture of them).

                                    You can't copy the script steps as text, but at least on a Mac, you can press Cmd-P in the script window, then choose “Open in Preview” and copy the text from the PDF window in Preview. This has the added advantage that this version spells out all the step options that are otherwise hidden/buried in the dialogs. (You can of course actually save the script as a PDF, if necessary.)


                                    btw, identifying the original record is much easier (fewer variables to set), faster (less work for FileMaker) and more reliable if the phone table has a primary ID of its own and you use that, instead of a (supposedly) unique combination of other field values.


                                    See here for a much shorter version of a reorder script:

                                    1 2 Previous Next