Automatically generating a Unique, Static, "Key" field

For HanDBase related topics that are not specific to one platform.

Automatically generating a Unique, Static, "Key" field

Postby mjhanna » Wed Oct 16, 2013 2:51 pm

The following is a method to automatically generate a unique, static, integer value in a record which can be used as a Key field for linking and relationships, similar to the old Palm "Key" field.

As a new user, converting from Palm and SmartListToGo (ThinkDB), I was surprised to find the lack of a "Key" field which is static and unique for all records. I searched the Forum posts and noted both users and the developers comment that the HanDBase "unique" field type leaves something to be desired. The Forum had several ideas that "implied" the following methid, but I could not find a clear "CookBook". I had to go through a session of tests to discover what would work. Hopefully this will help other users avoid that effort.

My definition of a "Key" field is that its value is unique within this database, and creating a new record will "automatically" load that field with a new unique value which will remain static with that record regardless of other actions in the database. I also prefer to have the potential to view it and have it an obvious incrementing value.

The limitation on "calculated" fields in HanDBase is that the calculation can only be based on the values in fields in "this" record and/or fields in the "previous" record. But what was confusing to me at first was that which record is the "previous" record can change based on the sort of the current View. This changeable "last" record in the sort is always the "previous" record used for values when adding a "new" record. There are no explicit database-wide functions in defining a "calculated" field to provide, for example, the max of all values of a field currently within this database. But this can be accomplished by carefully defining a separate "hidden" field for such a calculation.

You need two fields. I will use Field 1 and Field 2 for this example. One is for the unique static Key. The other is hidden and only used to automatically compute the next Key value when a new record is added. It is easier to create and name the two fields first, then go back and define their details, since each of their definitions will want to refer to the other name.

[Field 1]
Field Name: Key
Field Type: Integer
Not Visible, Not Exported, Not Encrypted
Default: Value From Previous Record, Take Value From: NextKey

[Field 2]
Field Name: NextKey
Field Type: Calculated
Not Visible, Not Exported, Not Encrypted
Result Format: Integer
Configure: ( Previous:NextKey Max (This:Key + 1) )
which displays as: ( P2 Max (F1+1) )

Obviously the field numbers will change in Configure depending on which fields you are using for these two fields. For my Palm databases I always made Key the first field, and I expect in HanDBase I will always make these my first two fields for any database which will have links and/or relationships. To make these fields even more "hidden", I expect to make them not visible in any of my defined Views. That way I would not be tempted to manually alter their values.

The HanDBase "trick" is that even when the database is resorted, this calculated NextKey field will cause the "last" record in any sort to be based on the max of each "previous" NextKey and Key. Thus the "last" record in the sort (which is always the "previous" record when adding a "new" record) will cause the new Key to Default to the unique value of one higher than any key in the database. But since the Key field only refers to that previous NextKey value for a Default, the new Key value is stored as a static value. Thus the Key values will not change with subsequent sorts. While the NextKey values will change since they are computed, the last record of any sort will always have the max value needed for a new record Key.

I recognize that this automatic generation of a Key will not work to ensure unique keys among multiple separate "out in the field" databases which may need to be synced and merged. However, if entering "New" records was always done in each separate database and never in the merged database, a range of separate integer values could be "reserved" for each of these separate databases within the immense potential range of a HanDBase Integer (minus to plus 2147483647). This could be accomplished simply by manually entering a separate beginning Key value in a beginning record in each database. Then these separately maintained databases could be merged at any time into a master database since all their Keys would be unique among all these databases. As long as a given separate database did not create more records than its reserved range, there would be no conflicts. And any separate database could be reconstructed and extracted from the master by filtering on the appropriate Key range.

I hope this description makes this automatic "key" construction in HanDBase clearer and more usable for others,
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 155
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: Automatically generating a Unique, Static, "Key" field

Postby dhaupert » Fri Oct 18, 2013 9:54 am

Hi Michael,

Welcome to the forum! I appreciate the detailed and thoughtful post- love this type of discussion :)

I believe there is a problem with the method you outlined, since calculations on previous records are always based on filter/sort/view settings, there is a potential for a duplicate key value in this case. Here's the scoop:

