9 Replies Latest reply on Dec 30, 2016 7:45 AM by philmodjunk

    Scripts for a find using two fields

    kentuckyfmp

      Hi

      I have a single table with 1000+ records and 10 fields including a Size field and a Colour field.

      There are no related tables.

      I need two separate scripts please:

       

      1.  To find all records with a given size and a given colour.  eg size 3 and Red

      2.  To find all records with a given size but NOT to include up to three colours. eg size 4 but NOT Green, Blue or Yellow.

       

      The end result is to be used for producing labels to attach to products. I can manage the labels part of the script but the Find has me baffled.

      I've searched all documentation that I have and looked up numerous websites but cannot find any examples to adapt.

       

      I should add that I'm new to both Filemaker and scripting. (I am familiar with MS Access and used their query system for such tasks as this. NOT SQL).

       

      Many thanks in anticipation.

      Gordon

        • 1. Re: Scripts for a find using two fields
          erolst

          The first Find is:

           

          Enter Find Mode

          Set Field [ YourTable::size ; 3 ]

          Set Field [ YourTable::colour ; "Red" ]

          Perform Find

           

          The second one could be scripted as:

           

          Enter Find Mode

          Set Field [ YourTable::size ; 4 ]

           

          New Record/Request

          Set Field [ YourTable::colour ; "Green" ]

          Omit Record

          Duplicate Record/Request

          Set Field [ YourTable::colour ; "Blue" ]

          Duplicate Record/Request

          Set Field [ YourTable::colour ; "Yellow" ]

           

          # [ the above could (and would) be written as a loop ]

          Perform Find

          2 of 2 people found this helpful
          • 2. Re: Scripts for a find using two fields
            kentuckyfmp

            Many thanks my friend

            First one works perfectly.

            Don't really understand the line re the Loop in part 2 and what does the # sign signify please? Is it a comment?

             

            Cheers

            Giordon

            • 3. Re: Scripts for a find using two fields
              erolst

              kentuckyfmp wrote:

              what does the # sign signify please? Is it a comment?

              Yes.

               

              kentuckyfmp wrote:

              Don't really understand the line re the Loop in part 2

              Imagine you have a field with a value list of colours that you use to omit those colours from a Find; then a script that would work with any number of selections could look like:

               

              Set Variable [ $coloursToOmit ; YourTable::gColoursToOmit ]

              # [ might contain a return-delimited list of "Green", "Blue", "Yellow" ]

              Set Variable [ $colourOmitCount ; ValueCount ( $coloursToOmit ) ]

               

              Enter Find Mode [ pause: off ]

              Set Field [ YourTable::size ; 4 ]

               

              Loop

                Exit Loop if [ Let ( $i = $i + 1 ; $i > $colourOmitCount ) ]

                # [ if no omission colour is selected, this loop is exited immediately ]

                New Record/Request

                Set Field [ YourTable::colour ; GetValue ( $coloursToOmit ; $i ) ]

                Omit Record

              End Loop

               

              Set Error Capture

              Perform Find [ restore: off ]

               

              But the non-looped version as given in the first post should also work.

              • 4. Re: Scripts for a find using two fields
                kentuckyfmp

                All works perfectly now.

                Only a simple example I know but it's helped me to understand a lot more about scripts than I knew previously.

                 

                Many, many thanks for your kind help and prompt response.

                Sincerely

                Gordon

                • 5. Re: Scripts for a find using two fields
                  philmodjunk

                  You can take the excellent advice that you've received here and go one step further by setting up a system where the user enters/selects criteria in global fields, then runs a script similar to what you have here to find your records.

                   

                  See the examples of scripted finds found here:

                   

                  Scripted Find Examples

                  • 6. Re: Scripts for a find using two fields
                    kentuckyfmp

                    Thank you Phil.

                     

                    As I mentioned I am a newcomer to both Filemaker and Scripting.

                    What are the advantages of the GLOBAL FIELDS and where are they set up please?

                     

                    I am also 78 years old and housebound through illness but I still enjoy learning. The computer is my day-long companion. I changed to Filemaker from MS Access after changing from Windows to Mac.

                     

                    All feedback greatly appreciated.

                    • 7. Re: Scripts for a find using two fields
                      erolst

                      Simply create a new field of the desired type, then set its storage setting to 'global storage'.

                       

                      The advantage of a global field is that it is accessible everywhere, so you can use it as a means of data exchange between different contexts. (Though that role has largely been taken up by 'script variables'.) That also means it doesn't really matter much in which table you define a global field - unless you need it for a relationship - and it comes down to organization and semantics.

                       

                      Being accessible from everywhere and to everyone can also be a problem, since if a problem arises (wrong value? missing value?), it can be hard to pinpoint what caused it.

                       

                      The other advantage is that a global field only has one value per file (and, in a hosted file, per user), so they are lightweight and individualized and thus ideal for eg certain user interfaces needs.

                       

                      If you'd like to see a deeper discussion of the pros and cons, google for 'global variables scope' (the equivalent of FM fields in general programming are 'variables', which can be a bit confusing at first ... and what in FM is called 'context' is 'scope').

                      • 8. Re: Scripts for a find using two fields
                        kentuckyfmp

                        My grateful thanks for this but too much to take in at short notice.

                         

                        Will play with it and report back in due course.

                         

                        In the meantime, Happy New Year to you and Phil and to all FM'ers.

                         

                        May you all have a great 2017.

                         

                        Sincerely

                        Gordon

                        • 9. Re: Scripts for a find using two fields
                          philmodjunk

                          A global field's value remains accessible while in find mode where non-global fields are empty. That's a key feature that these example scripts rely on to keep the scripts as simple as possible.

                           

                          Try this: put a global and non-global field with data on a layout and watch what happens when you enter find mode.