2 Replies Latest reply on Jan 26, 2015 2:29 PM by jdevans

    after converting- "There are no fields that look up values..." Error 402

    jdevans

      "There are no fields that look up values based on the field SHELF LIFE STATUS."

       

      I am getting this error on file open. It seems to be a glitch in a startup script. Not sure how to troubleshoot this. Source file was fp5. In FileMaker Pro 5, this error isn't there. But after converting it up to FileMaker Pro 13 (fmp12 file ext), it is happening on file-open.

       

      The field SHELF LIFE STATUS is a calculation whose result is set to Text.

      The calculation is SHELF LIFE STATUS= If(SHELF LIFE EXP = ""; ""; If(ITEM PURGED = "YES"; "PURGED"; If(SHELF LIFE EXP <  Get(CurrentDate); "EXPIRED";If((SHELF LIFE EXP - Get(CurrentDate)) <= 45; "EXPIRES IN " & (SHELF LIFE EXP - Get(CurrentDate)) & " DAYS"; "ACTIVE"))))

       

      I'm a scripting/calculation newb. Is there anyone who can help me troubleshoot this?

       

      When clicking OK on the error message, the file seems to work OK, but it is more of an annoyance than anything.

        • 1. Re: after converting- "There are no fields that look up values..." Error 402

          Hi Jdevans,

           

          I just tested this in a new v11 database and didn't see any errors when I open the file. You could try rebuilding this manually to see if you can isolate where the error is coming from. For example, with a copy of the database, remove this (or other) calculated fields until the error goes away and then add bits of the calculation back in until it reappears to pinpoint the issue.

           

          This is a somewhat complicated statement because of the nested If statements. I added tabs and ELSE statements to it in a text editor purely for the sake of making it readable (this is not valid FileMaker code with the ELSE statements).

           

           

          SHELF LIFE STATUS= 
          
          If(SHELF LIFE EXP = ""; 
               ""; 
          ELSE
               If(ITEM PURGED = "YES"; 
                    "PURGED"; 
               ELSE
                    If(SHELF LIFE EXP <  Get(CurrentDate); 
                         "EXPIRED";
                    ELSE
                         If((SHELF LIFE EXP - Get(CurrentDate)) <= 45; 
                              "EXPIRES IN " & (SHELF LIFE EXP - Get(CurrentDate)) & " DAYS"; 
                         ELSE
                              "ACTIVE"               )
                    )
               )
          )
          

           

           

          Hope this helps..

          • 2. Re: after converting- "There are no fields that look up values..." Error 402
            jdevans

            I used the script debugger, and the first error that came up was in a script called "Sort by Stock Number." This was called as part of the "Startup" script that happens on file open.

             

            The last line of "Sort by Stock Number" was this:

            Relookup Field Contents [No dialog; HarwareInv::SHELF LIFE STATUS]

             

            It seemed redundant based on what preceded it. So I wrote it down, then deleted that one line.

             

            No more SHELF LIFE STATUS error! None of the scripts seem to be affected by that change, and all the buttons do what they're supposed to do.


            What preceded it was:

            Sort Records [Restore; No dialog]

            Go to Record/Request/Page [First]

            Go to Field [HardwareInv::SHELF LIFE STATUS]