3 Replies Latest reply on Dec 31, 2014 9:48 AM by philmodjunk

    Date range and transaction balances

    amtrakpdx

      Title

      Date range and transaction balances

      Post

      Dear Wizards,

      Tbl A is INSTITUTIONS (Parent), Tbl B is TRANSACTIONS (Child).
      Tbl B has fk field for INSTITUTION and also fields for Transaction Amount, Transaction Date etc. and also a calculation field for running balance.  When I do a find to isolate for an INSTITUTION the running balance is accurate but when I constrain that find to INSTITUTION AND a date range (like the last year, or current month) the running balance is no longer accurate.  How do you guys handle this situation??
      TIA

        • 1. Re: Date range and transaction balances
          philmodjunk

          It takes a bit of scripting, some added field, a global prior balance field and a new calculation field in place of the running balance summary field, but it can be done. What you have to do is use either a script or a calculation to match to the transactions for that institution that have a date  prior to the specified date range, compute that balance total and put it in the global number field. You then define a calculation field that adds the running balance sumary field and this prior balance global field. You place this field on your layout in place of the summary field and put the global field in your header to show the prior balance amount.

          • 2. Re: Date range and transaction balances
            amtrakpdx

            Thanks for the reply.  I think I understand the additional fields you're suggesting and can handle them.  I'm what I'd consider an 'intermediate beginner' with scripting (I'm taking the Lynda.com FMP13 courses).  Can you give me a clue as to what script commands I'll use to isolate that last balance prior to the date range.
            Thanks so much for your help with this!

             

             

            • 3. Re: Date range and transaction balances
              philmodjunk

              The problem with your question, is that there is more than one way to get that prior balance value. As an experienced developer who can also work with SQL queries, I'd probably use the ExecuteSQL function to get that sub total and put it into the global field.

              A different non SQL approach is to define a relationship for the purpose. Say you add another Tutorial: What are Table Occurrences? of Transactions set up like this:

              Transactions>------Institutions-------<TransActions|PriorBalance

              Institutions::__pkInstitutionID = Transactions::_fkInstitutionID

              Institutions::__pkInstitutionID = Transactions|PriorBalance::_fkInstitutionID AND
              Institutions::gDate1 > Transactions|PriorBalance::TransactionDate

              I'm using my preferred naming conventions here, there is no need to rename any fields in your system unless you want to.)

              gDate1 would be a global date field where you specify the first of the two dates that make up your desired date range.

              Your Script can then work like this:
              Set Field [Institution::gPriorBalance ; Sum ( Transactions|PriorBalance::BalanceField ) ]
              #Now perform the find for your Transactions in the date range here....

              For examples of scripted finds where the user specifies criteria in global fields such as the gDate1 field, see: Scripted Find Examples

              But there is also a third option.

              You can use a scripted find to find all transactions for a given institution with dates less than (prior to) gDate1. Your script then goes to the last record, sets the global prior balance field to the value of the running total field and then performs a second find to pull up the transactions in your date range. (this doesn't require an added relationship, but may take longer to execute when there are a lot of transaction records with a date prior to gDate1.)