Custom Serial Numbers in Filemaker Report Layout


I have a report layout in Filemaker which lists projects, any main project may have one or more sub projects which are text and not as easily identified as if a decimal system were used (eg 1.00 main job, 1.1 1.2 sub jobs etc)..

The data was inherited from a legacy system where a grouping number field had been assigned, such that when a new project is entered the grouping number is auto incremented by 1, when a sub project is entered the grouping number is manually changed to the same as the main project.

So I can up with a cludge where I added a calculation field that detected whether GROUP NUMBER in current record was different to GROUP NUMBER in previous record.  If TRUE then a script would use the replace record function taking the previous serial number for the found recordset and add 1, or not if no change in group number had been detected.

Cludges being cludges this works when the replace record function is used as this triggers recalculation of the stored value (the custom serial number for the report).  From various posts I believe that when GET is used, which it is in this calc field, the latest values from the calc field which detects the shift may not be returned as no recalc triggered...

Which is when I spotted that the Report Layout script has a Find applied in the launch script; when the replace records script is run manually from the Browse view of the report layout,  to sequence the project serial number for the report it works fine...but when I call the sequence script from inside the script which launches the projects report layout, it seems to sequence ALL records, rather than just the FOUND set, causing big jumps in the sequence number on the report..

Hoping that the slightly long winded description makes sense, am guessing there could be a relational element to a more robust solution which accomodates changes in the FOUND recordset...and/or probably a scripting element too...rather than relying on the GET in the underlying GROUP NUMBER shift detection calculation field which doesnt robustly accomodate all usage scenarios where the underlying recordset could change in using the report layout in practice.

But hit the wall, as ever any insights or suggestions greatly appreciated...and a succinct and more technically accurate reference solution would be a great addition to the material out there already on this topic..
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:
It sounds like all your projects and sub-projects are in the same table so I'm going to take a guess here that what you really need is a self-join relationship between the records in this table based on the Main Project ID. In other words, there need to be two fields, the Project ID and the related Main Project ID if the record is in fact a sub-project related to a Main project.

When running a report, all related records can be grouped together. A sort order, whether based on the Project ID or on a separate Sort Order field, if you need to manually manipulate the order, can determine the order in which these records appear within the grouping. In general, I would recommend NOT renumbering the unique project ID of each record but using a separate Sort Order field to accomplish a manual or user determine sort order.
RingahAuthor Commented:
Thanks Will - I'll restructure for the self join and see how the recordset and sequence number turns out...

..the existing Grouping Number works fine, manually adjusted, for general sorting and to identify sub projects as related to the main project,  and there is a separate UID for each record which I don't use at all for anything else..

..the sticky bit is displaying a sequence number on the report which doesn't change when a sub project is displayed after the related main project, because the way I cludged this was to:

1.  create a calculation field (Seq_Shift) which STORES 1 if the Grouping Number from last record has changed and 0 if it hasn't:

If ( Projects::Group_Number  ≠  GetNthRecord ( Projects::Group_Number ; Get ( RecordNumber ) - 1 ); 1 ; 0  )

2. Then a second calculation field STORES the value from previous record sequencing number field:

GetNthRecord ( Last_Seq ; Get ( RecordNumber ) - 1 )

3. A script then loops through each record Replacing Field Contents with Last_Seq + Seq_Shift.

Ignoring the need to RUN the script manually for the present, I have a nagging feeling this approach is neither a suitable approach in FM not a suitable script that would re-sequence the found recordset whenever it changed, rather than relying on a manual RUN of the script which Replaces Field Contents thereby incidentially triggering update of the calc fields, which in turn returns the intended sequence number for any given recordset using GET functions..

But there I hit the wall, I'll run through it again, and if necessary look deeper into any custom scripts or plugins that might smooth this out, and post an update..
Will LovingPresident, Dedication Technologies, Inc.Commented:
I get the overall picture of what you're describing but I think this is a place where I could give a better response if you posted a sample file with some test data that shows exactly what you've got and are attempting to do.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

RingahAuthor Commented:
Greatly appreciated, hope the attached will be sufficient, contains all the key elements referenced in my last post..full access account is 'admin'..
Will LovingPresident, Dedication Technologies, Inc.Commented:
Looking at your file and the last script, the loop doesn't do anything after the first time through. The Replace step changes all records but after that first time there is nothing to change regardless of the number of records. Also, the Grouping and Grouping Number fields also don't see to be used or part of the process. And finally, there is no apparent Sort order within the sub-group so it just defaults to creation that what you want?

Or more specifically: What sort order do you want to use for Projects and for sub-Projects within the Project listing? I think a self-join is what you need but I have to clarify your goals first.
RingahAuthor Commented:
Got it, so assuming we have no self join for the present, we can disregard Grouping for this exercise.. refine the cludged approach I started with, I removed the loop and changed the first Replace Field Contents to '1' with Insert Calculated Value = 1 at Record 1 to tidy up, then Gotonext Record..

..then Replace Field Contents with calculated ( Last_Seq + Seq_Shift ) so that when we move from one main project to another between records the SEQUENCE number on the report layout increments by one, unless the Grouping Number hasn't changed because its a sub project..

..then sort by Category, Grouping Number, and Finally Project No, which with this dataset works fine..because any Sub Job will have the same Grouping Number (manually tweaked) as related Main Job...and the way sub job values are entered doesn't present any further sorting issues..but script still has to be manually triggered by button each time the found recordset changes.

Ultimately goal is not to use the auto calculation fields Last_Seq and Seq_Shift which can result in FM using the creation order if neither input values to the calculations have changed resulting in out of SEQUENCE numbers on the report layout..

..instead to have a script which loops through each record in the current found recordset, checks if the current Grouping Number is the same as that in Previous Record, incrementing SEQUENCE by 1 if TRUE or by 0 IF FALSE, then sort by Category, Grouping Number, and Finally Project No...

Hope this makes sense..many thanks..
Will LovingPresident, Dedication Technologies, Inc.Commented:
I'm still trying to get a better sense of what your goal is rather than how you're trying to do it now. I think what you want will be accomplished by a combination of a Find for only those records that are Primary Projects (meaning those with Sub-Project), a sort of those records by Status and whatever other criteria you want, and a Sorted portal that displays the related sub-projects.

If you want to be able to change the order of the sort in the portal you can include a Sort Order field and modify that within the portal. I sometimes put small arrows that run simple scripts which will do the re-numbering for you and move a particular portal row up or down in the Sort order.

I've attached a modified file which contains a Self-Join relationship based on the Project ID and a new field for each record called the Primary_Project ID which identifies the Project to which a sub-project is attached. Primary projects will have a portal record showing all related sub-projects. The portal is sorted at the Portal level (go into layout and double-click on it) but you can also sort at the relationships level.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RingahAuthor Commented:
Thanks Will...I can now understand from the example file how the self join applies, the primary project ID field comes into play, and the arrows work much better in the portal scenario which is an added bonus...this is fundmentally a much better design and achieves the goal of getting rid of the autocalc fields...

Think may still need to modify  the script which increments a serial number for each main Project record on the found set underlying the Projects Report layout, but I will restructure first as you suggest, and post it here for future reference if it proves necessary.

Thanks again for all your help on this, very much appreciated, and hope it will prove useful as a reference solution..
RingahAuthor Commented:
Not a mainstream need, but extends nicely and addressess all main elements for anyone working in custom report numbering, particularly in a professional services sector context with FileMaker Pro.  Thanks to Will for his insights on the key elements amidst some rather long winded explanations..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.