7 Replies Latest reply on Apr 11, 2011 10:01 AM by philmodjunk

    Going Crazy ... how do I create a simple search form that gives me a report?

    KathyCastle

      Title

      Going Crazy ... how do I create a simple search form that gives me a report?

      Post

      Hi All,

      I actually have searched for this information everywhere but nowhere is the answer clear. Bits of it are clear, but not the whole process.

      Background is that I have been given a Mac with Filemaker Pro 11 on it and told to convert our MS Access databases to Filemaker Pro. Okay, so I choose an easy database and get started on it .... all is good in terms of importing the data and then creating input forms. In fact, it is quite easy and I am feeling optimistic.

      But then I start to look at output. Yes, the find function is very powerful, but I don't really want to let the users loose on every table trying to work out their find queries. I want them to be able to search for the specific information they want easily. So, I want to create a form that looks a bit like:

      Select Trainer <selection box with trainer name>

      Select Course <selection box with course names>

      Select date range from <start date> to <end date>

      Submit button.

      I then want the variables in this form to be inserted in a search query or find that locates the relevant records and then displays the results in a report - in this case a report that looks a bit like:

      Trainer Name: John Bloggs

      Average results:

      Question 1    Question 2  Question 3  Comments

      5                  4                  4             Positive Comments  70% Negative Comments 26% N/A 4%

      Now, this is pretty easy in Access - takes a little fiddling for sure but it's all doable. How can I do this in Filemaker? I can find bits and pieces of this process but nowhere can I find how to link it all together. Is it even possible? Does anyone know something I can read or can post an example?

      Thanks very much for any answers - I am going a bit crazy trying to sort out what is, in my mind, a very simple issue which has become very complex. I am kind of wondering if Filemaker Pro is really suitable at this point.

        • 1. Re: Going Crazy ... how do I create a simple search form that gives me a report?
          Frinholp

          Create two layouts, one for search and one for results.

          Create a Search Layout based on your table you are searching.

          Select Trainer <selection box with trainer name>

          Select Course <selection box with course names>

          Select date range from <start date> to <end date>

          Add these fields to the search layout and make sure the fields can be entered in find mode. I am assuming you already have the knowledge to populate your drop-down box.

          Create a Results Layout based on the same table.

          Trainer Name: John Bloggs

          Average results:

          Question 1    Question 2  Question 3  Comments

          5                  4                  4             Positive Comments  70% Negative Comments 26% N/A 4%


          Add these fields to the layout.

          Create a script called Enter Find Mode or something similar.

          Add this line to your script - Enter Find Mode

          On your layout options, attach this script to the OnLayoutLoad script trigger.

          Create another script called Submit Find.

          Add these lines to your script -

          Perform Find []

          Enter Browse Mode []

          Go to Layout [Results Layout]

          Attach this script to your submit button.

          Lee

          • 2. Re: Going Crazy ... how do I create a simple search form that gives me a report?
            philmodjunk

            If you search out "scripted find" here, you should find numerous posts with this alternative approach to setting up a controlled search of the database:

            Define and extra set of field for: gTrainer, gCourse, gStartDate, and gEndDate. These fields are not the actual fields where this data is stored, they will only be used on your search layout and by the find script shown below. Give each field the appropriate data type and specify global storage for each. (The following script will not work unless the fields are global.)

            You can place these fields on a dedicated search layout with a button to perform the following script:

            Enter Find Mode[]
            Set Field [YourTable::Trainer ; YourTable::gTrainer]
            Set Field [YourTable::Course ; YourTable::gCourse]
            Set Field [YourTable::CourseDate ; YourTable::gStartDate & "..." & YourTable::gEndDate ]
            Set Error Capture [on] //suppresses "records not found" dialog if no records are found
            Perform Find[]

            There are even more sophisticated versions of this method that pop up the search layout in a small floating window with a script parameter used to determine whether to clear the global fields or leave the data from the previous search. An example of this can be found in the Known Bugs List Database.

            • 3. Re: Going Crazy ... how do I create a simple search form that gives me a report?
              Frinholp

              Phil, I've used the technique I provided as an answer within in my own system.

              As you may have gathered, I am always keen to further my development techniques.

              If you could 'in a nutshell' explain the benefits of the technique you have proposed, I would be very greatful; as ever.

              Lee

              • 4. Re: Going Crazy ... how do I create a simple search form that gives me a report?
                philmodjunk

                Frinholp, there's nothing intrinsically wrong with your approach and apologies if I suggested that in any way.

                With the global field approach, you can further insulate the user from the technical details needed to specify criteria for certain kinds of finds. Note how the user can enter a start date and a stop date and doesn't have to know how to use inequality operators or the range (...) operator, instead the script takes care of those details for them.

                • 5. Re: Going Crazy ... how do I create a simple search form that gives me a report?
                  Frinholp

                  Useful and to be considered.

                  I have three levels of user privileges; admin, super-user and user.

                  I will consider creating a seperate layout, using this technique for the user level.

                  Cheers

                  Lee

                  • 6. Re: Going Crazy ... how do I create a simple search form that gives me a report?
                    KathyCastle

                    Frinholp - thanks very much, your answer allowed me to get a query working and produce a report. 

                    PhilModJunk - I am also very interested in your answer but find myself stymied by the most simple problem... when you say 'define a field' where do I define it? If I have to set up a layout that uses those defined fields, how do I select them when the software only allows me to choose tables and fields within them? I feel like there are all these secret connections that aren't clarified anywhere. You give your answer as though this is obvious but I can't see how exactly to do it. 

                    Arrgh - it is frustrating. 

                    To be honest it is worse than this - currently I can successfully get the find layout to speak to the report layout using Frinholp's method. The next problem is then getting an average for each field. I am looking at a feedback database where people choose from 1 to 5 to indicate their satisfaction level. I would need to average the total find result for a trainer for each question. I tried adding a summary field in the database for each question but I can't get it to do more than show ? most of the time, in both subsummary and grand total subsections. Once it worked and I don't know why - the next time it didn't. 

                    Anyway, I hate having to use fields in the database anyway - I am used to clean databases and scripts that do the work. But again, I cannot see how to calculate the summaries, create a dummy field and place this in a report. The Layout view seems to require that I select a data source and then add it to the layout. 

                    I'm totally confused about this aspect of filemaker - it is annoying seeing as data input problems were so clean and easy. 

                    Thanks again for the help - I can at least get simple forms to work now! :)

                    • 7. Re: Going Crazy ... how do I create a simple search form that gives me a report?
                      philmodjunk

                      "when you say 'define a field' where do I define it? If I have to set up a layout that uses those defined fields, how do I select them when the software only allows me to choose tables and fields within them? I feel like there are all these secret connections that aren't clarified anywhere. You give your answer as though this is obvious but I can't see how exactly to do it. "

                      Open Manage | Database | fields, select a table, enter the field names and click create. After creating them, either click options or double click the definition line to bring up field options where you can specify global storage. Global storage fields used for everything but relationships can be defined in any table and will still be accessible to you from any layout in your database. I usually define a "globals" table and put all such global field definitions in this one table as it makes it easier to keep track of them. You can think of these global fields as having uses similar the "text boxes" you can create in Visual Basic or MS Access where you define the data source for them as an expression directly in the form. (Yeah I know, it's defined at the data level not the presentation (form) level, but that's a trade off that can actually be useful once you get used to the difference.)

                      Sounds like you are a VB programmer and/or an MS Access programmer. FileMaker uses a different paradigm for many things and it will take some doing to get used to them. Having to define calculation fields inside tables takes some getting used to. I had to make this "paradigm shift" in reverse when I first taught my self VB and MS Access. I thought it very strange that I couldn't define a calculation field in the table, but had to define it in the form, a script, or the SQL based Recordsource.

                      Now that I've done it both ways, I can see there's a trade off with either approach. "Data only" tables look very simple and "clean" and can thus be easier to work with, plus importing data into them can be quicker as there are much fewer indexes to update during import. However, I've also found it very, very useful to be able to define a single calculation field in the table and know that every reference to it on every layout and in every script will get exactly the same calculation expression and if I need to update the expression, I can update it in one place and don't have to update it identically in multiple forms, SQL expressions and scripts... (And FileMaker has gradually added more and more ways to get the results of calculations into a layout without having to define a special field for the purpsose.)

                      You may find this thread, and the link in it to an even more technical discussion of the same design aspect of filemaker, useful in order to better understand how you can refer to data in multiple tables from a single layout and/or script: 

                      Tutorial: What are Table Occurrences?