Custom Serial Numbers in Filemaker Report Layout

Posted on 2014-11-11
Medium Priority
Last Modified: 2014-11-27

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..
Question by:Ringah
  • 5
  • 4
LVL 25

Expert Comment

by:Will Loving
ID: 40436564
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.

Author Comment

ID: 40442341
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..
LVL 25

Expert Comment

by:Will Loving
ID: 40443801
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.
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.


Author Comment

ID: 40444230
Greatly appreciated, hope the attached will be sufficient, contains all the key elements referenced in my last post..full access account is 'admin'..
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 1500 total points
ID: 40446006
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 order....is 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.

Author Comment

ID: 40446685
Got it, so assuming we have no self join for the present, we can disregard Grouping for this exercise..

..to 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..
LVL 25

Accepted Solution

Will Loving earned 1500 total points
ID: 40466041
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.

Author Comment

ID: 40468705
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..

Author Closing Comment

ID: 40468707
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..

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question