9 Replies Latest reply on Dec 9, 2011 7:26 AM by Mike_Mitchell

    Keeping balance updated

    larsheise

      I really hope to get a hint, that could optimise my solution.

       

      I have 2 tables. One for entering posts and the other with all the registered posts/records.

       

      a post looks somehow like this:

      date account text amount

       

      When the user has entered all the posts my script takes one after one and puts them into the other table. My problem is to find an effective way to update the balance for each account.

       

      Today I do like this

      I go to all the related records ( same account no.)

      Make a new record and register the record via variables

      Then I sort by the date and goes to the last record

      Then I loop back until I get to my recent post - ( balance is empty)

      go one further back to get the balance of that record

      and then I can loop back and update the records

       

      ( the other way I have tried is to sort by date and do a replace with a summary running total)

       

      I find both my methods too slow with lots of records over Lan - not to mention wan.

       

      There must be a more effective solution to this ??

       

       

      Thanks

      Lars

        • 1. Re: Keeping balance updated
          usbc

          Hello Lars,

          While it's always possible for me to misunderstand the question:

          I'll assume that you have a relationship "Accounts" based on a unique Account_ID.

          If so then you could consider a single unstored calc field resulting in a number. Sum(Account::amount)

          No looping.

           

          Chuck

          • 2. Re: Keeping balance updated
            BruceHerbach

            Hi Lars,

             

            You may be able to do this by adding another TO to the relationship graph for the registered Posts table.  Match on the account number and have the balance field Sum the amount field. 

             

            Another method may be to add an accounts table with a field for balance.  Again match on accout number and sum up the amount field from the posts table. 

             

            Hope this helps. 

            Bruce Herbach

            • 3. Re: Keeping balance updated
              larsheise

              Thanks for the input

               

              This is just like a normal bank account, where I need a balance after each post

               

              Date          Text          Amount          Balance

              Dec 1         xxx          500                    500

              Dec11          yy          -200                    300

              dec 18          zz          150                    450

               

              Now I need to insert this record:  dec 8   xyz    100

               

              So when sorted the table afterwards will look like this

              Date          Text          Amount          Balance

              Dec 1         xxx          500                    500

              Dec 8          xyz          100                    600

              Dec11          yy          -200                    400

              dec 18          zz          150                   550

               

              These numbers are also shown in portals different places, where the user might want to se only posts from dec. 15 - dec 31, so therefor I cannot see how to solve this with a calculation.

               

              I also cannot se a way to make a relationship. I know the dates and account no. - but I do not know the date of the previous post ( in the example above it is dec 1), from where I can pick up the balance.

               

              Hope this clarifies my problem, and someone finds a better solution, than I did in my original question

               

              Thanks

              Lars

              • 4. Re: Keeping balance updated
                Mike_Mitchell

                Lars -

                 

                Could you do this with a Script Trigger on data entry? For example, take the most recent balance, add (or subtract) the amount in the current transaction, and then add it to the Balance field as the user commits the record?

                 

                Mike

                • 5. Re: Keeping balance updated
                  larsheise

                  No I am afraid that is not possible.

                   

                  The balance is only interesting, when it has been entered into the "registered table" vi my script.

                  The user is typing into what I would call a draft table.

                   

                  On some new posts where the date is the most recent I can use the current balance and then just add or subtract, but if the new post has to fit in between some other posts (due to the date), I have to update all the posts that comes after.

                   

                  Thanks

                  Lars

                  • 6. Re: Keeping balance updated
                    Mike_Mitchell

                    I'm a little confused why this would be very slow. I do something similar on a couple of systems, and it's quick. How many transactions are we talking about? And are you doing them one at a time, or do you put all the batched transactions into the final table, then update the balances?

                     

                    In other words, which way are you doing this:

                     

                    Method 1:

                     

                    1) Grab transaction 1 from draft table.

                    2) Create new transaction at appropriate point in final table.

                    3) Loop through all newer transactions in final table, updating balance.

                    4) Repeat steps 1 through 3 for all remaining transactions.

                     

                    Method 2:

                     

                    1) Import all draft transactions into final table.

                    2) Loop over all transactions from oldest (new record) to newest, updating balance as you go.

                     

                    I think method 2 might be considerably faster.

                     

                    Mike

                    • 7. Re: Keeping balance updated
                      RubenVanDenBoogaard

                      Or you could apply some witchcraft and create a dynamic balance based on the selected portals.

                       

                      see example.

                       

                      Best regards,

                       

                      Ruben van den Boogaard

                      Infomatics Software

                      ruben@infomatics.nl

                      • 8. Re: Keeping balance updated
                        larsheise

                        My system is a bit more complex, so therefor I cannot do an import.

                         

                        Actually one line in the the draft table can become 2 or more lines in the end table. I have a debit account and credit account and often VAT accounts in one line.

                         

                        I just tried to boil it down and just focus on what I see as my main problem.

                         

                        And yes there can be many transactions - 40 lines in the draft table that becomes 100 in the end table, which can have several Thousand records for each account.

                         

                        I had hoped that I was missing some math or function in FileMaker that could do this a lot faster.

                         

                        Lars

                        • 9. Re: Keeping balance updated
                          Mike_Mitchell

                          Lars -

                           

                          Unless there's something about your solution I don't get, I'm not sure imports won't work. You can do multiple imports to get the same source record into the destination table multiple times. Just isolate the source set by type (debit, credit, VAT, etc.) and import as many times as needed.

                           

                          What I'm thinking you might be able to do looks something like this:

                           

                          1) Isolate the source transaction set by type

                          2) Import to the appropriate destination account (debit, credit, VAT, whatever)

                          3) Find based on the earliest date in the source set

                          4) Go to the earliest transaction and update the balances on a forward-fit basis.

                           

                          This will mean passing through the records only once (or once per account type), which will be much faster than running all the calculations 100+ times.

                           

                          What am I missing?

                           

                          Mike