Jump to content

Non-Unique Sequence Numbers for Related Records

This topic is 5695 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies


I have what turns out to be a tricky problem.

I have a solution that manages Jobs, and for each job, many Tasks. I wish to record for each task a sequence number that is unique among all the tasks for the same job, and which starts at 1 for each new job. Thus, the sequence numbers are not unique among all tasks, but are unique within the set of tasks for a given job.

I have two ways of doing this, each of which has its shortfalls. I am looking for ideas of another way that is more reliable.

The first way is theoretically the best, but works out to be impractical. This approach uses a self-join between the Tasks table (joining on the unique Job ID) to derive the set of all tasks for the current job.

I can easily get the next sequence number by setting the TaskSequenceNumber field to use an Auto-Enter calculation of


The problem of this approach has to do with the record-committing behavior of FMP 7 and 8.

I am entering records in a portal on the Jobs layout (the portal shows Task records). If I begin entering tasks, and keep tabbing from field to field, the records never get committed, and so the value of max(TaskSequenceNumber) never changes, until I click outside the portal. This results in all the records entered in a given "run" to have the same sequence number. Yuck.

The second approach is workable and pragmatic, but seems to be failure-prone to at least a small degree (i.e., I have seen records for which this approach has failed, but I can't say why).

This approach is simply to use for the TaskSequenceNumber field an Auto-Enter calculation of


This is elegant and practical, but useless if you're not entering records in a Portal. Also, as I said, it appears under some (as yet undetermined) circumstances to produce a "wrong" (i.e. unwanted) value.

Can anyone suggest a different approach that is not susceptible to the kinds of failures I've seen so far? Any assistance would be greatly appreciated.



Edited by Guest
Link to comment
Share on other sites

I tried a lot of techniques like lookups and recursive custom functions but nothing seems to get around the issue of uncommitted records. It used to work in versions previous to FileMaker 7 because I used lookups to locate the previous related record to auto-fill a portal with the most recent entry. I believe this has to do with the way FileMaker 7 and later commits records and it's new revert record feature. In other words, the index doesn't update until you commit a record.

Have you considered using a script to add records to the portal. Within the script, you can commit the record. Of course, this will remove the ability to completely do data entry via the keyboard but it may be the best option.

Link to comment
Share on other sites

Tried. Failed to find an answer.

Now, how about "manually" entering in the task number as part of the process, or using a script to generate the required number of new task records, after which you can tab through and do your data entry.

Link to comment
Share on other sites

It just occurred to me that you can use a simple auto-enter calculation or calculation field with the Get(RecordNumber) function. Or, you could just use "@@" in a text block to display the record or portal row number. Sometimes the simplest solutions are the best.

Link to comment
Share on other sites


Well, I finally picked up my copy of the FM8 functions and script steps and read up on "Get(RecordNumber)".

Nifty. Learn something new everyday. Ignore my previous posts. I was thinking Get(recordnumber) got the absolute position of the record in the total set of all records in that table. Apparently, it sticks to getting the relative position of the record in the "found set", which is exactly what the original poster needs to do rapid, tab to next field, data entry without bothering to commit for the serial number updates after every row.


Link to comment
Share on other sites

I would have thought the same thing as coconutt2000, but when I tested the example I made on my computer, auto-entering the record number into a field works fine. It doesn't look at all the records, just the records in the portal as long as I am entering the record through a portal. When I enter them directly into the table, it auto-enters the record number of the found set.

I learn something new every day!

Link to comment
Share on other sites

This topic is 5695 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.