Importing records with unique ID#

[Henk Muller]

What is the proper way to set up an unique ID# field that is used in a relationship, especially with regards to importing records into an updates file?

I'll try to explain this.

I have a main database with a text field "serial" [auto-entered serial number (starting with "0001", step 1)].

There is a calculated field (text, "AB-" & serial). This field is used to create a relationship with a child database. This works fine.

This solution is used by several individual users, I mean they are not networked (it's a runtime version).

Now I make some changes and fixed some errors in the main database. I want to distribute this "update" to the users.

But how to prevent duplicate ID numbers after importing and keeping the relationship between the main database and the child file?

[LiveOak]

The best solution is to purchase full copies of FM and network the users. As an option, give each user a unique starting number or prefix "AB", "AC", "AD", etc. The prefix might be better, as overlap of numbers can occur. Make sure on import that the update box is not checked. -bd

[Henk Muller]

Thank you, LiveOak , for your quick response.

From the first part of your answer I understand I was not clear enough. The users of my solution are not connected to each other in any way, they live all over the country and I even don't know them personnally. So the prefix in the ID# field is fixed.

I understand that the checkbox on importing should be unchecked. But I still don't understand what happens after importing the 'old' records into the new database.

The auto-enter serials in the previous version starts at "0001", incremented by 1 for each new record. Say somebody has made 6 records in the previous version. So the unique ID's are:

"AB-0001", "AB-0002"...."AB-0006".

The auto-enter serial in the updated database starts with "1001", the ID field for the first record will be "AB-1001".

After importing the six records from the previous version and making one new record, what will be the ID-fields of the first seven records?

That's my first question.

The second one is:

Is there a better way to calculate a unique ID#?

The first update was after two months after the original release. So I'm quite sure there is no user who already made 1000 records. But what if the next update will be after six month? How many records can a user make in that time? As I said I don't know the users.

I hope you understand what my real question is.

[john.daly]

I had a similar problem with a runtime version used by over 70 individuals all in different locations and with no possibility of networking. They also add ther own records to the database. The way I overcame it was to forget about a unique ID # and in my case producing a unique field consisting of the Company Name and the Postcode combined.

If however you just want to distribute a new version of the database without any data in it, then the user can just import the data from the old version (or you could provide a script to do it) with Perform Auto Enter left unchecked so the existing unique ID's will be preserved.

Don't know if this is any help.

[Henk Muller]

quote:

---

Originally posted by john.daly:

I had a similar problem with a runtime version used by over 70 individuals all in different locations and with no possibility of networking. They also add ther own records to the database. The way I overcame it was to forget about a unique ID # and in my case producing a unique field consisting of the Company Name and the Postcode combined.

If however you just want to distribute a new version of the database without any data in it, then the user can just import the data from the old version (or you could provide a script to do it) with Perform Auto Enter left unchecked so the existing unique ID's will be preserved.

Don't know if this is any help.

---

Thank you for your response.

I think that my solution is different from yours in that I need the unique ID # in a one-to-may relationship. For a better understanding. My main database contains about 200 fields and generates a report per record of 9 pages. I don't know the proper term in english, but the report is about estimating a house's value. Each record in the main database might be related to one to three pictures of the house in the child database and a corresponding text field. So I need a unique ID # for each record in the main database.

For the second part of your answer please read my answer to LiveOaks reply.

[Henk Muller]

quote:

---

Originally posted by LiveOak:

The best solution is to purchase full copies of FM and network the users. As an option, give each user a unique starting number or prefix "AB", "AC", "AD", etc. The prefix might be better, as overlap of numbers can occur. Make sure on import that the update box is not checked. -bd

---

In addition to my reply:

Some of the users are asking for a network version.

Can you give me some advice what I should be aware of in distributing a network version. I'm aware of the need for the user to buy as many licenses for FMPro as they have workstations, how they should install this and set up the network.

But what are the consequences for my database. I read in a newsgroup about the use of global fields in a shared database (that they are only updated in the database if changed by the host). So I have to check the use of global fields in my database. Are there any other differences between standalone solutions and network versions?

[john.daly]

Sorry, I hadn't read your post until after I posted my reply (see timing!).

I still use a unique serial number to relate records. As far as I'm aware you cannot reset the auto serial number from within a script. To get round this what I do is determine the gHighest ID value by sorting the records on the ID# and going to the last record.

I hope all this clear, otherwise let me know.

gHighest ID = ID# of last record

The script then generates a new record with an Auto Serial Number and sets a field gSerial Number equal to this auto generated number. It uses the value of this to produce a 'gCorrection'

gCorrection = gHighest ID - gSerial Number

The ID# of the next record generated can be set as:

ID# = Auto Serial No + gCorrection

This will always produce a unique calculated ID# regardless of how many records have ben produced by your users.

My databse was originally produced as a stand alone but I have one example of it being used on a network but have not been aware of any problems with this. I simply set up a file Open.fp5 that automatically opens the database in Filemaker 5.0. This avoids changing any file names etc. The database has over 200 fileds of all types including about 20 global fields. It's been running for about a year so far with approx 2300 records and still growing.

[Leatherpriest]

What about a script in the new version that sorts the files by their id (ascending), replaces all of the existing files id's, and updates the auto-enter serial number to pick up where it left off ? The replace command can be done without dialog, and the script could run on startup of the new version. The only thing I'm uncertain of is how you could make the script run only once, for the initial update. ... Be sure to opt for "auto update serial #" under the "specify" button, and choose the id field to replace. Provided that your users haven't deleted any files, the sort,replace,and serial# update could theoretically work , as the present id's would be replaced by the same ones, and any additions would pick up where the user left off. However, if there are any files deleted- the replace command would give many of the files new id's and knock things out of sync. If the id itself is not crucial, but the names and other info are - it should be no trouble. Whatever the case - Use caution if you decide to go this route!

[Leatherpriest]

One other thing - Put a "show all records" before the "sort " command.....