Maximum prior value of text field

[Christopher_Campbell]

I keep a "worklist" of final photographic prints in Filemaker and assign each work a number in the form "class.year.sequence.frame," such that a given work might look like "3.2006.021.9774" and I would like to have a calculation field display the prior maximum value. I know that because I've used periods as separators, the value will display as 3.20060219774, which is a little awkward, but still usable. However, when I created a calculation field called "Worklist_max" and defined it as: Max (Worklist number), it only displays a value in records where I have already assigned a worklist number. What I would like it to do is to show me the highest prior value when I create a new, empty record. How do I need to modify this field? Thanks.

[DukeS]

Max() function works on related fields, repeating fields or two or more table field.

You must create self relationship with carthezian product (x) in relationship. Then you can use Max() on related field.

So if you have field Number you write Max(RelatedTable::Number)

[dwins]

"How do I need to modify this field?"

The best mod is to delete it.

To find the maximum value, do a SORT.

You can use a script to do all the work, ie., find max, place it in new field, change number sequentially, etc.

[Christopher_Campbell]

DukeS, I'm afraid your advice is far too cryptic for me to be able to make use of it at all. My database is a simple flat file with about one hundred fields. The field for which I would like to find a maximum is a text field, "Worklist number," with a form that I specified above. I have no related fields, no repeating fields. A quick scan of the index of Lane, Bowers et al Using Filemaker 7 shows no entries for "Cartesian" (I assume that's what you meant?). Could I please ask that you be quite specific in laying out exactly what fields are necessary to get from my simple number to the Max calculation field? I'd appreciate it very much.

[jteich]

FileMaker is able to count! 1, 2, 3 and also

3.2006.021.9774, 3.2006.021.9775, 3.2006.021.9776 and so on.

Activate the field option 'serial number' for your working number and set the next number to 3.2006.021.9775 or whatever you like!

-jens

[Christopher_Campbell]

Hi Jens, I had considered that option, but the trouble is that not all records in the database get "worklist" serial numbers. Some records are of prints made for clients, and some of my work doesn't turn out well enough to get a number and stand as part of my exhibitable corpus. It is precisely because I can't simply look at the prior record, or simply increment serial numbers, that it would be useful to display the max value for the field.

[DukeS]

Self join relationship is made when you drag for example tableID out of the table and back in the same table on the relationship graph. So you connect tableID with tableID with same table ( named table1 and table2 for example ). Then you double click in the square between tables and change relationship from (=) to (x). Then you can Max ( Table2::Field ) and you will get the result.