Custom Serial Numbers in Filemaker Report Layout
Posted on 2014-11-11
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..