ID Fields after the fact

[Polyvox]

Okay, I've got a database (table?) with almost 1000 contacts that I have been accumulating over the years. Now after reading the "White Paper for FMP Novices" I see the value in having an ID Field as described therein. I want to write a script that will accomplish this after the fact and another script that will perpetuate the autoentry in the future. Am I being clear, I hope? I have called my new ID Field ContactID. I want the serial number to begin with 1001 and each record to have its own unique number. Also I am curious why we cannot just create a field that will reflect each record's record number that FMP seems to generate anyway? Wait, I think I know the answer: it's because a record number is only related to a group of found records and does not reflect a unique record with a unique serial number. But back to my original question?

[LaRetta]

I want to write a script that will accomplish this after the fact and another script that will perpetuate the autoentry in the future.

Welcome to FM Forums! Auto-Enter serials are critical to relational design and you're asking the right questions. You won't need a script for this. If you are serving this file, do this while unserved and back up first.

1) Go to Define Fields and select the Options button on the ContactID field. On the Auto-Enter tab, you will notice Serial Number. Check it. There is no need to worry about the value at this time (we'll trigger the 1001 serial start later). Say OK then DONE.

2) Go to a layout with your ContactID field on it.

3) Show all records (CRITICAL YOU DO THIS).

4) Put your cursor in the ContactID field.

5) Select Records > Replace Contents.

6) Select Replace with serial numbers and type 1001 in the initial value. Leave increment at 1.

7) Be sure checkbox is CHECKED that says "update serial in auto-enter." After FM serials all your records, it will set the next serial (prime it) and it will automatically begin serializing all new records from [color:green]the ending value forward.

8) Then select REPLACE.

Do NOT allow this field to be available to Users. Do not check 'Prohibit modification of value'. If you display it, turn off entry to field (Field Format > Behavior and uncheck 'Allow Entry.'). You'll be all set.

LaRetta

Edited January 25, 2007 by Guest
Changed 'from this value' to 'from ending value'

[Polyvox]

How odd. I did everything you suggested and it seemed to work. Now after creating another layout with the contactID field in it (different from the original layout which I used to create the serial numbers) I find that every record shows a question mark (?) in that field rather than the serial number that I know is there. Diagnosis?

Also I have not added a new field since I did this procedure. I trust that the next new record will contain the next serial number in order. But what happens when you delete a record? Does its unique serial number disappear with it, never to be used again? If I add a record, then change my mind and delete it, then immedialtely add another, how do the serial numbers behave?

Thanks for your patience.

[LaRetta]

Hi, Polyvox, luv the pic. ;-)

The number field which contains your uniqueID is probably too narrow to display the whole number. The results are ?. Enlarge the field and it should display correctly.

Yes, when you delete a Contact, its unique ID will be lost forever; such is life. Just as a Social Security number is never reaassigned to a different person, a unique serial dies with the entity it makes unique. Every table should have uniqueIDs. You can't waste these serials because the number will keep incrementing forever and their sole purpose is to bind different pieces of the Contact's information together. For example, when you create an Invoice for this Contact, it will inherit this ContactID as well.

You will have holes in these serials no matter how careful you are so relax about them. People care about holes but computers do not and unique IDs serve the computer. Create a new record, watch the serial increment, then delete it and create another. You'll soon get over this feeling that you are wasting numbers because that's just how things work - holes are life.

LaRetta

[Polyvox]

LaRetta

Glad you like my pic. I chose it to express that I hope I can remember what I learn.

One thing seems to lead to another. But this is still on the topic of serial numbers. I did create a few new records and deleted them, watching the serial numbers advance. Interesting. Then I tried a Find using the serial number, but I wasn't able to enter anything into the ContactID field. It wasn't as if I was trying to create a new serial number. I was merely trying to find a particular record by its number. Easy question. I know. But it leads to my better understanding.

By the way, a Table is a Database, a Database is a Table, yes?

Thanks.

Edited February 1, 2007 by Guest

[LaRetta]

Then I tried a Find using the serial number, but I wasn't able to enter anything into the ContactID field.

