Auto Increment Field

[DEKistler]

Hi there,

Just curious if you have plans for an auto increment field ( sort of a Key field) in the near future; or if you have a simple way to achieve the same thing with an existing field? Calculated field maybe?

Thanks,

DK

[DEKistler]

Jumped the gun on this one too!

Looks like this topic has been out there for a while with the current "solution" described many times.

Still added "Auto Increment Field" ala ACCESS style to the wishlist with 2 votes (I think).

Best,
DK

[beagley]

Hi,
I'm trying to make a Clients/Sessions database, so it looks like I will need a unique client number for relationships.

What is the technique you refer to for creating an auto-increment field?

(Searching for "increment" only brings up posts like this, no one describes how to actually create this field and I don't see the option in integer or calc settings.)

[dhaupert]

Hi there,

The Unique field type functions as an auto-increment field already. The problem is that HanDBase is not a single database engine being accessed from various clients, but a bunch of database engines (desktops, smartphones, tablets, etc) all working with separate copies of the same database and having to sync together. If each client creates its own auto-incremented value, you can see how there would be duplicate values in the database by the time they all sync and merge together their data! So the unique field we have is not really useful in that situation- at least by itself.

There are a few other options for making a key that is unique across all devices. Here's an overview:

1. If you are on the iOS platform, a very simple solution exists- the External field type has a Combine Text mode. You can create a few items in your database such as a date field, time field, and a unique field, for example, and use the combine text field to concatenate them all into a single field useful as a key field.

2. The second option is to use a time field, date field, and a calculation together. The time field stores as seconds from the beginning of the day and the date field stores as days since 1/1/1904. If you do some calculations you can create a value that is a unique time stamp. The trick is to get rid of the years that have already passed from the date value and then multiply by 100,000 to shift it's result over. Thus, the calculations would be: (date - 39420)*100000 + time. And if you output this value as an integer, you should get something that increments by the number of seconds since the last record. No 100% guarantee of uniqueness, as there is a chance that two devices are creating records the same second, but pretty close for most applications.

3. Using the link field for a key is another option- the link field auto generates a key value that contains a time/date stamp, a device ID stamp, and a unique counter all into a single long field of text/digits. You don't see this value as the program hides it from view- for good reason, it's a long string of text and not very useful to the naked eye! But if you really want to, you can get this value into a text field and use it for a key. Here's how: create a link field. Then create a conditional field that has the condition- if field 0 is equal to 0, output is link field, else output is link field. Now field 0 will be evaluated and whatever it is the output of this conditional field will be the value of link field. You can then use this output for your relationship.

Give one of these a try and let me know how it goes!

[beagley]

Neat. Looks like some really interesting ways to achieve something MORE complex than what I need. But I may give one of them a shot.

I did give the "Unique" field type a try, changing my Chart # field from integer to Unique. It did not work. I create a new record, the Unique value is set to 1 automatically, and now I have two records with 1. So Unique no longer works? Or as soon as you set that as the field type, it starts over at counter 1?