12 Replies Latest reply on Apr 23, 2015 3:15 PM by philmodjunk

    Lookup vs. Variables

    Matty_1

      Title

      Lookup vs. Variables

      Post

      I'm wondering if someone could please give me the advantages and disadvantages between using lookups vs using variables to copy over the information from one place to another.

      The way I see it, is that although it's more of a pain to initially setup, look ups are better as all you need to do is copy in a record ID and everything else takes care of itself.  You never have to worry about forgetting to create a variable when there are lots of fields to transfer.  On the other hand I've been told that on a large scale, this sort of method can hinder the systems performance.  What I was told was the use of relationships in general slows things down.  Is this true?  For scalability's sake, am I best to always use variables in my script steps to move over data?

      Thank you!

        • 1. Re: Lookup vs. Variables
          philmodjunk

          In both cases the "cost" is all in the initial set up. Either you take the time to correctly setup a relationship based look up or you have to take the time to correctly script multiple variables. either way it's a field by field series of steps that all have to be set up correctly and not accidentally omitted.

          I don't think that there's much difference performance wise either, though others with more expertise with large data sets are welcome to chime in and correct me on this.

          Where there is a key difference comes afterwards. That relationship may or may not be useful for additional tasks beyond the initial look up.

          • 2. Re: Lookup vs. Variables
            Matty_1

            Thanks Phil, yeah that's more or less how I see it.  I was told around the 10 user mark I might see a noticeable difference in the performance which worries me as we will get there soon enough.

            • 3. Re: Lookup vs. Variables
              philmodjunk

              "told" by whom? The reality is that you will likely need the relationship anyway and we have a user base numbered in the hundreds...

              Keep in mind that the most efficient option is often "Neither". Instead of copying info from one table to another by either look up or a script using variables, it's often most efficient just to link to the data in the original table and never copy it in the first place. Of course, there are many reasons why you might copy the value over--such as pricing info being looked up from a products table so that future price changes in the products table doesn't change the price looked up at the time the record doing the look up was created...

              • 4. Re: Lookup vs. Variables
                Matty_1

                Further to this, I'm having a strange issue.  On a database that uses the variable route, I'm starting to make a few changes and adding in look ups.  For some reason when I use one of the variable scripts, the lookup doesn't work.  If after the fact I manually clear the ID in the match field and retype it in, the look up performs as expected.

                The only difference I see in the script is that the developer uses multiple windows which are created during the script and closed right before the end.  Any ideas on what could be causing this??

                • 5. Re: Lookup vs. Variables
                  philmodjunk

                  Not without a much more detailed view of the scripts and relationships involved.

                  • 6. Re: Lookup vs. Variables
                    Matty_1

                    Here's the script, note the two lines I've copy pasted confirming everything works with the exception of the lookup.  the Id is there in the field and as I mentioned, if I manually clear and enter it back in the lookup works.

                           
                    1.      

                      Set Variable [ $assetID; Value:T03_RM Issues::ASSET ID MATCH FIELD  (midway first page)

                           
                    2.      
                    3.      

                      Set Field [ T02_RM Work Orders::ASSET ID MATCH FIELD; $assetID (Midway third page)

                           
                    • 7. Re: Lookup vs. Variables
                      Matty_1
                      /files/8a7f6b2a4e/FMP-NewWOScriptP1.jpg 1275x1650
                      • 8. Re: Lookup vs. Variables
                        Matty_1
                        /files/5e5a5c4a2d/FMP-NewWOScriptP2.jpg 1275x1650
                        • 9. Re: Lookup vs. Variables
                          Matty_1
                          /files/75c7450ed1/FMP-NewWOScriptP3.jpg 1275x1650
                          • 10. Re: Lookup vs. Variables
                            Matty_1
                            /files/471f66a7bf/FMP-NewWOScriptP4.jpg 1275x1650
                            • 11. Re: Lookup vs. Variables
                              Matty_1
                              /files/0524f8f25a/FMP-NewWOScriptP5.jpg 1275x1650
                              • 12. Re: Lookup vs. Variables
                                philmodjunk

                                You didn't document the tables and relationships currently defined and relevant to this issue.

                                Set Variable[$assetID; Value:T03_RM Issues::ASSET ID MATCH FIELD (midway first page)

                                Better look at your script again. This value is used to find records. Then, about the middle of page 2, there's another set variable step that sets $AssetID to a new value. This is inside a loop that creates multiple new records and sets each to a different assetID as controlled by the Set Variable step on page 2.

                                Set Field[T02_RM Work Orders::ASSET ID MATCH FIELD; $assetID]

                                Without being able to see the relationships currently setup and the specific options selected in your looked up value auto-enter field option, I can't tell if there are any issues with the relationship or look up settings or not.

                                as I mentioned, if I manually clear and enter it back in the lookup works.

                                I must assume that you mean that you are doing this in a field named "AssetID", but there is more than one field with this name. From what table is this match field?