Does this script cause the needed update?
Refresh Window [Flush cached Join Results]
I'm not recommending that you do this as a final solution to the problem, but this may provide a useful clue.
Also, if you change the portal record via the other layout and then return to the current layout, is this all done in the same window?
If you include your calculation field in the portal row, do you see the previous value or the updated value?
If you do not see the updated value, do you see anything change if you click into the field?
Yes - the script changes the value of the field sorted on.
I'll avoid the 'flush' procedure for now. 'Refresh' by itself did not solve the problem.
The calculated field is taken from the 1st record in the related table.
The change sequence is this: go to related record on another layout. Many things can be done on that other layout. One of them is to launch a script from that other layout to a 3rd layout that adds a record to the table that the sort field is based on (that added record is now the 1st record in the related table) . Two 'returns' get back to the original layout. The calculated value has changed in the portal row, but the portal rows have not been resorted. Both the calculated value and the source field are the same. (sorry if this sounds confusing).
But did refresh window [flush cached join results] actually work? This step does not change any values.
It is confusing. How do you sort records if the calculated field is "taken from the 1st record in the related table"?
I don't see how that can be used to sort any records in the related table if it only references the first related record.
Thus I must be missing a detail here. Can you post your actual calculations and relationships involved?
The portal is a list of customers. The sort field is the status of the customer's most recent order.
The 'go to related' goes to the detail layout for the customer, including a portal that lists all that customer's orders - most recent first.
The user can create a new order from the customer detail screen, which then will provide the status of the customer's most recent order (the newly created order) for display in the original portal.
So there's a table of customers; a table of orders. The portal shows the status of the customer's most recent order - which is likely to change when a new order is added.
The 'flush' seems to resolve the problem, but I still don't understand what triggers the portal to re-sort. I would hate to have to find all such situations - or remember everytime I create one - that I have to 'flush' the cache before returning to the original layout. A user found this. It's something I would normally gloss over during testing (no more!) since I would expect FM to take care of a re-sort when necessary.
I thought there must be a third table involved, something not clear in your original posts. I take it that you have an as yet unamed table that I am calling "Dashboard" that is the basis for your layout with the portal that won't sort:
But you keep indicating that the portal to Customers isn't sorting. What sort order have you specified in the portal to customers? How does the addition of a new record in Orders for a given customer affect that sort order?
As I stated when I first suggested Refresh/Flush, this isn't the final solution, but I needed to be sure that this was the issue. Sometimes, when there is data one or more "remove" from the records immediately present on the layout, we have to "help" the updating process. The fact that refresh/flush works just helps confirm that this is the issue here.
'Dashboard' is a good term for the layout on which the 'customers' portal resides.
The primary sort field for the 'customer' portal is the status of the most-recent order (the relation between customers and orders is sorted by order creation date) so that all orders of like status for the selected customers displayed in the portal are grouped. The definition in the customer table for the calculated field by which the customer portal is sorted, 'orderStatus', is 'unstored, from customers, = orders::status'.
The addition of a new order can affect the sort order, if the status of the new order, say 'pending', is not the same as the status of the 1st previous order, say 'scheduled', the sequence of customers in the portal would change.
Hope that helps point to why the customer portal is not triggered to re-sort.
Ok, this is not a situation that is typical of every sorted portal. The combination of additional relationships and unstored calculation is sufficient to stall the needed updates without a bit of a push from a script.
Here's what I did:
I set up the following relationships:
Dashboard::gPortalUpdate X Customers::__pkCustomerID
Customers::__pkCustomerID = Orders::_fkCustomerID
The relationship from customers to orders is sorted in descending order on a serial number field to list the most recent order first for a given customer. The portal to Customers is sorted on the cRecentOrderStatus field which simply replicates the value of OrderStatus in Orders.
I then went to the orders layout and set it up with the OnRecordCommit trigger to perform a one step script:
Set Field [Dashboard::gPortalUpdate ; Dashboard::gPortalUpdate ]
this works for two reasons: gPortalUpdate has global storage specified in field options so that the update script will work from any layout were it is needed. gPortalUpdate is the match field in a Cartesian Join relationship. Thus, updating the value of the match field will force the portal sort (and any portal filter) to update. The process of going to the orders layout, editing or creating at least one new record will open a record in that table for editing and changing layouts back to the dashboard layouts will automatically commit records on the orders layout--thus tripping the OnCommitRecords trigger and updating the portal. The other thing that you'd need is to use a script to delete records from the Orders table--or don't allow deleting orders records so that the script can also use this set field step to force the portal to update.