Non-Unique Sequence Numbers for Related Records

[Patrick Kuras]

Hello,

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

max(TaskSequenceNumber)+1

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

get(PortalRowNumber)

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.

Thanks,

PKuras

Edited May 3, 2006 by Guest

[John Mark Osborne]

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.

[coconutt2000]

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.

[John Mark Osborne]

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.

[coconutt2000]

Now, there's an answer...

Try auto entry calc:

Get(RecordNumber)-Min(view tasks::task id)

[coconutt2000]

works fine for a "run", but then resets... probably need a more complicated calc to test for preexisting records in the found set through the portal..

[coconutt2000]

Hmmm...

Edited May 4, 2006 by Guest

[T-Square]

What about this: set up each task with a simple Auto-enter serial number, create a calculated task number that is based on the record position, and sort the list by ID?

[coconutt2000]

roflmao...

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.

Kudos!

[John Mark Osborne]

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!