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

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

    StylisticGambit

      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?
          StylisticGambit

          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?
            DavidJondreau

            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?
              StylisticGambit

              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.