5 Replies Latest reply on Aug 14, 2014 1:16 AM by Porpoise

    portal doesn't dynamically update when relationship based on calculation

    robdownunder

      Title

      portal doesn't dynamically update when relationship based on calculation

      Post

      I have a portal that is based on a key field and an event date.

      In the main table, I have a changeable event date, let's call it date_X that is used in the definition of a portal. A record in the portal matches if its event date is before date_X (defined in the relationship graph, not as a filter).  So the portal shows all records before date_X.

      date_X is based on a calculation that's just a copy of a global field in another table.

      When date_X changes, the portal doesn't update unless I do work in the relationship graphic and then exit back to the layout.

      If date_X is changed to be a static date field, everything works as expected.

      Is there any way to make the portal update automatically ?

      thanks,

      FM Pro 11, Mac OSX 10.6

        • 1. Re: portal doesn't dynamically update when relationship based on calculation
          aammondd

          You might add a RefreshWindow [Flush Cached Join] script step on the OnObjectSave script trigger on the field that is changing that forces your calculation. You might also have to add a commit record step before that just to enforce  the changes and calculations.

          Phil may be along with further advice. If the calculation is simply a copy of a global field why not use the global field in the relationship that would be easier and remove the "calculation" refresh part out of the mix.

          You can reference global fields from anywhere they dont have to be on the table or layout.

          • 2. Re: portal doesn't dynamically update when relationship based on calculation
            philmodjunk

            The only thing I can think to add is that if you defined the global field to be part of the table your layout is based on, this refresh window step would probably not be needed though sometimes a commit record step helps things along if you are using a script to modify the global.

            Note to aammondd: My guess is that robdownunder is using the calculation field so that he can get a field into the right table for use in defining the portal's relationship here.

            • 3. Re: portal doesn't dynamically update when relationship based on calculation
              robdownunder

              Thanks aammonndd and Phil,

              Phil's right about the reason for using the calculation.

              I'm going to have to bite the bullet and move the date field to the main table. The reason I was avoiding this is that it is referenced on multiple layouts and in various scripts, so I have some major finding and replacing to do.   The field was in a separate table I use to hold search criteria throughout my database, separate to avoid cluttering other tables with searching tools. The field existed before I hit this problem with the portal not-refreshing.

              Shame FM has this feature. I'll have to work around it.

              cheers,

              rob

              • 4. Re: portal doesn't dynamically update when relationship based on calculation
                aammondd

                I use tables of global fields all the time to display disparate data from non relatable tables.

                I just use the globla table as a join table using the cartesian (X) join to the layout table. Then I relate my other table to the Global table in a standard join fashion.

                Since the global field  is the same for all records anyway the Cartesian join is just a formality in order to allow the other tables records to be displayed.

                 

                 

                • 5. Re: portal doesn't dynamically update when relationship based on calculation
                  Porpoise

                       This thread is quite old, but just in case somebody else stumbles across it. I found a solution.

                       The workaround of the OP to move the global field to the main table of the layout won't work if you use the global field on multiple layouts with unrelated main tables.

                       A cartesian join of the table with the global field to the main table won't work if you have other criteria for the relation between the main table and the portal table.

                       The obscure feature/bug here is that a global calculation field in the main table seems unable to refer to a global field in another table, regardless of Commit Record or Flush Window. E.g.
                           Table1 Field1 Number Global
                           Table2 Field2 Calculation Global, [=Table1::Field1]
                       Now you can put anything you want in Table1::Field1. Table2::Field2 won't return anything.

                       A workaround that does work, is to make the calculation field in the main table unstored in stead of global. While this does mean that you can't refer to the calculation field if there are no records in the main table, that shouldn't be a problem here because the portal would be empty anyhow.