Auto Numbering

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

Auto Numbering

Postby fblittle » Tue Oct 27, 2009 10:13 am

I am using HandBase on the iPhone and Windows xP PC. I have been syncing to MS Access for a few months, but about a month ago after I synced to Access I went in to look at the records in one of the Tables and found that I had records only to a certain date. After investigation of why I found that the Auto Numbering in My iPhone database was messed up. It started repeating the Auto numbering from a certain number and Access was expecting that auto number to be unique as it was the Primary Key, so it rejected everything after the first repeated number. I had this problem before, but I sorted it out and manipulated the data so that they were all unique again. After a couple of months the same thing has happened again.

Why is the auto number repeating it self? I thought it was supposed to be unique? Am I doing something wrong in my sorting and syncinging? I have 402 records in this database.

Records at the top and bottom of the second screen shot show the repeating auto numbering.

Screen shot mistake. The database structure incorrectly shows the DateTimeID as Integer. It is really Unique. The screen shot is old when I was making the DB.
Screen Shot DateNTimeSetup1.JPG
Screen Shot of DataBase Structure
Screen Shot DateNTimeSetup1.JPG (35.77 KiB) Viewed 1617 times
Screen shot of Records.JPG
JPG Screen shot of Records and structure
Screen shot of Records.JPG (196.76 KiB) Viewed 1618 times
Posts: 8
Joined: Wed Jul 15, 2009 1:19 am
Location: Gridley, CA

Re: Auto Numbering

Postby dhaupert » Tue Oct 27, 2009 2:26 pm

Hi there,

Sorry to hear of the difficulties you're having.

By auto-number, do you mean you are using the unique field? If so, that explains it. the unique field stores a local number generator which means it is not unique across multiple devices. It's been mentioned here in several posts, mainly is a legacy field type from prior versions as once we released the multi-user syncing, the field type itself would have had to been changed to deliver not a number, but a combination of letters and numbers that is more in the range of 20 characters than a simple number. This is because in order to guarantee that a value is unique requires many more combinations than a simple number can provide.

Anyway, there are a few workarounds posted around the boards here before, such as:

1. Depending on the platform you could consider a plugin from UnpluggedIdeas called UserID:

2. If you want a value that can be used by relationships and is unique, create a link field, and then create a conditional field that is the following
if field [choose link field] is equal to 0 then output is [link field]
else output is [link field]

This value will look kind of strange, but is a globally unique value and should be usable in Access as a primary key.

Hope this helps!
Posts: 4113
Joined: Tue May 26, 2009 11:51 am

Re: Auto Numbering

Postby fblittle » Tue Oct 27, 2009 10:15 pm

Thanks for the reply:

I am not syncing over multiple devices, just one. I did share my database with several others, but we are not syncing to the same Access database, but to copies.

The problem that I am having is that after a sync, sometimes, not always, I the records are not in the original order, I believe, or something like that, so the next new record I add to my iPhone the Auto number will be a number that was already generated. You can see it in the screen shot above in the first column 'DateTimeID'. The highlited row is '18928' and the top row the 'DateTimeID' is also '18928'. All the other numbers above '18929' are reused as well. Access rejects these because they are going into the Primary Key and are not unique.

I had my HanDBase generate the unique number, but it is actually for the Access Database. I just don't know why the HanDBase does not remember the last number used after the sync? Am I right to assume that it is looking at the last record it sees and generates the next number up after that? If so if the order is not original it will use a number that has already been used.

I think that I will let Access generate the unique number (Primary Key) as the record is added to Access and let it sync back to the iPhone. The unique number is needed for Access more than for HanDBase.
Posts: 8
Joined: Wed Jul 15, 2009 1:19 am
Location: Gridley, CA

Return to HanDBase General Discussion

Who is online

Users browsing this forum: No registered users and 1 guest