7 Replies Latest reply on Jun 16, 2010 5:23 PM by TSADesign

    Query Based Upon Multiple Fields

    TSADesign

      Title

      Query Based Upon Multiple Fields

      Post

      I am writing an database that will be interacted with by a client's client. The primary application for this database is that it will list all open invoices for the client's client, along with additional information that they would like to  search on that isn't available through my clients normal invoice system. Since I won't have any access to the client, I am trying to make this as simple as possible for them to query the data, without having to know how to use the Find, Extend Find, Constrain Find searches.

       

      I have created a standard find that will be used to find records based upon a date range using the following fields: gDate1 (beginning date = global field) and gDate 2 (end date = global field). There is then a Flag field that has the following calculation - Case (Date of Invoice ≥ gDate1 and Date of Invoice ≤ gDate2 ; 1 ; 0 ) - this all works perfectly an I wrote a script that does a find on Flag = 1.

       

      Where I'm running into problems, is that there is second field (Store Number) that they MAY want to search on. I say MAY because they may just want to do a search on a date range, they may want to do a search on store number activity, or they may want to do a search on store number activity for a specific date range.

       

      I created a second Flag for store number using the same idea as above, then created a third flag if there is a date range and store number entered.

       

      I was trying to manage the sorts using if and else if statements, but i'm not familiar with scripting and I can't get this to work. A version of the script is below:

      If [Invoice Details::Flag 3 = 1]

          Perform Find [Restore]  {find is Flag 3: [1]}

      Else

         Perform Find [Restore] {find is Flag 2: [1]}

      Else

        Perform Find [Restore] {find is Flag; [1]}

      End If

       

      I'm sure there is an easier way to do this than with Flags - in fact I trying using Set Variable and Set Field scripts, but couldn't get those to work either. The Flags at least provide me a way of seeing if the Find is flagging the right data.

       

      Thank you,

        • 1. Re: Query Based Upon Multiple Fields
          comment_1

           


          TSADesign wrote:

           

          There is then a Flag field that has the following calculation - Case (Date of Invoice ≥ gDate1 and Date of Invoice ≤ gDate2 ; 1 ; 0 ) - this all works perfectly an I wrote a script that does a find on Flag = 1.


           

          That's not only unnecessary - it also slows down the search (since the calc musr be unstored).

           

          I think you could catch all posssiblitites by:

           

          Enter Find Mode []

          Set Field [ Date of Invoice ; gDate1 & ".." & gDate2 ]

          Set Field [ StoreNumber ; gStoreNumber ]

          Perform Find []

           

          Adding some error handling might be in order, too.


          • 2. Re: Query Based Upon Multiple Fields
            philmodjunk

            The following script only works if the fields starting with g are set in field options to have global storage.

             

            Put the following fields on a search form layout for your users. They can fill in the fields and then click a button to perform this script:

             

            Go to Layout [Invoices]

            Enter FInd mode[]

            If [ Not ( Isempty ( Invoices::gDate1) and IsEmpty ( Invoices::gDate2 ) ]

                Set Field [Invoices:: Date of Invoice ; Invoices::gDate1 & "..." & Invoices::gDate2 ]

            End IF

            Set FIeld [Invoices::Store Number ; Invoices::gStore Number ]

            Set Error Capture [on]

            Perform Find[]

             

             

            You can add a step to sort the found records if desired and you can also pop up a custom dialog to tell the users no records were found if get ( foundcount ) = 0 if you want.

             

            To examine what a script did while performing a find, run the script and then select Modify Last Find while on a layout that shows all the fields where criteria may have been entered by the script.

             

            PS. If you are doing business as a database consultant, get filemaker advanced. The debugger and database design reports will save you thousands of hours of hair pulling and are worth every penny of the higher sales price.

            • 3. Re: Query Based Upon Multiple Fields
              TSADesign

              Thank you for the suggestion. This works to find everything that fills the category, such as a date range and a store number, but not a store number for a specific date range.

               

              As an example, I selected just a store number and it worked perfectly, I selected just a date range and it worked perfectly, however If I wanted a store number for a specific date range, I got all fields that met the date range - including those that met the store number. What I need to be able to do is to filter out all items that meet the date range (Flag) and the Store Number (Flag 2) = this is what I was trying to do with a Flag 3.

               

              Thanks again - look forward to hearing back from you,

              • 4. Re: Query Based Upon Multiple Fields
                philmodjunk

                I don't follow that.

                 

                "If I wanted a store number for a specific date range, I got all fields that met the date range - including those that met the store number."

                This appears to describe exactly what you requested in your original post but now you say that isn't what you want.

                 

                Are you trying to exclude records such as "find all records from January 1 to January 10 except for those for store 54321"?

                 

                As written, if you enter Jan 1 and Jan 10 dates, specify store 54321, and run the script. It should find all records for store 54321 dated from Jan 1 to Jan 10.

                 

                If it doesn't then something is wrong here...

                 

                • 5. Re: Query Based Upon Multiple Fields
                  TSADesign

                  This worked perfectly - thank you!

                  • 6. Re: Query Based Upon Multiple Fields
                    TSADesign

                    Sorry for the confusion I tried the first solution presented first before trying yours.

                     

                    Just a point of clarification using this script, it would seem I can do away with the flag fields correct?

                    • 7. Re: Query Based Upon Multiple Fields
                      TSADesign

                      No need to respond - I tested this and determined that I was able to eliminate those fields.

                       

                      Thanks again for your help,