Because of limitations in HanDBase, your calculation for the next id to use is stored within a single record that doesn't actually use it. Instead the next new record refers to the previous record to get this initialization. The program automatically assumes the new record is the last in the list, but scans backwards from there to find the previous last record in the list. This 'list' though is not static, it can be based on filters or sorting. So that last record may or may not be the last designed record. If it isn't, then there is a chance for two or more records to have the same value. Unless there is no filtering of data or sorting, in which case this would work just fine.

The unique field is one that I've warned users about because it is not designed to handle syncing with other users. For example, you may have two users working on the same database, and the unique counter stored on each of them could easily store the same value on both devices. But since your solution was presented for the case where you were not syncing from other users, the Unique field would probably be OK to use.

There are a few limitations for the unique field:

1. There is only one counter stored per database. This is used for any unique and link fields in the database. So if you have any link or other unique values, you'll see this go up by 2, 3 or more per record. Meaning it's not an auto-increment field, but it will auto increment by at least one for each value.
2. When syncing between different databases (whether on the desktop, or another device) there is a chance for a duplicate entry.

But for this particular use, it should be fine.

Generating a unique static key field can be done- the link field already generates one, and you can use a link field to provide one, especially when combined with the conditional field where you can get it to be displayed. But the format of this is a lot less friendly to the naked eye- it's a bunch of numbers, concatenated with commas. However, they are necessary to distinguish between devices- they represent a time stamp, a device ID, and a counter value.

Another approach can be done on versions that support the External field type where you can concatenate two or more values. For example, you can have a time stamp a date stamp and a unique counter to create your own unique key. Or you could use a unique value along with a device ID. But on Android (the platform you mentioned using currently) we don't yet have the External field type implemented. Hopefully we'll see that soon!

I've also seen where people use a time and date stamp and add them together with something like this:

(Date - 40000)*100000 + (Time)

In short, there are many solutions around for generating keys. The ones that are more unique are generally less readable, and the ones that are more readable are generally less unique!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Automatically generating a Unique, Static, "Key" field

Postby mjhanna » Fri Oct 18, 2013 12:07 pm

Thanks for your kind comments, Dave, and taking the time to review my idea.

I agree and recognize the limitations of this method caused by Views and Filters. I had intended to add a warning to this post that the act of Adding a new record to the database should only be done when using a View which displays all unfiltered records in that database. [And I admit I still have much to learn about HanDBase Views and Filters, especially what is displayed when you click on a Relationship field name.]

Your warning about sorting is potentially valid, but my testing shows this specific Key calculation will work with any sort as long as the View displays all unfiltered records.

You also warn about syncing with other users. As you note, this Key method definitely requires restrictions on adding records. I think it works best in two cases. If only one user ever adds records, then syncing with other users will be possible. Or if users do not sync with each other, they can still export to a master merged database which never adds records. For multiple users in the field adding their own records, never having access to or syncing with others records, and then exporting their records to a home base, this can work well if they each have a reserved Key number range. As that is most of my applications, this can be useful to me.

I also agree that the Link/Linked field types can be very useful for relating one record in the Link database to multiple records in the Linked database. However, I prefer a more visual static Key/Record ID which can have some advantages (and disadvantages) over the Link/Linked field types for relating records. As part of porting my existing databases to HanDBase I am testing using a Key to relate records to mirror what I have done for these previous databases on the Palm. Once I have convinced myself that it works :D I will post a tutorial on how to implement this Key method in case others may find it helpful.

Your summary is most appropriate: "The [solutions] that are more unique are generally less readable, and the ones that are more readable are generally less unique!" I totally agree. But it is nice to have the tools in HanDBase to be able to construct whichever solution works best for me for a given application.

And thanks again for your welcome,
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 155
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: Automatically generating a Unique, Static, "Key" field

Postby dhaupert » Mon Oct 21, 2013 11:13 am

Michael,

You are absolutely right about the sorting being irrelevant to the key calculation- since you are using a running total calculation it will always recalculate after the sort. Sorry I overlooked that fact in my analysis!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Automatically generating a Unique, Static, "Key" field

Postby mjhanna » Tue Oct 29, 2013 6:43 pm

