5 Replies Latest reply on May 10, 2012 9:49 PM by PeterThorton

    Creating a convenient "search form"



      Creating a convenient "search form"




      I have the following problem:

      I have an "Input order form" layout, which allows the users to create orders. An order is related to either a Customer or a Dealer (depending on which way the order will go) So I have a table Order, which is related to table BusinessPartner, through a join table (called OrderPartnerLookup. Why I need the join table is not really important, but if you need to know, ask) BusinessPartner has a "Type" field that's either set to "Customer" or "Dealer".

      On the layout I have a textbox that represents the partner. Until now it was set up as a dropdown list, which showed all the partners, but this is no longer sufficient. I've been asked to do some kind of "search box" that would allow the users to search by Name, ID, email, etc. The search box should only search either customers or dealers, depending on where it's called from.

      As I'm new to Filemaker, so far I have no idea how to do this. I thought I'd make a 'search layout', and a script that would switch to this layout and enter search mode when a button is clicked. Then another button that would navigate back to the original layout and fill in the selected value. But I'd also like the user to see a list of records that match the criteria, and be able to select one. Maybe I should base the layout on a list view?

      I've been experimenting with this for a while, but judging by my experience with FileMaker so far, it may well take me days before I produce something usable. Any help, suggestions, and ideally samples, would be greatly appreciated.

        • 1. Re: Creating a convenient "search form"

          There's more than one way to do this, but the following scripted method is likely the easiest to add to your solution.

          Define global fields for the user to use to enter criteria to be used in this search. Global fields used for this purpose can be defined in any table. I usually define a separate table, Globals and put such fields there just to make them easier to manage. If you do not specify global storage for these new fields, the following example script will not work.

          The user enters data in these fields and then performs a script by clicking a button (or via a script trigger) to do something like the following:

          Freeze Window
          Go to Layout [BusinessPartner (BusinessPartner)]
          Enter Find Mode[] //clear the pause check box
          Set Field [Businesspartner::Name ; Globals::gSearchName]
          Set Field [BusinessPartner::Email ; Globals::gSearchEmail]

          and so forth for each field you set up for search criteria...

          Set Error capture [on] //keeps error message from appearing if no records are found
          Perform Find []
          If [ Get (FoundCount) = 1 //only one record found]
             Set Variable [$ID ; BusinessPartner::PartnerID ]
             Go to Layout [Original Layout or layout based on join table]//you may need to create a new record in the join table here
             Set Field [FieldwhereIDneeds to be entered ; $ID }
             Go to layout [original layout] //if you updated a record in the join table.
          Else IF [ Get ( FoundCount ) // more than one record found
             Go to layout [PartnerList ( BusinessPartner)]
             Go to layout[original layout]
             Show Custom Dialog ["No business partners matching this info were found'}
          End IF

          Note that I don't know exactly how you want the results of this search to be used. If you are creating a new record in join table, the code is a bit different than if you are just updating a field in your orders table. The script also goes to a new layout you'd create with a list view for listing records when more than one matches the search criteria. A button in the layout body would then run another script that selects that record and enters its ID number where needed, the code for it will be identical to the code in the above script for what takes place when just one record is found.

          • 2. Re: Creating a convenient "search form"

               Go to Layout [Original Layout or layout based on join table]//you may need to create a new record in the join table here

            Yes, I obviously need to create a new record in the join table. But HOW? I've tried the first part of the find script (the "If [ Get (FoundCount) = 1)" part) The search works, it finds the business partner I need. However, when the script returns me to my previous layout, nothing happens. The field remains empty, and a glance at the OrderPartnerLookup table reveals that no record has been added.

            So how do I create the new record? My original layout is based on the Order table, so if I use New Record/Request after I navigate back, I'll make a new Order, right? And the "search form" is based on BusinessPartner, so I'd be creating a new business partner. I haven't seen a script step that would look like this "New Record[Table name]" so I'm assuming there isn't one. The only way I can think of is create a layout based on OrderPartnerLookup, navigate to it in the script, call New Record/Request, fill in the data and then navigate back to my original layout. Seems kinda roundabout to me, but I'll give it a shot.


            • 3. Re: Creating a convenient "search form"

              Alright, it seems to be working. I've modified your script slightly to fit my needs. (I would have posted the whole script here, but it's pain. I can't believe you can print out a script, but you can't copy or save it as a plain text)

              I create the join table by first going to the layout for that table, making new record, filling in the data, and then going back to the Order Management layout.

              One thing I need to do is restrict the search to a specific type of business partner. If launched from the Customer Order, it should only show customers. If launched from the Dealer Orders, it should only show dealers. I managed to do this, somehow.

              I used a variable $$PartnerType, where I loaded the appropriate value, which I then put in the field Globals::gSearchPartnerType, and then after entering find mode I set the field like this:

              Set Field [Businesspartner::Type; Globals::gSearchPartnerType]

              there may be a more direct way of doing it, but at least it works. Well, For the most part.

              When the user does a search and he finds nothing, I don't want to navigate back to the original layout. I want them to try again. If they're done searching, they can always hit the 'back' button that I put on the layout. So I've modified the end of the script, (the last else, around the "record not found dialog") to simply enter find mode again instead of navigating away. It works, except after the first search it forgets about the Type constraint and displays dealers along with customers. How can I fix this?

              Also, on the list layout, I want to include a "back to search box" button, so the user can try again if they don't see the customer they're looking for in the search result list. What do I need to do in order to setup the search layout to work properly again?

              • 4. Re: Creating a convenient "search form"

                One thing you don't do is return to find mode. The script is set up so that the user enters search criteria into global fields while in browse mode. Then a script enters find mode and builds the needed find request and performs the find. The reason that the script returns to the original layout is that I've assumed that your global fields for entering search criteria are on that original layout. Thus, by returning to the original layout, the user has the opportunity to modify the data in the global fields and try again.

                Thus, it all depends on where the user is entering search criteria. If you set up your system so that a button changes layouts to a layout with the global fields or opens a samll dialog window for entering the criteria, your "try it again" code would need to return the user to that layout or re-open that window with that layout. On the other hand, if you place the global fields on your order layout, then your script should do as my example script and return the user to the order layout.

                To post a script to the forum:

                1. You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
                2. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
                3. If You have FileMaker advanced, you can generate a database design report and copy the script as text from there.
                4. If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)
                • 5. Re: Creating a convenient "search form"


                  I solved it, after the dialog box I just tell the script to return to find mode and set the BusinessPartner::Type again. The user doesn't have to enter values for the global fields anywhere at all. In fact I found them completely redundant. I removed them and the scripts still work perfectly.