Creating an index field that auto updates

[cartographer]

I've been trying to find out how to define a field that has an integer value that auto updates each time I create or delete a record. Each row below is a record in Filemaker for that field:

1

2

3

5

7

8

If I were to insert a record at 7, it would change the value of 7 to 8, and the value of 8 to 9 and automatically insert the value 7 at that record. If I were to delete record 7, record 8 would change its value to 7 in that field.

How do I setup my field to have that characteristic?

[Lee Smith]

It's a little vague on what you are really needing.

What is the purpose of your numbers?

You are not the first beginner that has wanted to see Order to their serial numbers. However, I would caution against doing this if the numbers serve any Purpose in your design at all. After all, they are just numbers, however, if you have used these numbers in things like a relationship, you can break things, so I recommend that you just leave them alone.

If on the other hand you just want to see a sequential number when looking a list of records, then check out the @@ (insert record number), availabe in Layout Mode, under the Insert Menu.

If I've missed the point, just reply and let us know what you are trying to do.

HTH

Lee

[cartographer]

The database is one where I have a list of links on a webpage that is running off a PHP based guestbook program. http://www.macmaps.com/astronomy.php is the example. I have already separated each numeric entry in the white portion of the website into their own records on Filemaker. If I wanted to add an entry, naturally all the numbers have to shift upwards one number ahead of that entry. If I wanted to remove an entry the reverse would have to happen. The problem right now is creating a numeric field where the entry value doesn't get lost when deleted from the index since I may want to reuse it. I used to run this fine in Excel, but I'm wanting to stop using Microsoft Excel and move to Filemaker as it seems a lot easier to sort values, and saves me an extra step of having to send everything over to BBEdit to do an additional sort.

[Lee Smith]

So, starting with something like this

1.7 Department of Astronomy at UC Berkeley - Extrasolar planet search

1.8 "Bill's 14.5 inch telescope making project" - A detailed look at building your own telescope.

1.9 Dean Dauger's Solar System Simulator for the Macintosh

1.10 European Space Agency

1.11, 1.12 Extrasolar Planet Encyclopaedia - in French and English, with mirror site.

1.13, 1.14 Keck Observatory and their news page

what would an addition look like?

Lee

[cartographer]

1.7 Department of Astronomy at UC Berkeley - Extrasolar planet search

1.8 "Bill's 14.5 inch telescope making project" - A detailed look at building your own telescope.

1.9 Celestron International - manufacturers of binoculars and telescopes

1.10 Dean Dauger's Solar System Simulator for the Macintosh

1.11 European Space Agency

1.12, 1.13 Extrasolar Planet Encyclopaedia - in French and English, with mirror site.

1.14, 1.15 Keck Observatory and their news page

Edited May 26, 2006 by Guest

[cartographer]

Bumping an old question here which never got answered. Anybody have an idea?

[LaRetta]

The problem is that a straight alpha sort will not work; nor will a straight number sort. You get:

10.15, 10.16

10.4

1.11

1.9

8.12

8.2

... and you want:

01.09

01.11

08.02

08.12

10.04

10.15, 10.16

I suggest that you create a calculation (result is text) which turns BOTH sides of the decimal into two characters like:

Let ( string = Substitute ( indexField ; "." ; " " ) ; Right ( "00" & LeftWords ( string ; 1 ) ; 2 ) & "." & Right ( "00" & MiddleWords ( string ; 2 ; 1 ) ; 2 ) )

When the records are sorting correctly (by this indexable calc) then the record numbers no longer matter because the record order will adjust itself during the sort, right? I don't believe you have to worry about that second index number (10.15, 10.16) at all because it couldn't be split (and sorted) anyway. This process will self-run and you can still display the original field instead of the calc. Let us know if this moves you closer to what you need.

LaRetta