In addition to your insightful comments, Dave, there is one more restriction/limitation on using this method for a key. A user should not use the "Copy Record to New" action to create a new record. Doing so will also copy the existing key, and thus you will have two records with the same, no longer unique, key. You could do this copy, and then manually modify the key to be unique within this database, but that does give the potential to forget and thus destroy uniqueness.

Hmmm... Even more to consider about this method.
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 155
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: Automatically generating a Unique, Static, "Key" field

Postby Brian_Houghton » Tue Oct 29, 2013 10:21 pm

mjhanna wrote:In addition to your insightful comments, Dave, there is one more restriction/limitation on using this method for a key. A user should not use the "Copy Record to New" action to create a new record. Doing so will also copy the existing key, and thus you will have two records with the same, no longer unique, key. You could do this copy, and then manually modify the key to be unique within this database, but that does give the potential to forget and thus destroy uniqueness.

Hmmm... Even more to consider about this method.


Hello Michael,

Here is another method to consider:

1. Add a date field set to use the current date parameter
2. Add a time field set to use the time added parameter

Then, to get a unique number, regardless of if you use the copy record to new option, do the following:

3. Add a calculated field that adds the date field in step one to the time field in step two, and set the value to be returned as an integer

Hope this gives you another method to consider.

Sent from my Nexus 7 using Tapatalk HD
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: Automatically generating a Unique, Static, "Key" field

Postby mjhanna » Thu Oct 31, 2013 12:12 pm

Thank you, Brian,

The basic concept of your idea of using the HanDBase integer representations of the date and time the record is added is sound. However, there is a flaw in simply using addition. It does not ensure uniqueness. For example, if a record is added today at time X, and another record is added tomorrow at the same time X minus one second, the sum of those two pairs of integers will be identical (the date is one greater but the time is one less). If records are likely to be added at the same time of day, the odds of producing duplicates greatly increases.

But a slight change in the calculation can produce uniqueness: multiply one number by a constant before you add the other to avoid an overlap. First I suggest subtracting from the date integer some constant integer value representing a date after about 1980 but prior to today (e.g. 40000, which is 7 July 2013). That will reduce the size of that date integer which otherwise could cause the multiply calculation to get larger than the max Integer that HanDBase can store. Then multiply that reduced date number by a value which will ensure it will not overlap with the time integer. Since time will never be larger than (24*60*60=) 86400, multiplying by 86400 (or simply by 100000) will work. Finally add the time integer to that value.

So if F1 is the date added, and F2 is the time added, use the calculation:
(((F1 - 40000) * 86400) + F2)

That produces a guaranteed unique date/time stamp number which could be used for a unique key. In fact that produces the date/time added in number of seconds since 7 July 2013, which will continue to be an integer less than the max integer for the next 60 some years.

The nice thing about this method for generating the key is that it is independent of any other (previous) records. However, it is a huge number which will not be consecutive or visually meaningful. Always trade-offs. :D

Hope this gives other users ideas,
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 155
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: Automatically generating a Unique, Static, "Key" field

Postby dhaupert » Thu Oct 31, 2013 2:52 pm

Hi Michael,

I agree and that's very similar to the formula I put in my first post in this topic! It seems most of the people who don't use something like this are those who want something that is a simple count and something they can use to refer to their customer/client/patient easily (eg, customer 101, 102, etc) without using a name. In their case the number is too big to be practical for them. In that case, my suggestion is to come up with your own number! You can display a number using one of these calculations to offer as a suggestion but ultimately giving them the power to create an ID is the only way to have something that is unique and short without needng a central ID generator (ie, an online version would generate all values for all versions of the database and keep a single counter to avoid overlaps).
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Automatically generating a Unique, Static, "Key" field

Postby ddhsoftwareadmin » Tue Feb 07, 2017 9:17 pm

Resurrecting an old thread here as I had a customer ask about this same thing via email. I wrote a sample database using a Link field and conditional field. This will give you a unique key value you can use across multiple devices and platforms and it will stay static. The only negative is that it's not as pretty or as short as some of the other proposed solutions. Here's a link to it:

http://www.ddhsoftware.com/gallery.html ... ecord=4868
ddhsoftwareadmin
Site Admin
 
Posts: 221
Joined: Tue Dec 09, 2008 2:13 pm


Return to HanDBase General Discussion

Who is online

Users browsing this forum: Bing [Bot] and 0 guests

cron