2 Replies Latest reply on May 13, 2015 5:04 PM by RonDeLosReyes

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

    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

        • 1. Re: Help: Using a Related Record Count to assign a report number
          philmodjunk

          The simple fact is that your calculation is producing a total, not a running total and thus all the related records get the same value.

          I do not recommend that you use these values to match records in relationships.

          You might try changing your SuppReportNumber field from a field of type calculation to a field of type number with an auto-enter calculation. It will then capture and keep the current value at the time the record is created and this should then capture a different value for each supplementary report record.

          PS. it looks like you have your PK and FK field names reversed. a PK should be the name of a field that stores unique values and FK stores possibly non unique values on the "many" side of a relationship matching to the PK field. You show many records in Table 2 for 1 record in Table 1 so it seems that the match field in Table 1 is the Pk not the field in Table 2 that matches to it.

          • 2. Re: Help: Using a Related Record Count to assign a report number
            RonDeLosReyes

            Both of your suggestions solved the issue:

            I reversed the PK & FK in the respective tables, and then switched from a Calculation field, to a text field with an auto-entered calculation, and it works as long as the supplement report is committed before a new supplement report is created - which I will force.

            Thank you very much!