5 Replies Latest reply on Jun 9, 2010 2:06 PM by philmodjunk

    Question on Setting Up a Complex Find Formula

    bt22true

      Title

      Question on Setting Up a Complex Find Formula

      Post

      I am trying to set up a button script, but I am pretty new to Filemaker. Ideally, here is what I would like to do:

      On pressing a button, I'd like to enter find mode in a single feed (lets call it Product_ID) where I could punch in a number to search my records for. If the search is successful, I'd like the script to end, but if I do not find a matching record, I'd like a new record to be automatically created with my search phrase being entered into the new records "Product_ID" field.

       

      Is something like this possible? Ideally, I would like to have a search dialog box open up in the beginning where I would enter the search term. 

       

      Any help much appreciated.

       

      -Brett

        • 1. Re: Question on Setting Up a Complex Find Formula
          TKnTexas

          Yes, this is very do-able.  I used to do this for a daily sales database.  I only wanted one record per date.  

           

          I had a field called Sales_Date.  I had a field called gSales_Date (global version).  

           

          My script prompted for gSales_Date, the date in question.  It switched to find mode, pasted the value and did the find.  If the find error was not there (by code number), it branched to create the record and paste the date into the Sales_Date field, moving to the next field for data entry.  

           

          If it was found it took the user to the record.  

          • 2. Re: Question on Setting Up a Complex Find Formula
            bt22true

            I'm new to Filemaker, so I'm still trying to figure out scripting. How would a script like yours look? 

            Thanks for the help. 

            • 3. Re: Question on Setting Up a Complex Find Formula
              philmodjunk

              Define your global Product ID field and name it: gProductID   (Global storage is a field option you'll need to set or this script won't work.)

               

              Your script would look like this:

               

              Enter Find Mode []  //clear the pause option

              Set Field [YourTable:: Product_ID ; YourTable::gProductID ]

              Set Error Capture [On]

              Perform Find []

              If [ Get ( FoundCount ) = 0 ]

                 New Record/Request

                 Set Field [YourTable:: Product_ID ; YourTable::gProductID ]

              End If

              • 4. Re: Question on Setting Up a Complex Find Formula
                bt22true

                Thanks so much for the help. 

                It doesn't seem to be working though. In your script, I don't see where I would be prompted for the Product_ID value I'm trying to find. Any suggestions? 

                • 5. Re: Question on Setting Up a Complex Find Formula
                  philmodjunk

                  In my example, you enter the product ID in the global field gProductID and then run the script to use it to first search for the record and then create a new record if one does not already exist.

                   

                  You could use Show Custom Dialog with an input field that refers to gProductID to get the input from the user if you want.

                   

                  The script also had a typo in the If step which I've now fixed. (Changed a ) into a 0 )

                   

                  Another approach could be used that doesn't use any scripting at all.

                   

                  Set a validation rule on your product ID field to specify only unique values. The system will then display an error message if you enter a product ID that already exists.