4 Replies Latest reply on Jul 27, 2012 4:04 PM by rtgauss

    self-joining relationship problem


      Having an issue with getting a self-joining relationship lookup to work properly and was hoping someone could add some insight to or maybe have a better way to solve the problem without using the lookup.


      First the background:

      The input field, 'countryin', is the original input. 'country' is a calculation which checks 'countryin' and corrects/changes it based on rules and related fields. In a large database, 20,000 to 50,000 records, the sort and find can take a long time beause the field ('country' in this example) is a calculation that is not able to be indexed becuase it references a related field. The solution was to create another field 'country2' which does a lookup based on a self-joining relationship. The self-joining relationship is a calculation which is: "Get(RecordID) & country." The recordID would ensure uniqueness and country changing would tirgger the lookup. Then the sort and find would be done on the field 'country2', instead of 'country'. Becuase 'country2' is a simple text field, it would be indexed and therefore the sort and finds would be much faster.


      The problem:

      The lookup never finds a exact match.

      If the lookup is set to exact match or do nothing, the result is nothing. If the lookup is changed to "next lower" or "next higher" it does the lookup, but from the wrong record. Perhaps it is a timing issue where the lookup is attempted before the calculation is completed and saved?


      Any insight or solution?


      Many thanks.

        • 1. Re: self-joining relationship problem

          rtgauss wrote:


          'country' is a calculation


          Why don't you change it into a Text field with auto-entered calculated value, replacing existing value?

          • 2. Re: self-joining relationship problem

            Thank you.


            I had already tired that, and it does speed up the sort and finds, but the enormous increase in the import phase, due to the auto-enter, far out weights the time the sort and finds currently take.


            Too bad I can't firgure out why the lookup does not work.

            I'll have to find another solution to speed things up.


            Thank you.

            • 3. Re: self-joining relationship problem

              I guess I don't see why a lookup would be any faster, even if it did work. You didn't say what the calculation does - still, the time required to perform the calculation and the time required to index the field would be the same. If anything, a direct auto-enter based on local values should be faster than fetching data over a relationship.




              BTW, the reason why the lookup cannot work the way you have it is that the relationship looks for a matching value in the index. When you modify the matchfield in the target record, the index on the "other" side of the relationship remains unchanged untli the record is committed. Therefore the modified value will not find a matching value and the lookup will enter the value specified under 'If no exact match, then:'


              You can test this by entering an arbitrary value to use in such case and see that it indeed gets used - signifying that no exact match was found.


              Message was edited by: Michael Horak

              • 4. Re: self-joining relationship problem

                FWIW:  We ended up with a hybrid version of auto calculation, due to speed issues. We use a text field with auto calculation, but we don't use 'perform auto enter options' during import. After the import, a script loops through and set the three inported fields to themselves, which activates the auto calculation in the desired field.


                For a 12,000 record import:

                using 'perform auto enter options'  takes 7 minutes for the import,

                using the hybrid version takes 3 minutes for the import and the script.


                Given we have to import 30-40 files a day, averaging 47,000 records, the speed savings is significant.


                Thanks for everyone's input.