7 Replies Latest reply on Jun 22, 2009 7:02 AM by LeoCA

    Find Records with Conditions

    LeoCA

      Title

      Find Records with Conditions

      Post

      We have a Layout (Report) based on a Vendor_Invoice table. The table has columns of Vendor_Name, January_Invoice, ..., December_Invoice. How can we just show Vendors that have missing monthly invoice (=0), regardless of which month?

      We have try "Perform Find" and "Enter Find Mode". But have problem of specifying conditions (basically January_Invoice=0 OR ... OR December_Invoice=0) in either methods. Can anyone help?  Thanks.

       

      LeoCA

        • 1. Re: Find Records with Conditions
          comment_1
             You can create a separate request for each field. However, I think you should re-examine your data structure instead.
          • 2. Re: Find Records with Conditions
            LeoCA
              

            I don't quite follow "a separate request for each field".  Does this mean 12 REQUESTs?  How to get end results in a single output?

            I don't see this has anything to to do with data structure.

             

            Similar question:  A vendor table contains vendor information in all 50 states.  Want to get a list of vendors that are in a list of states (MD, VA, NC, ...). The states are changed depending on requests.  What approach can you suggest?

             

             

            • 3. Re: Find Records with Conditions
              TSGal

              LeoCA:

               

              Although the answer provided by "comment" is correct (Thank you!), let me expand a bit.

               

              When you go into Find Mode, you can place a 0 (zero) in January_Invoice field and press return.  As expected, this returns all records where the January_Invoice field is 0.

               

              If you return to Find Mode and place a 0 in January_Invoice AND a 0 in February_Invoice and press return.  This will return all records where the January_Invoice field is 0 AND February_Invoice is 0.

               

              Return to Find Mode and enter a 0 in January_Invoice.  Before pressing return, pull down the Requests menu and select "Add New Request".  It looks like your original entry in January_Invoice has been deleted.  However, if you look in the Status Bar (if visible), you will see you are in the second Find Request.  Enter a 0 in February_Invoice.  When you press return, you will return all records that have either 0 in January_Invoice OR 0 in February_Invoice.

               

              Therefore, what you want to do is create 12 different Find Requests, placing 0 in the appropriate month for each request.

               

              Let me know if you need any clarification for the above steps.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Find Records with Conditions
                philmodjunk
                  

                Since you are likely performing this same task repeatedly, you can script the technique:

                 

                Enter Find mode[]

                Set Field [table::JanuaryInvoiceField; 0]

                New Record/Request

                Set Field [table::FebruaryInvoiceField; 0]

                New Record/Request 

                //repeat for each month

                Set Error Capture [on]

                Perform Find[]

                Set Error capture [off]

                 

                What Comment is suggesting is that a restructuring of your tables might greatly simplify this process and eliminate the need for 12 requests.

                • 5. Re: Find Records with Conditions
                  LeoCA
                    

                  Thank you TSGal.  I tried it in FIND mode and got results I need.

                   

                  Do you happen to have scripts that adding requests that I can use.

                   

                  LeoCA

                  • 6. Re: Find Records with Conditions
                    TSGal

                    LeoCA:

                     

                    In case the posts crossed one another, please look at the excellent script example from "PhilModJunk" above.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: Find Records with Conditions
                      LeoCA
                        

                      Script from "PhilModJunk" is a perfect solution.  Thanks.

                       

                      LeoCA