We turned off entry to the field so Users couldn't change it. One of the GREAT enhancements (as of vs 7) is the ability to control field entry in Browse AND Find modes independently. But with vs. 6, it's all or nothing. It is still critical that this field not be accessed in Browse so you might consider performing the Find on the Contact Name instead. It is usually easier searching for names than remembering a Contact's ID. In fact, I have never had Users need to perform a find on the UniqueID. Otherwise, you need to use a script attached to a button (instead of menu-activated View > Find Mode). There are a few methods. I'll explain them both because I think you'll be moving into that area sooner than you think and Custom Dialog and globals will give you more to play with. Either of these scripts will work.

Script 1

[color:blue]Allow User Abort [ Off ]

Set Error Capture [ On ]

Go to Field [ ContactID ]

Enter Find Mode [ Pause ]

Perform Find [ Replace Found Set ] Uncheck Restore (and be sure to use Perform Find from Sort/Find/Print section instead of Editing.

[color:blue]If [ not Status ( CurrentFoundCount ) ]

Show Message [ "No Contact found matching this ID." ] On this Button default, remove CANCEL so OK is the only option.

[color:blue]Show All Records

End If

Exit Record/Request

Script 2 A Custom Dialog can accept the User input and perform the find. To do so, create a new field called gFind (global) data-type text then create a script as follows:

[color:blue]Allow User Abort [ Off ]

Set Error Capture [ On ]

Show Custom Dialog [ On General tab > Dialog Message (text), type: Enter Contact ID. On Button defaults, leave both OK and CANCEL. On Input #1 tab, click Enable Field Input and select gFind. Say OK and OK.[color:blue]

If [ Status ( CurrentMessageChoice ) = 2

Halt Script

End If

Enter Find Mode [ uncheck both Restore Find Requests and Pause ]

Set Field [ ContactID ; gFind ]

Perform Find [ Replace Found Set ] Uncheck Restore (and be sure to use Perform Find from Sort/Find/Print section instead of Editing.

[color:blue]If [ not Status ( CurrentFoundCount ) ]

Show Message [ "No Contact found matching this ID." ] On this Button default, remove CANCEL so OK is the only option.

[color:blue]Show All Records

End If

When you try to leave either script, it will ask if you want to Keep or Replace the Find Requests. This was one of the most confusing aspects of prior versions. Since you are inputting a different Find criteria each time, the question is moot. Just say OK. Then Insert > Button and 'Perform Script' this script. Call the button FIND ID.

A point about the global field: if this file is multi-user (served or shared) then the global will automatically lose its value when the file closes. It's kind of nice to leave that global value so that, if the User wants to try again then they can see the value they entered last time (in the Custom Dialog). Sometimes it's appropriate to clear your globals. This will be a decision only you can make. After playing with it, you'll at least be aware of why/when the value sticks.

As an aside ... it's been awhile since I've had to work in vs. 6. We have it soooooo much easier in 7/8. But it was good practice for me.

Update: I just saw your edit. Prior to vs. 7, a database is each file. From 7 forward, a file can hold many TABLES (which are also databases held within one file) AND have many files (which are databases).

LaRetta

[Polyvox]

From 7 forward, a file can hold many TABLES (which are also databases held within one file) AND have many files (which are databases).

LaRetta,

Thanks.

Digesting.

Didn't quite understand the above quote though. Up til then you've been remarkably clear. Maybe that's what I get for not updating to 7/8.

[comment]

The word "database" has no clear meaning in Filemaker, and it's best not to use it. In versions before 6, each file is a single table, and multiple files can be grouped to form a solution. In version 7 and above, each file can contain multiple tables, so a solution requiring 5 tables can be a single file with 5 tables, or 5 files with a single table each - or any combination in between.

[Lee Smith]

From the Online Help.

Table

A collection of data pertaining to a subject, such as customers or stock prices. A database file contains one or more tables, which consist of fields and records. When you create a new table, a visual representation, or occurrence, of the table appears in the relationships graph. You can specify multiple occurrences (with unique names) of the same table in order to work with complex relationships in the graph.

Lee