Delivering Updates - Record Serialization?

[innodat]

How I can deal with the serialization issues when delivering an update to a data file?

Let me define the process and point out the issue: The user receives automated update for his file - records are imported into new file, old one renamed, moved to backups, etc. He/She opens the new file, all records are there, good. Creates a new record - fails, because FileMaker seems to pick-up on record serialization (Number Field "Record ID" using Auto Enter of Serial Number) from the NEW FILE, and not where the imported records left off... Let's say there's 100 records which where imported, it still starts over at "1", "2", etc. instead of "101".

Is there a good way this can be addressed??

Thank you so much!

Michael

PS: I know that separation enthusiasts will probably point out that in a properly separated model, this problem doesn't exist... however, there's reasons why complete separation was not an option here.

[Ron Cates]

In each table after import you would do something like this

Go to Record/Request/Page[ Last ]

Set Next Serial Value [ pk_cus_id; SerialIncrement ( _pk_cus_id ; 1 ) ]

This will set the serial number in the auto enter serial dialog

[innodat]

The things you learn, amazing. And it always when you think you got at least the essentials about FileMaker down...

Thank you for your answer - I should probably un-sort the records before I go to the last one?

Thanks again!

Michael

[Vaughan]

I should probably un-sort the records before I go to the last one?

You should do whatever is necessary to get the last serial number to be the last record in the found set. Sorting a serial number in a text field can produce undesirable results because the "numbers" will be sorted as "text" so "2" will be sorted after "19".

I got caught on this big-time a couple of years ago on this issue, now all my key fields are stored as numbers without leading zeros or any other characters.

[David Jondreau]

Even easier would be to drop serial numbers for keys and use a calculated ID that is unique across all other IDs.

Like this one: NightWing

[innodat]

I got caught on this big-time a couple of years ago on this issue, now all my key fields are stored as numbers without leading zeros or any other characters.

It seems this is happening to me... I could convert the field to a number field with only numbers, no other characters, and deliver it as the next update. I don't believe this would mess-up the import for existing data?

I guess I could still have a auto-enter calc which adds a prefix such as "ART"xxx for artworks, "IMG"xxx for image records, etc. which then is the Record ID the users sees and can work with.

Any objections to this?

Even easier would be to drop serial numbers for keys and use a calculated ID that is unique across all other IDs.

Like this one: NightWing

I like this approach a lot too, however the user can't take notes about records which have IDs that are 23 character long... how did you address this in your solution? Some users are just old fashioned and like to work this way.

[comment]

I guess I could still have a auto-enter calc which adds a prefix

Or you could just put the prefix as text on a layout.

[innodat]

Or you could just put the prefix as text on a layout.

True, but then I have a double prefix for those records which were created in the old file... I can't get rid of those prefixes because they function as keys and all links between records would be lost.

I've just did the following test: imported records to a new file with Record ID field type number. Prefixes came with, and don't seem to bother finding the Max Record ID (un-sort, go to last in this case). The journeyman's suggestion seems to be working just fine.

[comment]

If you are importing from a closed file (as you should), records will be imported in creation order.

[David Jondreau]

I like this approach a lot too, however the user can't take notes about records which have IDs that are 23 character long... how did you address this in your solution? Some users are just old fashioned and like to work this way.

I don't understand what you mean by "taking notes". If you mean you want an ID that is somewhat readable, by all means create an ID that makes sense in your context, but don't use it as the "real" key.

It's true that Filemaker only indexes the first 20 characters of a word in a field, but any decent UUID function will create an ID that is broken up with spaces or dashes.

[innodat]

I don't understand what you mean by "taking notes". If you mean you want an ID that is somewhat readable, by all means create an ID that makes sense in your context, but don't use it as the "real" key.

Some users like to identify records by their ID number, rather than some other less absolute piece of information. For example: "Record 678, need to inquire abut blablabla." The ID the user uses has to be just as unique as the actual key, otherwise it's not reliable. Therefore I don't understand why it can't be one and the same? It's not like the user can change it. We put it in a tool-tip field to be visible upon request.

PS: I like the NightWing system you pointed out, I think I will deploy it in future solutions.

[Ron Cates]

"The ID the user uses has to be just as unique as the actual key. otherwise it's not reliable."

But can you guarantee that whatever system you use to generate these IDs is reliable?

NightWing's is an example of a very strong system but anything less is likely to produce unforseen complications depending on the complexity of your solution.

The NightWing system doesn't exactly produce a more user friendy ID?

I use an invoice numbering system seperate from the key IDs that works great for user interaction but I don't use them as keys at all.

[comment]

