Serial Numbers

[Matthew Williams]

Hi

I'm fairly new to FM, though I do have some programming experience. I'm working in FM5.

I'm trying to create a db that tracks different kinds of art in text books. Here's what I would like to happen:

A user creates a new record (for a new art) and indicates the type of art (Figure, Photo, Table, etc.) and the chapter in which it falls. From that information, the db should be able to calculate and suggest an art number. For example, the first photo in chapter 1 would be assigned the art number P01-01; the first table would be assigned T01-01, etc.

I can get the T01- part okay, but I can't get the db to find the highest value in the serial number field and add 1 to it. Everything I try results in a circular reference error.

I downloaded a sample that looked like it might solve the problem, but it was written in FM3; when I try to view or edit the field with the proper calculation in FM5, I get the same circular reference error and can only cancel out of the calculation dialog box.

Any help getting this problem resolved would be REALLY appreciated.

Matthew

[Vaughan]

Did you try using the Max[] function in a calcultion field? Or creating a summary field and specifying max there?

Depending on what happens you may have to create a self-join relationship to help sort out the different chapters and books etc from each other.

[Matthew Williams]

Hi Vaughan

Thanks for the reply. I have tried using Max, but I still get the circular definition error. Perhaps I'm using it incorrectly; could you elaborate?

Let's say that I have a field called Figure Number that must increment by 1 each time a new figure is added (Can't use the auto serialization because it can only increase; rather, I need to find the highest value and add 1 to it). What would my calculation be, and what other fields would I need?

I've tried setting Figure Number to a calculation of Max(Figure Number)+1, but that gives me the error.

Thanks again for your help.

Matthew

quote:

---

Originally posted by Vaughan:

Did you try using the Max[] function in a calcultion field? Or creating a summary field and specifying max there?

Depending on what happens you may have to create a self-join relationship to help sort out the different chapters and books etc from each other.

---

[Matthew Williams]

Ooh! I hadn't thought of using a script to override the normal new record command. I'll try it.

Thanks!

[LiveOak]

Eliminate the use of autoenter and write a script to create a new record. Have the script set the Figure Number field to Max(Figure Number) + 1 using a SetField() step. -bd