11 Replies Latest reply on May 18, 2017 9:24 AM by srzuch

    Portal Totals

    LWayne_1

      Title

      Portal Totals

      Post

      Hi -

      I have a Portal (Portal 1) that automatically changes when the related field changes in the layout table.  This works as planned.

      I then added a Portal 2 which is exactly the same as Portal 1 except it shows the summary of selected fields on one line. When the related field in the layout changes Portal 1 information changes but the totals in Portal 2 no longer show.  What do I have to do to automatically change the summary fields in Portal 2 so they will automatically refresh like Portal 1 does?

      Thanks,

       

      LWayne

        • 1. Re: Portal Totals
          Dillik

          I'm seeing the behavior you describe only when the summary of related records is done by placing a summary field from the related table onto the current layout, and only when the key field that drives the relationship is expressed as a popup list, radio button set, etc.  If the key field is a regular text field, the summary field updates when I click out of the record or hit Enter; for some reason, this doesn't happen with other control styles (such as popup lists).

          The problem also goes away if the summarizing is done in the local table using a Sum() function in a calculation field (this method is preferable if you ever want to search on the summarized data, as summary fields are inaccessible in Find Mode).  If you don't want to switch to local calculation fields, you can force the summary fields to refresh by putting an OnObjectModify script trigger on your key field; the triggered script should perform a Commit Records/Requests step.

          1 of 1 people found this helpful
          • 2. Re: Portal Totals
            philmodjunk

            If Dillik's excellent advice doesn't resolve this, Please post back with a more detailed description of the design of your layout and the relationships used to set these two portals.

            • 3. Re: Portal Totals
              LWayne_1

              Dillik -

              Thanks for your help.  I tried both the last two ideas and they both worked great!

              • 4. Re: Portal Totals
                piaccounting

                Had a similar query:

                I have a portal of Table A containing an Amount field. I also have a TotalAmount field on the same Table A which is defined as Type: Summary (= Total of Amount).

                 

                The TotalAmount field was not refreshing after each entry of the amount field from the rows on the portal. So I searched the site and found this:

                 

                If you don't want to switch to local calculation fields, you can force the summary fields to refresh by putting an OnObjectModify script trigger on your key field; the triggered script should perform a Commit Records/Requests step.

                 

                 

                Thank you for taking the time to explain that bit fully, as it did the trick perfectly. I know it's 5+ years' old but does show the value of these sites.

                 

                Dave

                PS I think the question should be marked as answered!

                • 5. Re: Portal Totals
                  srzuch

                  However, if you are using the local calculation total field before the portal records are committed the actual amount stored in the field may not be updated, even though the displayed amount is.  So if you want to test to see if the local calculation total field equals another amount (or zero) before committing the records, the test may not be accurate.

                   

                  I read something that you need to further trigger the calculation engine, but I have not figured out what exactly must be done to guarantee the accuracy before the committing the records.  Also, I am still on FMA 15 (no plans to update in the near future)

                   

                  Steve

                  1 of 1 people found this helpful
                  • 6. Re: Portal Totals
                    piaccounting

                    Yes I fear I may have spoken too soon. Obviously getting the totals to refresh is not as straight forward as (we both) hoped. I see from Summary fields not updating there has been some great contributions to the issue. My total fields too were mysteriously vanishing when entering data (philmodjunk) - refreshing to know I'm not alone. Right now time to get these totals right!

                    • 7. Re: Portal Totals
                      philmodjunk

                      I just ran some tests as some of this sounded odd.

                       

                      I set up watch expressions on a summary field fro the related table and a calculation field using Sum in the parent table. I set up a script that did the following

                      Show custom dialog (set to show values of both fields)

                      Commit records

                      Show custom dialog (same settings)

                      Refresh Window

                       

                      The results were just as odd as reported if not more so.

                      When I edited a field and tabbed out of it, the calculation field updated on the layout to show the correct total. The summary field either failed to update or went blank. But in the data viewer, the summary field showed the correct total and the calculation field using Sum () did not. I ran the script and the first custom dialog showed the same values as the data viewer. Then the commit records updated the values in the data viewer and the values in the second dialog. But if the summary field was blank, it did not show a value until I either refreshed the window or clicked into the field.

                       

                      So bottom line, you should consider both a commit records and a refresh window before the aggregate values based on the modified data will be consistently correct both in display and when accessed in a script.

                       

                      Tests were in FileMaker 15 in windows 10.

                      • 8. Re: Portal Totals
                        piaccounting

                        I’ve certainly experienced total fields not updating and the fields disappearing (i.e. going blank). Now I confess I have not been circumspect in placing fields in the right table but am becoming more so as I learn more.

                         

                        What I have at the moment is a layout with Parent Table A and portal Table B:

                        The sum of the amount field on portal Table B is being calculated with a field from the parent Table A. 

                        This field is a Calculation type field and is unstored, =Sum (TableB::Amount).

                         

                        The amount field on the portal has an OnObjectModify script which does a Commit only.

                         

                        I did wonder about a window refresh but this appears to be working well at the moment, so will try not to fix it – unless needed. Like the idea of the custom dialog box - sure I'll find some good uses for them.

                        • 9. Re: Portal Totals
                          srzuch

                          Your results are similar to what I came up from my testing under FM 14 and 15.  I was trying to use implicit transactions via a portal (per Geist Interactive) to (1) record journal entries and (2) test to confirm that debits = credits prior to (3) committing the transactions.  The same concept can be applied to many accounting tasks (allocating a disbursement to many expense accounts,  allocating an inventor sale quantity to many product lots, allocating a customer receipt to many unpaid invoice balances, and so on ...)

                           

                          The amounts accessible through reference to the aggregation sum calculation field did not equal the correct amounts displayed if the portal records were not committed.

                           

                          Therefore if I continue with FM, I will commit each record in the portal, and use some "flag" in the master (parent) table to denote if the entry is balanced or not. 

                           

                          I have seen some suggestions to loop through the portal and calculate the totals in the validation scrip, but I am not comfortable working with data in fields prior to committing the record.

                           

                          There should be a way to access the correct displayed total amount without committing the records, but I have not figured it out (but then, I am a newbie with FM).

                           

                          Steve

                          1 of 1 people found this helpful
                          • 10. Re: Portal Totals
                            philmodjunk

                            In batch updates, one normally need only commit records once, after updating or creating the last record in the batch. In my most recent test, I will note that the summary field, when accessed via script, returned the correct total without having to update, but whether that will always be the case? Hard to say from just one test.

                             

                            Once I have FMP 16 installed, I'll run the same tests and then see if there is any issue report in existence that is relevant before making an issue report if I get the same results with the latest release of FileMaker.

                            • 11. Re: Portal Totals
                              srzuch

                              This is what I have found to date.

                               

                              If the portal amount being totaled is an UNSTORED calculation (e.g. quantity * price), and the total is calculated and displayed in the parent table with an aggregate sum function (=sum(portal::amount)), then every thing appears to be updated and displayed correctly prior to committing the portal records.

                               

                              But, if the portal amount is a STORED calculation, then sum amount used in a script in not updated until committing.

                               

                              Steve