6 Replies Latest reply on Feb 27, 2012 1:57 AM by Mike_Mitchell

    How to find matching fields and then go to first record in layout?

    cgallardo

      Hi all,

      I am very new to Filemaker.I have only been using FM10 for a few hours every week since the start of the year and have very basic knowledge of databases so I am sorry if I use the wrong jargon.

      I think my database tables and relationships are ok as when testing via the table views everything works as expected. Just having a bit of trouble setting up the user interfaces and some of the scripts.

       

      This is (part of) my project:

      MENU LAYOUT

      Has a drop down box with the names of shops and another box with a calendar to select shipping dates.

      There are also 2 buttons, Cancel and OK

       

      This layout is attached (sorry if it is the wrong definition) to a Table (that is not related to anything, just a standalone table) called TempVariables. I didn't want to use a custom dialog box as I thought this way I get more control on formatting and scripting.

      On Layout load, a simple script is run to silently request a new record creation.

       

      Once the data is entered and the user clicks OK, the following script runs:

      1. Data is copied to global variables ($$ShopName and $$ShipDate). This is working
      2. A delete record request without dialog is done(to keep the TempVariable table 'clean').This is also working.
      3. Goto LAYOUT 2 - Display Purchase Order (attached to Orders Table). It is displaying the layout, but...
      4. ... and this is where I'm getting stuck. I am trying to use the global variables to find and display the first record that has matching fields for the Shop Name and Ship Date as defined in the Menu layout. If it does not find the record then it should silently create a new record. If it does find a record, the user can then edit the order (provided the ship date occurrs in the future, but that is a challenge I hope I can tackle this one on my own once I get there). I am able to get it to create a new record and preset the fields using the global variables, I just can't get it to go straigth to an existing one.

       

      I had also created a calculated field in the Orders Table called ShopName-ShipDate, this as you may have guessed is a text field that concatanates the ShopName and ShipDate. I had done this hoping that I could use that field as some sort of an Index to make it easier to go to the desired record.

       

      Thanks for you help.

        • 1. Re: How to find matching fields and then go to first record in layout?
          cgallardo

          An update...

          I set up this script (see below) and when a matching record is found it displays it. However, if no record is found it pops up a message (no records match this find criteria)

           

           

          Now I am trying to find a way so that if no record is found a New Record request is made. I am thinking of using the field ShopName-ShipDate ,as explained above, and do a count (before the find) on the number of occurrences of this field where the contents are as per the concatanated variables. Then using If/Then statements to go one way or the other (i.e. perform find or create new record). Any ideas? I need something similar to count but that would return a count of matching  instead of non blanks.

           

          Thanks again.

          • 2. Re: How to find matching fields and then go to first record in layout?
            cgallardo

            Update 2:

            Here is the script I ended up making up, is this the best/correct way? It seems to be working...

             

            Capture.JPG

            • 3. Re: How to find matching fields and then go to first record in layout?
              luistavera

              You can also use Get(FoundCount) to get the number of found records

               

              If[Get(FoundCount) = 0]

                New Record/Request

                .

                .

              End If

               

              And you may want to turn off error capture after this

               

              Set Error Capture [Off]

              1 of 1 people found this helpful
              • 4. Re: How to find matching fields and then go to first record in layout?
                Mike_Mitchell

                Hello, cgallardo. Welcome to FileMaker.

                 

                I think your difficulty may be coming from the fact that you're trying to restore the Find requests. This will cause FileMaker to attempt to find whatever record was being found when the Perform Find step was saved in the script. Try this instead:

                 

                Enter Find Mode [ ]

                Set Field [ Orders::ShipDate ; $$ShipDate ]

                Set Field [ Orders::ShopName ; $$ShopName ]

                Set Error Capture [ On ]

                Perform Find [ ]

                Set Error Capture [ Off ]

                If [ not Get ( FoundCount ) ]

                  New Record / Request

                  Set Field [ Orders::ShipDate ; $$ShipDate ]

                  Set Field [ Orders::ShopName ; $$ShipDate ]

                  Set Field [ Orders::OrderDate ; Get ( CurrentDate ) ]

                End If

                 

                Other tips:

                 

                - You probably don't need to create a new record in your temporary table. You can use fields with global storage instead for the user to input into. This will also allow you to use the global fields directly rather than relying on variables (not that variables are bad, of course; it just makes things more straightforward).

                - For OrderDate, if the default is the current date, you can set this to enter automatically through the Options / Auto-Enter in the Manage Database dialog and spare yourself a script step.

                 

                HTH

                 

                Mike

                1 of 1 people found this helpful
                • 5. Re: How to find matching fields and then go to first record in layout?
                  cgallardo

                  Thanks Mike,

                  Set error capture makes sense, however I do not understand the difference between Perform Find and Perform Find[Restore]?

                   

                  Your answer also helped me better understand global storage fields! (for my records and if helps someone else further info at: http://help.filemaker.com/app/answers/detail/a_id/5895/~/working-with-global-fields)

                   

                   

                  The script is working fine now though, but these answers help to improve it.

                   

                  Thanks again.

                  • 6. Re: How to find matching fields and then go to first record in layout?
                    Mike_Mitchell

                    Glad it helped. When you restore the Find requests, FileMaker will perform the Find using the same criteria that were in the buffer when the script step was saved, every time you run the script (or whatever criteria you tell it to use). This is very handy if you always want to use the same Find - like when you run a report of a particular department, for example.

                     

                    However, in this case, it's not going to work so well, because you're wanting to generate the Find criteria on the fly, as it were. You want to have FileMaker look for a particular set of records based in what you tell it when the script runs. If you restore the Find requests, it will look for whatever records are stored when you save the script. So, for example, if you did a Find for a particular order, then saved the script, it will always look for that particular order, not what you saved in your variables when the script was run.

                     

                    Short version: If you want to look for the same Find every time, then restore the Find requests. If you want to create a different Find each time, then don't.

                     

                    HTH

                     

                    Mike