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.
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?
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:
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...
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)
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.
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]
(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?
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.
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...
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.
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...
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.
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")
…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.
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...
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:
ReorderPortal_eos.fmp12.zip 13.4 K