4 Replies Latest reply on Jun 20, 2014 11:53 AM by taylorsharpe

    Regarding opening balance

    samarthmkt

      dear Sir,

      Clearly the closing balance for the end of the month becomes the opening balance for the beginning of the next month. But Filemaker cannot take the value from record 1 closing balance and place it into record two opening balance. How do I get it to do this without a self join which ends up crashing the programme.

        • 1. Re: Regarding opening balance
          jlamprecht

          I need some more information regarding your business workflow.

           

          Is this a manual process or an automatic process?

           

          Possible Solution:

           

          When a new month begins, perform a find for the previous month of the current year of your Balance records. Grab the closing balance from that record and store it in a variable. Then, proceed to create a new balance record for the current month and year and use that closing balance variable for your Opening Balance field.

          • 2. Re: Regarding opening balance
            mikebeargie

            You can use ExecuteSQL() or GetNthRecord() to get it. ExecuteSQL() would probably be easiest. You could also set an auto-enter value in the field using "value from last visited record" if you are on the record that needs to carry the closing balance over when the new record is created.

            • 3. Re: Regarding opening balance
              wimdecorte

              samarthmkt@hotmail.com wrote:

              How do I get it to do this without a self join which ends up crashing the programme.

               

              A self-join is not the asnwer here, but it should also not crash the program.  At what point does FM crash?  This could be an indication of the health of the file...

              • 4. Re: Regarding opening balance
                taylorsharpe

                I guess I don't see why FileMaker can't go from one record to the next to store the ending balance in the new month's starting balance. 

                 

                You could create a field that stores YYYYMM and a second field that is YYYYMM - 1 (have to adjust end of year issue) and then do a join on those first and second fields and have a summary field that calculates the ending value or something like that, but what a pain. 

                 

                Mike's two suggestions work well.  The GetNthRecord is fun for grabbing values from another record in a found set, but I would probably do the ExecuteSQL like Mike suggests. 

                 

                Maybe something like this:

                 

                Let ( [

                 

                F1 = DateFieldOfThisMonth ;

                F2 = Date ( Month ( F1 ) - 1 ; 1 ; Year ( F1 ) ) ;   // Last Month's Starting Date

                F3 = Date ( Month ( F1 ) ; -1 ; Year ( F1 ) ) ;  // Last Month's Ending Date

                F4 =  "SELECT

                               \"Balance\"

                          FROM

                               TransactionTable

                          WHERE

                               TransactionDate >= ? and TransactionDate <= ?

                          ORDER BY

                               \"Serial\" DESC

                          FETCH FIRST 1 ROWS ONLY" ;  

                               //  This assumes you have a primary key serial number that increments and the highest serial number is the last balance value

                               //  This could also be based on something else like highest creation timestamp. 

                F5 = ExecuteSQL ( F4 ; ¶ ; ¶ ; F2 ; F3 )

                 

                ] ;

                 

                F5

                 

                )