8 Replies Latest reply on Sep 4, 2012 9:13 AM by philmodjunk

    No updated unless manual Refresh Window

    MikeF_1

      Title

      No updated unless manual Refresh Window

      Post

      Have a layout based on table Events.

      In that layout is a revenue portal based on table Revenue, and
      an expenses portal based on table Expenses.

      In the Expenses table is a summary field --- Expense Amount
      Summary.

      If I change a flat expense in that portal, the Expense Amount
      Summary [in the Events layout] calculates properly and accommodates the change.
      This works fine.

      The problem is however --- if I change a record in the
      Revenue portal, that impacts a variable expense which calculates inside the
      portal properly [say total Revenue times 3%].

      … But it does not update the Expense Amount Summary field
      unless a Refresh Window command is manually executed.   This
      causes all sorts of problems – it’s frustrating for the user, and if they don’t
      remember the reports are mathematically incorrect.

      Have a script named CommitRefresh that contains two lines
      --- Commit Record and Refresh Window. 
      Have put it everywhere I can think of but to no avail.

      Hopefully this is clear enough.

      Any solutions would be greatly appreciated.

      Thanks,

      -         
      Mike

        • 1. Re: No updated unless manual Refresh Window
          philmodjunk

          Is this the relationships that you have?

          Revenue>----Report-----<Expenses

          Report::__pkReportID = Revenue::_fkReportID
          Report::__pkReportID = Expenses::_fkReportID

          If this notation is unfamiliar, see: Common Forum Relationship and Field Notations Explained

          Thus, when a change in a revenue record is made that changes how an Expenses record calculates, it's via the same relationships as those for your portal.

          if I change a record in the Revenue portal,

          Have you tried using a script triggered by the OnObjectSave trigger on this field?

          A better option might be to add this relationship to the above:

          Expenses-----<ExpRevenue   (ExpRevenue is a new occurrence of Revenue)

          Expenses::_fkReportID = ExpRevenue::_fkReportID

          Then define your calculation to refer to data in ExpRevenue and to evaluate from the context of Expenses.

          • 2. Re: No updated unless manual Refresh Window
            MikeF_1

             

            Phil,

            The relationship is Events::_pkEventID to EventID in both Revenue and Expenses table.


            Events::__pkEventID = Revenue::EventID  [Indexed only]

            Events::__pkEventID = Expenses::EventID [Indexed only]

             

             - Mike

            • 3. Re: No updated unless manual Refresh Window
              philmodjunk

              Which differs only in the field and table names that I used in my example. If you substitute your names for mine, this should work.

              • 4. Re: No updated unless manual Refresh Window
                MikeF_1

                Have tried all of the above, to no avail.  Expense Amount Summary does not change properly unless I manually Refresh Window.

                 

                >Have you tried using a script triggered by the OnObjectSave trigger on this field?

                      Which field do you mean?

                 

                And ... is there any way to Refresh Window on *anything* changing in a layout?

                As in duplicate the manual procedure automatically?

                 - Mike

                • 5. Re: No updated unless manual Refresh Window
                  philmodjunk

                  You could try ONRecordCommit as a layout based script trigger.

                  It should not be necessary, however. You might try Refresh with the "flush cached join results" to see if that makes a difference, but that's an option that can create it's own problems with long delays while the layout refereshes and thus is best avoided. It may be useful data here as a test though to see if it is the wrong trigger or the wrong script steps that is keeping the scripted update from working.

                  Did you try the relationship change? That method "cuts out the middleman" and thus the "tunneling" that can create refresh issues by linking the related data directly.

                  You might also experiment with using the Sum (RelatedTable::RelatedField) function in place of referring to the summary field from the related table.

                  • 6. Re: No updated unless manual Refresh Window
                    MikeF_1

                    I have it working, but knowing better and forging ahead anyway, don't know which step did it.

                    Will have to re-trace everything bit by bit.

                    Thanks Phil.

                    • 7. Re: No updated unless manual Refresh Window
                      MikeF_1

                       

                      Phil,

                       

                      Got rid of all the script triggers and simply put Expenses "downstream" from Revenue in the relationship graph, thinking that if Revenue feeds Expenses, then the variables will be properly accounted for.  It definitely calcs properly, so believe it's the correct solution.

                      Uncertain why another table occurrence of Revenue did not do the same thing, but the following certainly works ...

                       

                      Events::__pkEventID = Revenue::EventID = Expenses::EventID

                       

                      There are other tables related to Revenue and Expenses, but are further down the line and do not appear to be affected in any way.

                       

                       - Mike

                       

                       

                      • 8. Re: No updated unless manual Refresh Window
                        philmodjunk

                        Uncertain why another table occurrence of Revenue did not do the same thing,

                        I can't tell for sure from here, but I'd guess that you added a new table occurrence but still referred to the existing occurrence. Adding a new occurrence of the table "down stream"--which is what I had in mind, should work, but your data references have to be to fields from the newly added occurrence.