AnsweredAssumed Answered

Help: Using a Related Record Count to assign a report number

Question asked by RonDeLosReyes on May 13, 2015
Latest reply on May 13, 2015 by RonDeLosReyes

Title

Help: Using a Related Record Count to assign a report number

Post

Filemaker Pro Advanced 13.0 v5
Windows 8.1 64bit

Table1 is main report
Table2 is supplemental report

Relationship - Table1::FK_ID = Table2::PK_ID

Table1::ReportNumber
Right(Year(Get(CurrentDate));2) & Right("0" & Month(Get(CurrentDate));2) & Right("0" & Day(Get(CurrentDate));2) & "-" & Pk_ID
/* this is a calculated number based on date and record ID number,  ex: 150512-125 */

Table1::RelatedRecordCount
If (IsEmpty(Count(Table2::Pk_ID));0;Count(Table2::Pk_ID))
/* this works great and is a running count of related records to the main report record */

Table2::SuppReportNumber
Table1::ReportNumber & "-S" & Table1::RelatedRecordCount

/* this is a calculated number based on Table1::ReportNumber & “S” & number of related supplement reports,  ex: 150512-125-S1 */

A properly working list would look something like this:
150512-125
     150512-125-S1
     150512-125-S2
     150512-125-S3
150512-126
     150512-126-S1
150513-127
     150513-127-S1
     150513-127-S2

What I am getting is this:
05122015-125
     05122015-125-S3
     05122015-125-S3
     05122015-125-S3
05122015-126
     05122015-126-S1
05132015-127
     05132015-127-S2
     05132015-127-S2

I know this is due to the supplement report number picking up the running count of related records.  What I am failing to comprehend is how to prevent that.  I have tried a Lookup field so it only get the related count at the time of supplement creation, but the next supplement report added changes all supplement numbers to the total count.

It's most likely something simple I am missing, or a field (global or not) that needs to be added to the calculation... my brain is simply blocked and refuses to see the solution.

Any help would be greatly appreciated!

-Ron

Outcomes