9 Replies Latest reply on Nov 30, 2016 10:20 PM by TKnTexas

    Need to fill a field in Parent from Child records

    TKnTexas

      I have field in a parent table that is manually being captured by visual inspection of child records.  Need to have a solution when I go back to work on Monday.  SO HELP!!
      I have a table of disbursement checks written from our accounting software, a check register.  We use it to document when checks written actually are disbursed (mail, overnight, or handed out) as well as the dates.  I can query what checks are still being held.  This is the problem.

       

      The owner wants to know what are the due dates of the invoices that are being paid on these checks.  Each check could have one invoice on the remittance (child table) or 250 (or more).  Checks are listed on the remittance oldest to newest.  What I need to do, on the found set of child records is to capture these due dates to populate a field on the parent with a single due date for one invoice being paid, or mm/dd-mm/dd for the list of checks. 

       

      The current "manual method" is not acceptable.

       

      I am thinking:

       

      1)  Read the child, capture the due date in a variable.

      2)  If the next record is the same check, move to the next record, loop until a new check is reached.

      3)  If the next record is the last, append the due date and copy variable to a field.

       

      If the child is a single invoice, how to ?

       

      Welcome all solutions.  I have a chance to really show upper management the power of FileMaker Pro.  So far it has been an internal tool for accounting, primarily me benefiting.

        • 1. Re: Need to fill a field in Parent from Child records
          erolst

          If you need to (or more to the point, can) regard the complete related set of child records for each cheque, simply apply

           

          Replace Field Contents: target field: listOfRemittanceDueDates; calculated result: List ( Remittance::dateDue )

           

          to the found set of Cheque records.

           

          If you need to examine a sub-set, there is an easy method.

           

          Create a summary field of type List of: the due date, and another one of type Count of: the primary key. Write this script

           

          # [ you have the found set ]

          Sort Records [ by chequeForeignKey (and due date, if you like) ]

          Go to Record [ first ]

          Loop

            Set Variable [ $nextGroupAt ; GetSummary ( sCountOfPrimaryKey ; chequeForeignKey ) + Get ( RecordNumber ) ]

            Set Field [ Cheque::listOfDueDates ; GetSummary ( sListOfDueDates ; chequeForeignKey ) ]

            Exit Loop if [ $nextGroupAt > Get ( FoundCount ) ]

            Go to Record [ by calculation: $nextGroupAt ]

          End Loop

          • 2. Re: Need to fill a field in Parent from Child records
            Vaughan

            TKnTexas wrote:

             

            What I need to do, on the found set of child records is to capture these due dates to populate a field on the parent with a single due date for one invoice being paid, or mm/dd-mm/dd for the list of checks.

            Consider the reverse: each parent record can look at its related child records and calculate based on what it finds. Make it an unstored calculation and it will just work.

             

            Sort the relationship by date, then use the calculation expression (return text):

             

            Let(

            [

            first_date = relationship::invoice_date ;

            last_date = last( relationship::invoice_date )

            ] ;

            Case(

            first_date = last_date ; first_date ;

            first_date & " - " & last-date

            )

            )

             

            The expression needs a check for the case when there are no related records.

            1 of 1 people found this helpful
            • 3. Re: Need to fill a field in Parent from Child records
              TKnTexas

              This was perfect, and simple.  I modified it to show m/d versus full date.

               

              Then I released my child records do not have a DUE DATE field.  So I have a tweak to make and then reflect the new field. 

               

               

              The VP was very pleased that I might be able to automate this.

               

              I appreciate the quick responses to help with my problem.  I obviously need to do more study on the various functions I can use with relationships.

              • 4. Re: Need to fill a field in Parent from Child records
                TKnTexas

                Let(

                [

                first_date = relationship::invoice_date ;

                last_date = last( relationship::invoice_date )

                ] ;

                Case(

                first_date = last_date ; first_date ;

                first_date & " - " & last-date

                )

                )

                 

                Is the LAST function a feature of ver15? Or should it work in ver14? 

                • 5. Re: Need to fill a field in Parent from Child records
                  BruceRobertson

                  Last is a very old function and will work in 14.

                   

                  Note that your variable names don't match. You show last_date and last-date.

                  • 6. Re: Need to fill a field in Parent from Child records
                    TKnTexas

                    I fixed the typo.

                    I tweaked the calculation to show only M/D ~ M/D not needing the year.

                     

                     

                    Let(

                    [

                    first_date =

                    Month(GPAPData_Archive::Due Date)&"/"&

                    Day(GPAPData_Archive::Due Date) ;

                     

                    last_date =

                    Month(Last( GPAPData_Archive::InvoiceDate ))&"/"&

                    Day(Last( GPAPData_Archive::InvoiceDate ))

                    ] ;

                     

                    Case(

                    first_date = last_date ; first_date ;

                    first_date & " ~ " & last_date

                    )

                    )

                    And this is the results I get:

                     

                     

                    DueDate1 was manually filled in, the OLD WAY.  DueDate is the result of the calculation.

                     

                    I started this on my machine at work, Win10 on FMP14 Advanced.  I am on my MBPro with FMP14 Advanced. 
                    The results are not the same between the two machines.  At work, I was seeing different LAST_MONTH dates, hence the question of whether LAST() was compatible with FMP14.  Because it says repeating field, would this cause a problem since it is not a repeating field but a parent-child relationship?

                    • 7. Re: Need to fill a field in Parent from Child records
                      erolst

                      TKnTexas wrote:

                      At work, I was seeing different LAST_MONTH dates, hence the question of whether LAST() was compatible with FMP14. Because it says repeating field, would this cause a problem since it is not a repeating field but a parent-child relationship?

                       

                      Note the bolded part:

                       

                      Last

                      Parameters

                      field - any repeating field or related field, or an expression that returns a reference to a repeating field or related field

                       

                      Also, in the beginning you only mentioned the invoice date, but your formula above calculates the start date from the due date. Is that correct?

                       

                      Anyway, here's your calculation with an optimized use of variables:

                       

                      Let ( [

                        theFirst = GPAPData_Archive::Due Date ;

                        first_date = Month ( theFirst ) & "/" & Day ( theFirst ) ;

                       

                        theLast = Last ( GPAPData_Archive::InvoiceDate ) ;

                        last_date = Month ( theLast ) & "/" & Day ( theLast )

                        ] ;

                        first_date & Case ( first_date <> last_date ; " ~ " & last_date )

                      )

                      • 8. Re: Need to fill a field in Parent from Child records
                        TKnTexas

                        There will always be child records (invoice) paid on a check (parent).
                        I want to thank everyone for the assistance.

                        • 9. Re: Need to fill a field in Parent from Child records
                          TKnTexas

                          My initial table of child records did not need the due date column.  So to check the calculation initially, I used the invoice date field.  I pulled the source data from the accounting system, adding the DueDate field, importing and filling that field.

                           

                          Again, thanks to everyone.  The VP thinks I am a miracle worker, although I have credited the more knowledgable on here.