7 Replies Latest reply on Jun 23, 2009 10:02 AM by philmodjunk

    Finding Non-Blank Records

    Dave1428

      Title

      Finding Non-Blank Records

      Post

      Another question...

       

      So I have a bunch of records with a "notes" text field at the bottom... this field is left blank in many of the records (i.e. if there's nothing to make a note about).

       

      I'm trying to create a report that pulls that field from all the records within a certain date range... however, when I do that, it pulls all the blank ones from that date range as well.  Is there a script/shortcut/etc I can use so that Filemaker will only include the record in the report when the "notes" field is not blank?

       

      Thank you!

       

      Dave

        • 1. Re: Finding Non-Blank Records
          philmodjunk
             Make two requests:

           

          Request 1:  specify your date range and other criteria that you want your records to match.

          Request 2:  put an equals sign by itself in your notes field. Set this up as an "omit request".

           

          In a script:

           

          enter find mode[]

          Set field [table:: Datefield, Dateexpression]

          New Record/Request

          Set Field [table::notesfield, "="]

          Omit Record

          Perform find []

          • 2. Re: Finding Non-Blank Records
            LaRetta_1
              

            "that pulls that field from all the records within a certain date range... however, when I do that, it pulls all the blank ones from that date range as well. "

             

            I'm unsure why you have two requests, Phil, when a date range can't be placed into a find request and your two find requests aren't even used in your following script (no Restore on Enter Find More nor Perform Find) and you change to Set Field[] instead. 

             

            Anyway, this will work as well, if I understand correctly.  It will find records within the date range that ALSO have something in the Notes field:

             

            Enter Find Mode [ uncheck pause ]

            Set Field [ DateField ;  searchDate1 & ".." & searchDate2 ]

            Set Field [ Notes ; "*" ]

            Set Error Capture [ On ]

            Perform Find [ ]

            If [ not Get ( FoundCount ]

            Show Custom Dialog [ OK ; "No records Found" ]

            ... do whatever when no records found

            Else

            ... do whatever if records found

            End If

             

            If you want a User to input the date range, use globals and change searchDate1 and searchDate2 to global dates (values captured by Custom Dialog (input).

            • 3. Re: Finding Non-Blank Records
              philmodjunk
                

              Laretta, we are taking two slightly different tacks on the same issue.

               

              I was imagining the same set field you are using for the date range, but since it can take several forms depending on what you are trying to do:

               

              Set Field [ DateField ;  table::datefield1 & ".." & table::datefield2 ]

              Set Field [datefield ; "3/1/09 ... 3/31/09"]

              Set Field [ datefield ; "3/1/09 ... " & get(currentdate)]

               

              I used a generic "dateexpression" place holder. Your example is much clearer on that step.

               

              I used two find requests. The first finds all records in a given date range. The second omits all records that have a blank note field.

               

              No major difference in result. Just a little different approach to specifying the criteria. Your example adds some useful details (set error capture, show custom dialog etc.) and is a step shorter as you do it all in one request instead of two.

              • 4. Re: Finding Non-Blank Records
                LaRetta_1
                  

                Phil said, "I used two find requests. The first finds all records in a given date range. The second omits all records that have a blank note field."

                 

                This is not a script.  YOu then present a script using Set Field[].  You use one or the other - you can't make find requests, ignore them within Enter Find Mode[] and/or Perform Find[] and THEN set the fields using Set Field[] and expect to pull the information from the find requests.

                 

                No matter which date range concept is used, whether Get ( CurrentDate ) or a global date to accept user Input ... find requests are OUT - they can only be used for static data.

                 

                I posted - not to upset you, Phil, but to provide clarity to the thread - creating find requests outside of that script means nothing.  And your script goes the long way around finding information and can't find information with a variable date input method.   My script is less steps, more concise and works. :smileytongue:  And my name is LaRetta.

                • 5. Re: Finding Non-Blank Records
                  philmodjunk
                    

                  Copied from the database design report of a sample file where I tested this script:

                   

                  • Enter Find Mode [ ]
                  • Set Field [ Table:: Date Field; Table::gdate & "..." & Table::gdate2 ]
                  • New Record/Request
                  • Set Field [ Table::Text FIeld; "=" ]
                  • Omit Record
                  • Perform Find [ ]
                  • 6. Re: Finding Non-Blank Records
                    LaRetta_1
                      

                    I assure you that I understand how scripts work.  What you had posted began with "Make two requests".  Huh?  That implies FIND REQUESTS to a Developer or anyone who has ever used scripted finds.  So look again at what you listed and see if you can spot why I was confused by your post:

                     

                    Make two requests: 

                     

                    Request 1:  specify your date range and other criteria that you want your records to match.

                    Request 2:  put an equals sign by itself in your notes field. Set this up as an "omit request".

                     

                    In a script:

                     

                    enter find mode[]

                    Set field [table:: Datefield, Dateexpression]

                    New Record/Request

                    Set Field [table::notesfield, "="]

                    Omit Record

                    Perform find []

                     

                    Secondly, searching for date range AND something in Notes field is simply more efficient than to find all dates and then create a second request and then omit those without a note.  But I am glad you are testing your own scripts ... it's a good way to learn. :smileyhappy:

                    • 7. Re: Finding Non-Blank Records
                      philmodjunk
                        

                      In the script:

                      Line 1 enter find mode [] creates the first find request.

                      Line 3 new Record/request creates the second request.

                       

                      Hence "create two find requests".

                       

                      The first part of my original post describes the process in a general form that applies both to scripting and manual finds.

                       

                      The second part describes a scripted version of the method.

                        

                      I agree on your efficiency comment, but find that in most cases there is not a noticable difference unless you are dealing with very large numbers of records in your table.

                       

                      PS. I did say your method was better :smileywink: