Jump to content
Sign in to follow this  
jjjjp

maintaining the integrity of ordered records

Recommended Posts

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?

Share this post


Link to post
Share on other sites

Can a topic belong to more than one topic order?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

See if this helps:

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 ) ) )

  • Like 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.