3 Replies Latest reply on Dec 17, 2011 8:27 AM by LyndsayHowarth

    Does Looked-up value still have value?


      I haven't used a Lookup in a long time. An auto enter calc is so much more flexible.



        • 1. Re: Does Looked-up value still have value?

          Hi Kyle,


          Provided you make appropriate use of the Lookup( ) and LookupNext( ) functions (and, of course, disable the "Do not replace existing value of field [if any]" option), an auto-enter calc can precisely match the functionality of a lookup. In which case, yes, the auto-enter approach provides added flexibilty. But on its own without the use of those two functions, an auto-enter calc's behavior is more limited.


          That said, as I see it, there are two main ways that the Looked-up value option still provides good service. One is for backward compatibility, since there are a lot of solutions out there that contain loop-ups - sometimes thousands of lookups. The other is simplicity for novice users, since the "Lookup for Field..." dialog is a fair bit easier for a newby to deal with and understand than the nest of calc expressions required to get to the same point using Lookup( ) and LookupNext( ) functions in an auto-enter calc.


          In addition, some folk would argue that if you use the Looked-up value option when that's the range of functionality you want and use auto-enter calcs for all other cases, the database will be easier to understand - eg for another developer trying to interpret schema. My view is that this is a matter of style, and one could instead use field comments etc to make the intent clear - so this third argument is not as strong as the other two IMO.


          Given all of the above, I see it as a good think that both options are still around, even though there is overlap. Choices are a good thing...  ;)





          R J Cologon, Ph.D.

          FileMaker Certified Developer

          Author, FileMaker Pro 10 Bible

          NightWing Enterprises, Melbourne, Australia



          • 2. Re: Does Looked-up value still have value?

            I agree that choices are a good thing.


            Here's a particular case where lookups can be your friend.


            Take for example an invoice to company ABC. And, yes, we've used an ID, (i.e. Org_00001), to relate the invoice to that organisation. A while later company ABC has a management change or something and changes their name to ABC & D. But apart from the name change it is still the same organisation with the same related people and address.


            What an appropriate look up can do is to provide a company name for the invoice that reflects the company that was actually invoiced at that time, and which will not be changed retrospectively if the recipient company name is subsequently edited, whilst retaining a full history of business with that organisation by reference to the ID. Invoices are a particularly easy example because they are almost always created on the day they are dated, so that frozen moment can be based on the instant of creation.


            Other comparable examples, where a look up can be useful for recording the data of the moment, include currency rates or personnel, stock or other fluctuating counts.


            Mind you, its important to ensure that a re-lookup is not triggered unless you intend it to be, which is why a scripted set field may sometimes offer superior control as against using a simple look-up at the moment of creation or even a conditionally triggered auto-entry one.


            Choices - good!


            Chris M

            BabelFix, London

            • 3. Re: Does Looked-up value still have value?

              I continue to use both... depending on subtly different circumstances.


              I find an auto-enter calc can be a bit friendlier when using PHP. I find the join is cemented better. Values are looked up... but sometimes before the values are committed... it seems.


              Auto-enter calcs can be controlled much better... especially when using Evaluate().


              - Lyndsay