Help: Using a Related Record Count to assign a report number
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
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 */
/* this works great and is a running count of related records to the main report record */
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:
What I am getting is this:
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!