Solved

Custom Serial Numbers in Filemaker Report Layout

Posted on 2014-11-11
9
291 Views
Last Modified: 2014-11-27
Hi,

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..
0
Comment
Question by:Ringah
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:Will Loving
Comment Utility
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.
0
 

Author Comment

by:Ringah
Comment Utility
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..
0
 
LVL 24

Expert Comment

by:Will Loving
Comment Utility
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.
0
 

Author Comment

by:Ringah
Comment Utility
Greatly appreciated, hope the attached will be sufficient, contains all the key elements referenced in my last post..full access account is 'admin'..
projects.fmp12
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Assisted Solution

by:Will Loving
Will Loving earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:Ringah
Comment Utility
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..
0
 
LVL 24

Accepted Solution

by:
Will Loving earned 500 total points
Comment Utility
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.
Projects-v2.fmp12
0
 

Author Comment

by:Ringah
Comment Utility
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..
0
 

Author Closing Comment

by:Ringah
Comment Utility
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..
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now