2 Replies Latest reply on Mar 12, 2009 11:56 AM by RickWhitelaw

    Lookup function question

    RickWhitelaw

      Title

      Lookup function question

      Post

      I use a calculation: Case ( Lookup (DESIGNATED_SUB::DESSUB_SH )= SUB1_DATA::SUB1_ShNm  or Lookup (DESIGNATED_SUB 2::DESSUB2_SH)= SUB1_DATA::SUB1_ShNm  ; "*" ) and have noticed something interesting. In the table DESIGNATED_SUB I have an auto-enter date field set to enter Modification Date. If I go back in time in the "parent" table and re-initiate the lookup by either changing the record or deleting and re-entering it, if the date of the "parent" record ( a simple date field) is before the Modification Date already mentioned in the child table, "*" is not returned. There are obvious advantages and disadvantages to this. What puzzles me is that I can't find any documentation on the issue. The lookup function (in a calculation) allows indexing of a calculation field but I kind of expect the function to behave as a "standard" lookup does.  . . meaning looking up the value at the time of data entry. Any thoughts appreciated!

       

      RW 

        • 1. Re: Lookup function question
          TSGal

          RickWhitelaw:

           

          Thank you for your post.

           

          Without seeing your file and data, it is difficult to determine what is the problem.  I'm not sure what you are trying to accomplish, but if I had your file, here are the steps I would take....

           

          1. Go into Manage -> Database, click on the Relationships tab and make sure I have DESIGNATED_SUB linked to SUB1_DATA (what is the key field?), and DESIGNATED_SUB 2 linked to SUB1_DATA.

           

          2. Separate the two criteria into separate fields.  That is:

           

          Lookup (DESIGNATED_SUB:: DESSUB_SH ) = SUB1_DATA::SUB1_ShNm

           

          and

           

          Lookup (DESIGNATED_SUB 2:: DESSUB2_SH ) = SUB1_DATA::SUB1_ShNm

           

          3. Since there is only one evaluation, I would change the Case statement to an If statement.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Lookup function question
            RickWhitelaw
              

            Thanks for the reply.

             

            The calculation works fine "as is" and returns the expected data unless it is initiated from a record (in SUB1_DATA) containing a  date field ( not creation or modification date) earlier than the Modification Date in  the matching record in the  DESIGNATED_SUB table. In this context "or" is necessary, not "and". Yes, there's no reason for this to be a Case function. When I wrote it I must have been thinking there would be more than one evaluation. This is more of a mystery than a problem. The fact the function doesn't return the "*" for an "earlier" record protects the history in fact.

             

            RW