8 Replies Latest reply on Feb 24, 2016 11:11 PM by Doug Staubach

    Find involving Calculation field not working



      Find involving Calculation field not working


      First of all I'm using Filemaker Pro Advanced version 13.

      I am trying to find records based on a simplified calculation of either "0" or "1" as Text even in calculation definition based on lookup values across more than 10 fields concatennated into one calculation field

      All I'm simply doing is perform find, enter "1" then enter, and it keeps coming up as "No records match this find criteria."

      How do I make this work?


        • 1. Re: Find involving Calculation field not working

          Can you post the calculation used in that calculation field?

          • 2. Re: Find involving Calculation field not working

            UserFoundCal (Calculation-Text)
            Case(PatternCount(UserSupvInspCal; "^" & Trim(UserCal) & "¶")=0;"0";"1")

            UserCal (Calculation-Text) = User

            UserSupvInspCal (Calculation-Text)
            "^" & VLookup(Profiles::ProfileName; FireSupv; Profiles::Name) &
            "^" & VLookup(Profiles::ProfileName; BuildSupv; Profiles::Name) &
            "^" & VLookup(Profiles::ProfileName; FireMarshalInsp[1]; Profiles::Name) &
            "^" & VLookup(Profiles::ProfileName; FireMarshalInsp[2]; Profiles::Name) &
            "^" & VLookup(Profiles::ProfileName; BuildingInsp[1]; Profiles::Name) &
            "^" & VLookup(Profiles::ProfileName; BuildingInsp[2]; Profiles::Name) &
            "^" & VLookup(Profiles::ProfileName; ProjectManager[1]; Profiles::Name) &
            "^" & VLookup(Profiles::ProfileName; ProjectManager[2]; Profiles::Name) &
            "^" & VLookup(Profiles::ProfileName; ProjectManager[3]; Profiles::Name) &

            VLookup ( LookupTableAndFieldName ; MatchingValueOrList ; ReturnFieldName ) - Custom Function
            same as from https://www.briandunning.com/cf/1740

            • 3. Re: Find involving Calculation field not working

              Apologies for asking an obvious question but we have to rule this out first: When you inspect the records in Browse mode before performing the find, do you find records where UserFoundCal displays as "1"?

              And is this file local to your machine or hosted from FileMaker Server or FileMaker Pro on another computer?

              • 4. Re: Find involving Calculation field not working

                Yes, for where UserFoundCal displays as "1" which should be text anyways.

                while the printscreen has a "find" button which performs a script to enter find mode where I enter "1" in the UserFoundCal field set for field entry as "Find Mode" (no browse mode selected as I'll eventually make this a hidden field), I also tried the same using the Find button in the status toolbar and entered "1" similarly.

                This file is currently hosted on our filemaker server.

                • 5. Re: Find involving Calculation field not working

                  Take a back up copy on your your development machine and see if it works then. There's a known bug that can keep certain calculation fields from working in a find when the file is hosted. If that's the case here, then we've encountered another instance of that bug.

                  • 6. Re: Find involving Calculation field not working

                    Interesting, it works finding those set to "1" as a local copy at least.  Hopefully we can get this to work as a hosted file somehow.

                    • 7. Re: Find involving Calculation field not working

                      Any idea when a fix for this will be available?

                      For now I suppose as a temporary workaround I could use another text field and do a replace with the calc value before doing the find script which seems to work from a server so long as multiple users don't press that find button simultaneously.

                      • 8. Re: Find involving Calculation field not working
                        Doug Staubach

                        Hello jfalberg:


                        My name is Doug Staubach - I am the author of the VLookup function that you referred to in your earlier message.


                        I just wanted to let you know that I have released an updated version of that function today. The new version might provide you with better information and perhaps a better way of querying your fields without having to manually concatenate them. (It works better for non-related tables, returns useful error messages when SQL statements are formatted incorrectly, and can return the contents of multiple fields from the same row, instead of just one field.)


                        I recommend updating the function that you have with the new code version (1.2). After you do that, let me know if it solved your problem. (If it doesn't meet your needs, reply back to this message, and I'll see if I can help).