I use an invoice numbering system seperate from the key IDs that works great for user interaction but I don't use them as keys at all.

Why not?

[Ron Cates]

Because as much as I thought it was sound when I came up with it, due to my lack of experience and knowledge as a developer, over time i ran into a couple problems that basically broke it. The problems that resulted were minor but if they were being used as keys it would have caused much bigger problems. So by not using them as keys I was insuring against my own fallibility as a developer I guess.

[David Jondreau]

I've never met a visible key that users didn't want to have the ability to change.

The ID the user uses has to be just as unique as the actual key, otherwise it's not reliable. Therefore I don't understand why it can't be one and the same? It's not like the user can change it. We put it in a tool-tip field to be visible upon request.

Helping a user refer to a record has a lower threshold for "uniqueness" than linking related records. If there's ambiguity, a user can look at the two or three records and decide which one is correct.

But sure, the key can be one and the same. But you may run into problems, like the one you're experiencing.

There's rarely one "right" answer and I don't think it's necessarily wrong to have usable pks, but I've found that they're more trouble than they're worth.

Why not make the IDs more useful? A combination of the description or a client name and a serial number could work. Or a user-defined key?

[Vaughan]

I've started creating "keys" for contacts, courses etc that are the primary key (simple serial number) plus a Luhn check digit added to it. This creates a unique number (actually they were unique anyway) that is now non-sequential so simple typos don't easily match other keys. For codes that are entered over the web by end users it has proven effective. Simply typing the next number won't result in somebody else's code. These codes are designed to be non-sequential, so that solves the argument about missing numbers etc.

Examples:

primary key = 8098, check digit = 6, code = 80986

primary key = 8099, check digit = 4, code = 80994

It's even possible to make the check digit into an alpha character instead of a number.

As an added bonus I can look at the code and easily work out what the actual primary key value is.

[innodat]

primary key = 8098, check digit = 6, code = 80986

primary key = 8099, check digit = 4, code = 80994

At first I thought that you were banking on the idea that there would not be more than 9999 records in your database. But I realize that even 10001 or 1000001 would be unique, because you ALWAYS add another digit, regardless of how many digits the primary key has. Interesting.

It's even possible to make the check digit into an alpha character instead of a number.

Aren't alpha characters ignored when your field is of the type number? To FileMaker 8098A will be the same as 8098B... are you using a text field? That would lead to problems described above.

[innodat]

One more note on the "Go to last record" method: it's only reliable if the order of records has not changed previously. This could happen through an earlier import for example. That imported order will be the new "unsorted" state and doesn't necessarily match the original creation order. Therefore the record with the largest ID is not necessarily the last one anymore.

IF the ID field is a number field however, it should be possible to sort by it and then go to the last record:

Sort [Record ID: Ascending]

Go To Record [Last]

[bcooney]

This demo I did a while ago might be useful.

[Vaughan]

At first I thought that you were banking on the idea that there would not be more than 9999 records in your database. But I realize that even 10001 or 1000001 would be unique, because you ALWAYS add another digit, regardless of how many digits the primary key has. Interesting.

Yes I had to think about that too. It's elegant, isn't it.

Aren't alpha characters ignored when your field is of the type number? To FileMaker 8098A will be the same as 8098B... are you using a text field? That would lead to problems described above.

This is a "code" field for the benefit of the users and is only displayed on-screen. It's NOT used for relationships EVER. This code field can be a text field if it has alpha-numeric characters.

The codes with the last digit being a alpha character involve substituting the check digit with a character, using a Choose function for 0 to 9.

Choose( LuhnCheckDigit( primarykey ) ; "K" ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "J" ; "L" )

Note that the Choose function as written never returns "I" since it will be confused with "1". Also note that the letters don't have to be sequential or in any particular order. Finally, the "L" should never be returned. If "L"s start appearing there is some real trouble afoot.

For this client I put the alpha code at the start not the end to distinguish "account" codes from "student" codes. I was going to make the account codes "A80986" where the 6 is the check digit, but thought otherwise because it made the code unnecessarily long and added no other function. So I made the "A" change based on the check digit instead. Thus primary key 8098 became F8098, and 8099 became D8099. This made the codes very easy to tell apart while scrolling through the database because they change significantly from record to record, instead of the last character just being different.

The LuhnCheckDigit() custom function is available here:

http://www.briandunning.com/cf/707

[innodat]

bcoony, thank you for the demo - hadn't found it before when searching. I find the locate file very impressive. The actual import now looks pretty much the same, except that you sort descending and go to the first record, instead of ascending and last - any particular reason for that?

Vaughan, I now understand your approach - thank you for the link

Thank's everybody for this interesting tutorial and discussion :-)