10 Replies Latest reply on Aug 8, 2014 11:00 AM by synergy46

    Portal Filtering and Calculations...

    synergy46

      I am using FM 13 adv

      I have read this discussion and found it useful: https://fmdev.filemaker.com/message/87572#87572

       

      However, my situation calls for the summarizing of filtered portal information (which works... thanks for the posting) AND the combination of the summarized portal information with other fields.

       

      Here is the layout

      PortalReconcile.png

      Note: The Checks Out: and Deposits Out and summary fields located in a second portal that filters the same as the lower portal. This works great...

      However, what I need to do is: The user enters the Balance Per Stmt (yellow) and then the calculated field 'Balance Per Stmt...' subtracts 1200 and adds 100. This does NOT work when placed in our out of the portal...

       

      globals.png

      Thanks for your ideas.... Ron

        • 1. Re: Portal Filtering and Calculations...
          erolst

          One problem: Portal filtering works on the display level, functions work on the data level – which means that if you reference related data in a function, all related records are used, not just the ones in a filtered portal of that relationship (which is logical: which of possibly multiple filtered portals should be used?)

           

          This is the reason why you would see a correct display within the filtered portal, but a calculation field referencing these fields will fail (and you'd probably need a GetSummary() anyway, which only works with a field in the same table …)  

           

          One solution: give each summary field within the filtered portal an object name, then retrieve the respective value via GetLayoutObjectAttribute ( objectNameInQuotes ; "content" ).

           

          Another one (more robust): use ExecuteSQL() where you reproduce the relationship predicate(s) AND the portal filter in the WHERE clause.

          • 2. Re: Portal Filtering and Calculations...
            synergy46

            I tried your idea but did not get the desired results:

             

            FieldDefn.png

            getlayoutsummary.png

            Here are the results:

            getlayoutsummary2.png

             

            Did I misunderstand something?

            • 3. Re: Portal Filtering and Calculations...
              synergy46

              This thing is becoming unexpectedly complicated.... So, here is the problem:

               

              On my Transactions table I have a field labeld CLR which is a checkbox that is checked when a check or deposit shows on the bank statement.  These are 'cleared' items.

               

              Items (checks and deposits) that are not 'cleared' are 'Outstanding'

               

              I have succeeded in creating a filtered portal that shows CLR<>"Y" (not cleared).  And, I have a portal above the main portal that holds the summary of Checks 'not cleared' and Deposits 'not cleared'.  This works.

               

              The sticking point is wanting to use the summaryChecks and summary Deposits in a calculation with a user input field called 'Beginning Balance'.  This is where it falls apart.  FM will not allow (except perhaps with ExecuteSQL) interaction between values in a filtered portal and an input field (Beginning Balance).

               

              So, since what I am trying to do apparently is not doable in FM filtered portals, the question becomes:

              "Should I forget about using a filtered portal and use a standard transactions table that 'hides' the CLR values instead.  Or, are there complications beyond belief with that as well?  ha

              • 4. Re: Portal Filtering and Calculations...
                user19752

                Forgetting filter may be simple.

                 

                Or, if your relation is one to many, you can refer 'Beginning Balance' from portal table. So try to make calc field in portal table

                layoutsourceTable::Beginning Balance - GetSummary(Checks Out; Checks Out) + GetSummary(Deposits Out; Deposits Out)

                and put it into same filtered portal.

                 

                add..

                I skipped to read orange background image...

                Beginning Balance seems to be global, so any relation is ok.

                The calc filed should not be global to avoid saving result value. If it is global, you need to input BB every time to update result when portal content is changed.

                 

                このメッセージは次により編集されています: user19752

                • 5. Re: Portal Filtering and Calculations...
                  erolst

                  synergy46 wrote:

                   

                  FM will not allow (except perhaps with ExecuteSQL) interaction between values in a filtered portal and an input field (Beginning Balance).

                   

                  That's wrong. See attached. Be aware that though this works, it's a relatively fragile method since it depends on layout objects. Study ExecuteSQL().

                  • 6. Re: Portal Filtering and Calculations...
                    synergy46

                    I can see that for all intents and purposes your method works.  Thanks for creating and sending.

                     

                    I have looked at Execute SQL in working a different problem and found it unpredictable (even with SQL Explorer) and very fragile that I decided to just pursue FM methods.

                     

                    This is a 'freeware' app for a non-profit and I am the sole creator.

                     

                    I am surprised that FM does not allow their 'portal filter' to work with 'non filtered' fields.  Perhaps FM 14?

                     

                    I will study your solution and see if I can make it work.  Thanks again!

                    • 7. Re: Portal Filtering and Calculations...
                      synergy46

                      It turns out that the simplest solution is best.

                       

                      Here is the outcome:

                      picreconcile.png

                      And here is the code:

                      reconcilecode.png

                       

                      But, I used an OnOpen script trigger to go into the Transaction table and collect all

                      the 'non deposit' (checks, debit card etc) transactions that are outstanding

                      (ie, RefCHKKDep <>"Y") and then collect the totals into global variables which

                      are then used as the basis for calculated fields.

                       

                      screenshot_0864.png

                       

                      I hope this helps anyone else who may be 'mystified' by FM 'filter' function

                      (or should I say 'non-functioning')

                      • 8. Re: Portal Filtering and Calculations...
                        user19752

                        I think these things are done by finding records, not loop.

                        If you need to keep found set, you can do it on new window.

                        • 9. Re: Portal Filtering and Calculations...
                          erolst

                          user19752 wrote:

                           

                          I think these things are done by finding records, not loop

                          Actually, just loop through the portal rows …that is the intended found set.

                          • 10. Re: Portal Filtering and Calculations...
                            synergy46

                            The purpose of the loop is to collect summarry information according to transaction TYPE.  Since there will never bee more than a few hundred records, it seemed like the quickest and easiest way.