7 Replies Latest reply on Aug 25, 2009 8:45 AM by ninja

    Script to find records with a specific value in multiple fields

    samxli

      Title

      Script to find records with a specific value in multiple fields

      Post

      I have a layout which has many calculation fields. These fields vary in value, but if an empty field is detected, it will trigger an insertion of "!!EMPTY!!" into that field.

       

      Is there a way to write a script that will show me records that have one or more fields that contain the value "!!EMPTY!!" ?

       

      I can approach it by doing a bunch of If-Else statements, but is there an easier way? (I have 50+ fields per record). 

        • 1. Re: Script to find records with a specific value in multiple fields
          TSGal

          samxli:

           

          Thank you for your post.

           

          I'm reading your post two different ways...

           

          If an empty field is detected, you want to insert "!!EMPTY!!" into that specific field.

           

          You can perform a Replace on this field using the calculation:

           

          If (IsEmpty (field); "!!EMPTY!!"; field)

           

          You can put this into a script, duplicate the script 50+ times and modify each script for the field and replace value.

           

          Since you mentioned If-Else statements, it sounds like you wan to put "!!EMPTY!!" into one field to let you know there is an empty value.  All you need is one If statement separated by "or".  That is,

           

          If (IsEmpty (field1) or IsEmpty (field2) or IsEmpty (field3).....or IsEmpty (field<n> ); "!!EMPTY!!; "")

           

          That is, the calculation checks to see if any of the <n> fields are blank.  If so, then "!!EMPTY!!" is displayed.  Otherwise, nothing is displayed.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Script to find records with a specific value in multiple fields
            samxli
              

            TSGal:

             

            Thanks for your reply. What I meant to say was: I have fields that already contain the value "!!EMTPY!!" in them. How can I write a script that will only bring up records that contain one or more fields with the value "!!EMPTY!!" ?

             

            The purpose of this is to check which records have "!!EMPTY!!" fields so that I can fill them in with valid data.

             

            Thanks!

            • 3. Re: Script to find records with a specific value in multiple fields
              TSGal

              samxii:

               

              Thank you for the clarification.

               

              You can definitely create a script that would find the value "!!EMPTY!!" in one of your fields.

               

              The script would look something like the following:

               

              Enter Find Mode []

              Set Field [<field1>; "EMPTY" ]

              New Record/Request 

              Set Field [<field2>; "EMPTY" ]

              New Record/Request

              Set Field [<field3>; "EMPTY" ]

              ...

              New Record/Request

              Set Field [<fieldn>; "EMPTY" ]

              Perform Find []

               

              --------

               

              There is no need to enter the exclamation marks in the search.

               

              If you need more information, please let me know.

               

              TSGal

              FileMaker, Inc. 

               

              In your first find request, select the first field and enter: EMPTY

               

              (You do not need the exclamation marks).

               

              In the second find request, select the second field and also enter: EMPTY

               

              Continue this through each of the 50+ fields.

               

               

               

               

              I'm reading your post two different ways...

               

              If an empty field is detected, you want to insert "!!EMPTY!!" into that specific field.

               

              You can perform a Replace on this field using the calculation:

               

              If (IsEmpty (field); "!!EMPTY!!"; field)

               

              You can put this into a script, duplicate the script 50+ times and modify each script for the field and replace value.

               

              Since you mentioned If-Else statements, it sounds like you wan to put "!!EMPTY!!" into one field to let you know there is an empty value.  All you need is one If statement separated by "or".  That is,

               

              If (IsEmpty (field1) or IsEmpty (field2) or IsEmpty (field3).....or IsEmpty (field<n> ); "!!EMPTY!!; ""

               

              That is, the calculation checks to see if any of the <n> fields are blank.  If so, then "!!EMPTY!!" is displayed.  Otherwise, nothing is displayed. 

              • 4. Re: Script to find records with a specific value in multiple fields
                difazioj
                  

                Hi,

                Our school board is using filemaker pro 5.5 and I have used filemaker to create a database containing student information.  When I try to find all students who belong to St. Paul school I also get students from Sts. Peter & Paul, and St. Vincent de Paul.  I have a pop down file showing the list of all the schools and from that list I select the school that I want.  I know I can add the = sign or write a find script but is there another solution to this problem.

                 

                Thanks

                DiFazio

                • 5. Re: Script to find records with a specific value in multiple fields
                  ninja
                    

                  Howdy difazio,

                   

                  It's dangerous to introduce another topic into an existing post...especially a solved one...'cause many folks wont look at it.  You would want to start a new thread instead.  Then your post and the subsequent help will be findable by others in the future as well.

                   

                  using the = is the solution, whether manual or scripted.  If you make all searches an exact match, then you can't do vague finds and you will have lost functionality.

                   

                  To directly answer your question...no, not really, and this is a good thing.  Is there a reason you are looking for another way?

                  • 6. Re: Script to find records with a specific value in multiple fields
                    philmodjunk
                      

                    Ninja I agree with you but with one minor quibble:

                     

                    You could also set up a global text field with a drop down listing your names. Then a script with Go To Related Record could pop up a found set of matching records. I wouldn't recommend this though. (It's more work to set up and GTRR scripts have to be designed with care to avoid unexpected results.) A simple find, (for exact match use ==), either manual or scripted should do the job nicely.

                    • 7. Re: Script to find records with a specific value in multiple fields
                      ninja
                        

                      PhilModJunk wrote:

                      Ninja I agree with you but with one minor quibble:

                       

                      You could also set up a global text field with a drop down listing your names. Then a script with Go To Related Record could pop up a found set of matching records. I wouldn't recommend this though. (It's more work to set up and GTRR scripts have to be designed with care to avoid unexpected results.) A simple find, (for exact match use ==), either manual or scripted should do the job nicely.


                       

                      Quibble away,

                      It's always hard to find a balance between what folks want from a brief post, what they can do when you've never seen their work, and showing advanced/more intricate things to folks with unknown experience levels.  I'll never claim to get the balance just perfectly.

                       

                      But then I'll never say never either... ;)

                       

                      I appreciate it when you flesh out an answer that I've left more simplified than your preference.