3 Replies Latest reply on Jan 18, 2016 10:18 AM by StylisticGambit

    Auto-enter calculation, replace existing value from remote table?


      Imagine a field called FIELD in a table called TABLE1.

      TABLE1 is related to TABLE2 via some other field besides FIELD.  Inside TABLE2 there are many, many calculation fields that all depend on the value in TABLE1::FIELD.

      Here's the problem: those calculation fields inside TABLE2 must all be unstored, and performing a Find or running an ExecuteSQL query against any one of them is slooooow!

      So, I'd like to find a way to speed things up.  My thought is to create a FIELD table inside TABLE2 that would would be populated based on the value in TABLE1::FIELD.  Now, all of the unstored calculation fields inside TABLE2 that depend on TABLE1::FIELD can be pointed to TABLE2::FIELD instead.  I can index those calculations, and everything should be must faster.

      Is it possible to create TABLE2::FIELD so that it will auto-update against TABLE1::FIELD?  Or, do you have any other suggestions for this situation?

        • 1. Re: Auto-enter calculation, replace existing value from remote table?

          I've worked out a solution, but it's not exactly ideal.  I'll post it here in case anyone else finds this thread in the future.

          First, create a field called TABLE2::TRIGGER_UPDATE.  We will use this field to trigger a re-lookup of the TABLE1::FIELD value every time TRIGGER_UPDATE is modified.  If you want, you could set TRIGGER_UPDATE to be something like a modification timestamp housekeeping field -- this will force the update every time the record is modified.  Or, you script some other way to change the value of TRIGGER_UPDATE whenever you wanted to update your record.

          The next thing you need to do is create an auto-enter (and replace existing value) field called TABLE2::FIELD, defined as follows:

          If ( TABLE2::TRIGGER_UPDATE <> "" ; ExecuteSQL ( ... ) )

          The ExecuteSQL calculation will vary from case to case, but it's purpose is to pull the current value of TABLE1::FIELD.  In my case, it goes something like:

          ExecuteSQL ( "SELECT FIELD FROM TABLE1 WHERE PrimaryKey = ?" ; "" ; "" ; TABLE2::ForeignKey )

          So!  Because the TABLE2::FIELD can now be indexed, all of the calculations in TABLE2 based on it can now be indexed as well.

          • 2. Re: Auto-enter calculation, replace existing value from remote table?

            The performance hit using modification timestamp and ExecuteSQL() could be considerable. Also, it doesn't guarantee the data is current. I'd recommend a scripted approach to updating the trigger field and using a relationship to lookup the data, rather than ExSQL().


            But otherwise, yes, this is a good technique for trying to do what you want.

            • 3. Re: Auto-enter calculation, replace existing value from remote table?

              David, thanks for questioning my use of ExSQL and making me think! 

              Somehow, my brain was stuck on the the misapprehension that I was creating an indexed calculation field rather than an auto-enter field with replace existing value.

              For readers unfamiliar, the difference is that indexed calculation fields must only reference the local table.  As soon as you reference a remote field, the calculation is forced to be unstored, which causes the search slowdown that I was trying to avoid.  ExSQL was my clumsy way around this problem by referencing a remote field as if it was local.  (It has since occurred to me that GetField ( "TABLE1::FIELD" ) or Evaluate ( "TABLE1::FIELD" ) would also serve this purpose for an indexed calculation field, rather than my ExSQL command.)

              But David is exactly right.  So, my new definition for TABLE2::FIELD is:

              If ( TABLE2::TRIGGER_UPDATE <> "" ; TABLE1::FIELD )

              David, your recommendation to using a scripted approach to update the trigger field is also helpful.