1 2 3 Previous Next 76 Replies Latest reply on Jun 2, 2014 6:37 AM by jmci

    How Can I Show a Running Balance in a Portal?

    jmci

      Two of the tables in my current project are Account and Transaction. Every Account may have 0, 1, or many related Transactions. There is a Register layout based upon an occurrance of the Account table and it contains a portal to an occurrance of the Transaction table. Three of the fields in the Transaction table are "Credit", "Debit", and a calculation field "zc__Net__n" the formula of which is Credit - Debit.

       

      On the right side of each row in the Transactions portal I want to place a field which shows a running Balance for the account. For example, if the value of the field zc__Net__n in the first transaction was $1,000.00 I want the Balance to show the value $1,000.00. If in the next record the value of Credit = "" and the value of Debit = 20 the value of zc__Net__n will be -20.00 I want the Balance to show the value $980.00.

       

      I tried to do this by creating a summary field Transaction::zs__Balance__n which is a running total of Transaction::zc__Net__n but that didn't give me the result I wanted. Can anybody make a suggestion?

        • 1. Re: How Can I Show a Running Balance in a Portal?

          I don't believe Filemaker is designed to do this as it will in a report.

           

          That said, you might do a little work and when you create each related record you do a sum of the found set and set field to that sum, not a calculated field.

           

            go to portal row one

            set var to number field

            set running total field to var

           

            go to portal row two

            set var to var + number field

            set running total field to var

           

            go to portal row three

            set var to var + number field

            set running total field to var

           

          You can adjust the idea for your own script using a loop.

           

          You could use a button, an on open layout trigger, or a modified layout trigger.

          • 2. Re: How Can I Show a Running Balance in a Portal?
            mikebeargie

            You can do this easily with ExecuteSQL calls. here's a quick and dirty demo of such. You can probably come up with a better way to do the record numbering that's required to identify the portal rows (an auto-enter serial would work for instance).

            1 of 1 people found this helpful
            • 3. Re: How Can I Show a Running Balance in a Portal?
              erolst

              jackrodgers wrote:

              […]

               

              All that is completely unnecessary.

               

              The method as described by the OP does work, if implemented correctly – not that this is difficult; there must be a minor detail she/he overlooked.

               

              See attached:

              1 of 1 people found this helpful
              • 4. Re: How Can I Show a Running Balance in a Portal?

                Beautiful..

                 

                The natural assumption is that running totals won't work in portals (not to everyone of course) and you showed that it does.

                 

                I tried sorting the portal and the field still works.

                 

                Maybe I can pick up an hour or two of paid work using this... 

                 

                I am always pleased when someone shows me to be wrong...

                • 5. Re: How Can I Show a Running Balance in a Portal?
                  beverly

                  This absolutely should work.

                   

                  Do you have the fields making the relationship keys as "indexed"?

                  Are your calculation field(s) indexed (only if necessary - I have mine unindexed)?

                  Is the summary field IN the child records (where it needs to be) or in the parent record?

                  The  zs__Balance__n needs to be in the portal row, so needs to be a child field.

                   

                  Account::accountPK = Transaction::accountFK

                   

                  portal on Account layout has these fields:

                            Transaction::Credit

                            Transaction::Debit

                            Transaction::zc__Net__n (obscure from display if desired)

                            Transaction::zs__Balance__n

                   

                   

                  Lastly, can you post a sample of what you have done (bogus data is ok), if what you have isn't working?

                   

                  You may also wish to peruse my article on aggregates in single-row portals, for other ideas:

                  <http://filemakerhacks.com/2012/09/13/aggregates-in-filtered-portals/> this works for filtered and unfiltered portals, BTW

                  Beverly

                  • 6. Re: How Can I Show a Running Balance in a Portal?
                    jmci

                    Actually, in order to keep my original question brief and not too complex I left out a couple of issues. It seems that one of those issues is the cause of my problem. Erolst, your very helpful sample app made this clear. What I failed to mention is that I have the portal sorted in descending order so that the most recent child records appear at the top. When I modify the relationship between Accounts and Transactions in your sample file so that Transactions is sorted by the field theDate in descending order, the field sRunningOfSigned displays incorrect results just like my system. When I sort my own portal in ascending order I get the correct results in my summary running total field.

                     

                    I guess the real question here is will a running total summary field work when the portal is sorted in descending order?

                    • 7. Re: How Can I Show a Running Balance in a Portal?

                      There are two places to sort a portal, one works and one doesn't.

                       

                      If you place the sort after you double click on the portal object it will work. If you go into manage database and click on the join object and put the sort there, it probably doesn't.

                       

                      I checked that on his sample file and it worked when I sorted by clicking on the portal object.

                      • 8. Re: How Can I Show a Running Balance in a Portal?
                        erolst

                        jackrodgers wrote:

                        one works and one doesn't.

                        […]

                        it will work […] it probably doesn't.

                        jackrodgers wrote:

                        45. Sep 9, 2013 6:29 PM (in response to ptrc)

                        Re: Technet trend - outsourced developers?

                        I think 50% or more of the advice I see on lists is not very good advice and often given by people who don't understand the problem. I seldom do that, of course.

                         

                        I rest my case …

                        • 9. Re: How Can I Show a Running Balance in a Portal?
                          jmci

                          Jack, I'm not exactly sure what you mean. In erolst's app he has set the Transactions table to sort in the Edit Relationship dialog for the relationship between Accounts and Transactions. When you talk about double clicking the portal I assume you're pointing out that we can alternatively set the sort order in the Portal Setup dialog. After reading your message I did set the sort order to theDate field in descending order in the Portal Setup dialog but still experienced the same erroneous result.

                          • 10. Re: How Can I Show a Running Balance in a Portal?
                            jmci

                            Mike, you're solution worked perfectly. I created a field named Serial and populated it with the values 1 thru 5 and sorted the Transactions table on the Serial field. Whether I sorted it in ascending or descending order the field runBalance gave the correct result. I only wish I understood your formula in the calculation field runBalance.

                            • 11. Re: How Can I Show a Running Balance in a Portal?
                              erolst

                              jmci wrote:

                              I guess the real question here is will a running total summary field work when the portal is sorted in descending order?

                               

                              I guess the answer is “no” – which means you need do it yourself. See attached, and note that the relationship is sorted.

                               

                              Note also that you need a finer-grained field for the sort if there are several entries for the same date; a timestamp should do nicely.

                              • 12. Re: How Can I Show a Running Balance in a Portal?
                                keywords

                                I don't know why you experience erroneous results using the technique in erolst's sample file. I've tried numerous ways of sorting the portal itself and the running total is always correct—regardless of whether the sort is at relationship level or portal level. Just remember that the portal level sort will override the relationship sort.

                                • 13. Re: How Can I Show a Running Balance in a Portal?
                                  erolst

                                  I think the problem is not the sorting as such, but the sort order. If you sort descending, the values generated by a (Achtung, pun!) 'run-of-the-mill' summary field are indeed incorrect.

                                  • 14. Re: How Can I Show a Running Balance in a Portal?
                                    keywords

                                    Not in my testing of your own file. The running total is always correct.

                                    1 2 3 Previous Next