maintaining the integrity of ordered records

[jjjjp]

Some of my records I want to allow users to order. For example, I have a Table called Topics. I implemented this in the only way I thought possible: by creating a field called TopicOrder, which takes on the values 1, 2, 3, 4 ... Users can add or delete a record and move records up or down, and my scripts do all of the necessary bookkeeping, making sure that the TopicOrders form a continuous series starting at 1. If the integrity of this series isn't maintained, then various database functions are likely to act in screwy ways. I also created a consistency check that is performed on the server overnight, to make sure that all of the lists of ordered records in my database have not lost their integrity. In theory, I should never receive a message from the server telling me that a sequence has become corrupted, for example, by running 1, 2, 3, 5, 6.

Things have been fine till now (the database has had modest traffic over a couple of years; its usership is slowly increasing), but a new group of users began entering data recently, and last night I received a message saying that one of the ordered lists of records has been corrupted.

It is very hard for me to know what went wrong. Although I believe my scripts allowing users to perform operations on ordered lists are very careful not to let them abort a process without getting to the end or cancelling (in which case I restore the previous list using a temporary set of records that I set up at the beginning of the script), I am thinking that perhaps Filemaker got disconnected somewhere in the middle of the process. Or perhaps there is a bug somewhere in my code that is triggered in rare circumstances.

My question is, how do experienced Filemaker programmers set up these ordered lists, and how do they ensure that they remain uncorrupted? There seemed to me no way native to Filemaker of maintaining a user-defined order for records. If one has no choice but to do all of the bookkeeping oneself using a Number field, is there a way of guaranteeing the integrity of the list? Or does one need to create a utility that periodically checks for integrity and then automatically reorders the list when corruption has been detected?

[Fitch]

Can a topic belong to more than one topic order?

[jjjjp]

Let me present a somewhat simplified model of what I'm doing. Topics are added in a strict order to a series. When users create a new series, they can then (1) add topics to the series, (2) remove topics from the series, and (3) change the order of the topics in the series (by clicking little up and down arrows in a layout). So there are tables called Series and Topic, which basically consist of an ID and a series or topic name, and each topic that gets added to a series gets represented as a record in a table that I call SeriesTopic. Each record in this table contains the following fields: SeriesID, TopicID, and TopicOrder. The same topic can appear multiple times in a series and even in other series. The important thing is that if you were to do a Find in the table SeriesTopic for any SeriesID, and to sort by the field TopicOrder, you would see, for example, the following entries in that field: 1, 2, 3, 4, 5, 6.

My problem is that if the order goes out of whack (e.g., 1, 2, 4, 5, 6; or 1, 2, 3, 3, 4, 5), then all operations break down. I'm wondering whether people who need records to be strictly ordered rely on something similar to what I'm doing, or whether there is an intrinsically more robust way of creating ordered lists of records. Surely many people must allow users to order records. And if they rely on a field with an integer as I do, what precautions do they take in case the ordering somehow becomes corrupted? I am thinking that I may need to do a consistency check on the order in each script that manipulates the ordered list, and then to re-order the list in cases where the order has been corrupted, but this seems to me inelegant and time-consuming. In short, I am wondering whether there are best practices for the situation I'm describing.

[comment]

See if this helps:

http://fmforums.com/forum/topic/60325-manually-reorder-portal-records/#294075

[jjjjp]

Thanks. That looks like a good solution. I will need to think some more about whether it will work for me in all situations. For example, in one of my portals I now display the rank even though the records aren't organized by rank. But I may decide that not much will be lost by dropping that column from the portal.

[comment]

The rank could be computed.

[jjjjp]

In a script, I assume, not as unstored calculation. Correct?

[comment]

Not necessarily. If you have a sorted relationship, you can calculate List ( Child::SortBy ) in the parent record, and let each child find its rank in the list as =

ValueCount ( Left ( listOfValues ; Position ( ¶ & listOfValues & ¶ ; ¶ & searchValue & ¶ ; 1 ; 1 ) ) )

[jjjjp]

Thanks again.

I have one more question about your solution. (See attachment.) It seems there is no attempt to protect records against two users changing the ranks of overlapping pairs. What I have done till now (in my original implementation, using sequential ranks) is open the parent record before proceeding with a swap, temporarily disallowing anybody from doing a swap (or a delete or an addition of a new record) from the same parent. I'm wondering if I should keep this strategy in place, or protect only the two swapped records by trying to open both of them before beginning with the swap and leaving them open concurrently. But this strategy would, I imagine, be inelegant, necessitating the opening of an extra window (one window for each child being swapped). Better to be more restrictive on what other users can do and more elegant (open the parent) or less restrictive but less elegant (open both children)?

Again a question of best practices before I change a lot of code.

MovePortalRow.fp7.zip

[comment]

I am not sure I would worry too much about this, given that another user can perform another swap immediately after. But if you do, it might be better to perform the swap in the portal instead of in a layout of the child table.