Search code examples
filemaker

renumbering a ID field

Very new to Filemaker. Using Filemaker 11 pro.

I was wondering if it was possible to renumber an ID field column after doing an insert new record in between records? Maybe using a script trigger?

thanks

Solution

  • I agree with Jesse that renumbering a record's unique ID/Index doesn't sound like a good idea and more information or an example of what you're trying to do would help.

    If you simply want to display the Record Number, you could create an unstored calculation field with the calculation "Get ( RecordNumber )". This should always display which record, of the found set, is being displayed. FileMaker's definition is available here: http://www.filemaker.com/help/html/func_ref2.32.55.html

    If, on the other hand, you're trying to make a unique sort order for the records being shown, it is best to do this by creating a new SortIndex numeric field. (You'd need to make certain that the layout that you're displaying was always sorted by the SortIndex field which can be done using layout script triggers and, possibly, overriding the Sort menu commands using Custom Menus.)

    One method to consider would be overriding the New Record command using Custom Menus. When New Record is selected you might route it to a script which does something like this:

    1. Get the current SortIndex value
    2. Get the value of the SortIndex for the next record (by creating a self-join which shows records where the SortIndex > the SortIndex of the table occurrence your view is based on)
    3. Place the average of those two values in a $variable
    4. Create a new record and set its SortIndex to $variable