TABLES
I have two tables, order, and status. Status is many to one, tracking all status changes against the order.
UI
On the order layout, the user only needs to see and search the latest status.
USERS:
there are web (data API) and FMP client users.
CHALLENGE:
For either user, when a new status is submitted, I would like the script to perform a record insert only.
My goal is to avoid writing updates to parent or child recrods during the submission. If I can avoid updates, then I can avoid checking for locked records. Record locks would return an error to the users. This solution can't have that, so I would need to create a submission queue. --- brutal
The assumption is that I need to update existing records (an extra indexed field on either side of the relationship ) to achieve a performant portal search.
There's some thinking process underneath there that we need to know about to able to give any kind of solid suggestion.
Why is the need to check for locked records something to avoid at all cost?
And if that is to be avoided then a queue is the natural price to pay for that choice, so why would that be 'brutal'?
Explain 'performant portal search'. Are you experiencing slow performance? Perhaps there is an underlying reason that can be fixed with a small fix instead of with a sweeping architectural change.
For instance: the current status could be an attribute on the order instead of needing to go collect it through a relationship. The statuses table is still valid then to show the history.
If I understand your post correctly, you are asking how to display the latest status with minimal performance impact in portals. You ask if dedicated fields, I assume they point to each other in a relationship, could achieve this, then say you want to avoid updates on these fields to avoid record locks. Did I get this correctly?
If so, you could create a join table between your order and status field whose purpose is to point to the latest status. When a new status in inserted, you also create a new join record that points to the new status. You then delete the previous join record. That way, the only record that gets locked is the one being deleted. The delay between the creation and deletion should be minimal and, hopefully, negligible for your purposes.
I haven't coded FileMaker yet, I am just thinking it through.
'brutal' is dramatic, but to build the lock check and submission queue, felt like it might be more complex than needed. Plus depending on the interval of the server routine, search results might not be accurate.
Bdbd's join suggestion should solve it. So two tables; status and statusHistory. Before the script could delete and insert a new status record, I would need to make sure that a record exists and that that record is unlocked. If neither condition is true, is it safe/good practice to put the script in a loop? -- caveats?
Assuming the relationship between order-status itself has a sort that forces the most recent status to display at the top of the heap. Then a second "magic-key" relationship will allow you to create new records in status without ever locking "orders".
Will you ever need to do a find on orders based on the status? We went down this road a while ago. We are back to converting everything back to a single, static field that gets updated with a transaction.
As we move more and more of our legacy code to transactions, the potential for collision has gone down significantly. Nothing ever has that record open for more than a few milliseconds. If it fails, it tries a couple more times. That reduces almost any need for a queue for most systems.
A queue can also be really helpful, though. A mix between a static field being updated, and a relationship to status records from today only, or since the last update... can really limit the problems you anticipate in your posts above. That hybrid approach, I've rarely needed to use, but it works well.
Yes, that status field is the main criteria in their searches. They also perform batch status updates on found sets of orders so, I am leaning towards the two tabled suggestion. That way I can 'skip the queue' and if I implement a loop as you mentioned, I should be fine.
Are you going to have a toggle to show the “active” status? That combined with using a global field GTRR approach would still be fast. You can also avoid a related find that way.
No, no toggle. There are two text fields ( role and action ) that define the status of the parent and it's not an open-closed scenario for the parent. During the year, it is an active record that's in constant use, so there is a high probability that the parent will be locked.
How are you handling the find on the child table to know which record is the last one in a find? The toggle I was referring to was for the child record. That will allow you to isolate the child record easier. ( for example, update the current status in the child table to not-active, and then create a new active record, all in a transaction that will naturally limit how many records FileMaker has to touch to know what the current status is ).
From a performance standpoint, calculating the last record, or doing a find through a relationship doesn't typically scale well.
Based on bdbd's suggestion, I will have two tables (or two occurrences); statusHistory and status. That status table is a one-to-one of the latest status, it is also the relationship that will be queried. This solves the performance concern and simplifies the update script.
Ok. I was just curious. It sounded initially like that's what you were talking about, but also sounded like that status record would be updated as frequently as the parent record. Thank you for allowing me to pick your brain.