8 Replies Latest reply on Feb 3, 2014 8:51 AM by landry

    How to retrieve and sort info from table for a report

    landry

      Title

      How to retrieve and sort info from table for a report

      Post

           Hello all

           I'm having trouble getting my head around this problem and could use your help.

           My db contains a table that tracks info for the state's youth court system (see attached). I need to be able to retrieve info from this table by category, county, race and gender so that I can answer queries such as, "how many white female teen volunteers were there in Davidson county this quarter?" i.e, how many #race# #gender# #category# were there in #county# this #quarter# of #year#?

           I'm sure that this is possible, but I'm having trouble wrapping my head around how to retrieve that info. I can look at the image and quickly see that the answer is zero. There were no white female teen volunteers during the 3rd quarter of 2013 but I'm fuzzy when it comes to logic needed to retrieve that info.

           I am thinking that a script is needed: something to parse each line of the database and assign quantities to variables: e.g., $numWhiteFemaleTeenVolunteersDavidson = 0, $numWhiteMaleTeenVolunteersDavidson = 12, etc. Then I'd retrieve the values of those variables to create the report we need.

           I am new to Filemaker scripting, but am handy with basic Javascript and PHP however, this sort of thing is just past the limits of my knowledge. Can someone point me in the right direction? What is the best way to handle this issue?

           Thanks

            

      youth_court_sample_info.png

        • 1. Re: How to retrieve and sort info from table for a report
          philmodjunk

               There are two parts to what you want to do: 1) finding the records that match the specified criteria 2) displaying the results in a format that effectively communicate aggregate values such as a total or average computed from data in possibly many different records.

               1) involves setting up a system for performing a find. While this can just be done "manually" from the tools provided by FileMaker, it is often a more user friendly set up to use a special layout for specifying the criteria and a script that takes the data in those fields to build the needed find request(s) to pull up a group of records. See this thread for examples of scripted finds: Scripted Find Examples

               2) usually requires setting up a summary report layout to be used to display results based on the find from 1). But other report formats are also possible. See this tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: How to retrieve and sort info from table for a report
            SteveMartino

                 Obviously, Phil's response is best.

                 But just for quick viewing, couldn't you in Table View, Click the Find Icon, and type in the proper parameters in the proper boxes and Hit Perform Find?

            • 3. Re: How to retrieve and sort info from table for a report
              philmodjunk

                   Well, my answer may not be "best" in all situations. Shaving with "Okham's Razor" (the simplest solution is often the best solution...) remains a cardinal rule of good database design.

              • 4. Re: How to retrieve and sort info from table for a report
                landry

                     Busy with other projects right now, but will get back to this soon. Thanks for the tips.

                • 5. Re: How to retrieve and sort info from table for a report
                  landry

                       Hi Phil

                       I'm making progress building scripts that find the specified records, then add the values together. See attached image for a sample script that does the following:

                         
                  1.           Initialize variables
                  2.      
                  3.           For each variable           
                                     
                    1.                     Perform find
                    2.                
                    3.                     Set variable
                    4.           
                         
                  4.      
                  5.           Summarize all variables

                       This method seems to work ok, but I'm concerned that it might slow things down once the database is full of real data. By then, I'll have ten versions of the script running as follows:

                         
                  •           Find teen (females || males)
                  •      
                  •           Find adult (females || males)
                  •      
                  •           Find (female || males) cases
                  •      
                  •           Find (female || male) status
                  •      
                  •           Find (female || male) recidivists

                       Is there a better way to accomplish this or should I keep doing what I'm doing? It seems that I should be able to have the search parameters stored in an array or something and call them when I need them so that the script itself would iterate through the various combinations of male, female, teen, adult, recidivists, etc. Maybe I'm over-thinking it though.

                       I haven't quite got the hang of Summary Tables. It seems like I may be able to use them to summarize my variables rather than do it in a script, but I'll need to review that part of the documentation.

                       Thanks for the help. I'll get back to you with more questions.

                       Landry

                  • 6. Re: How to retrieve and sort info from table for a report
                    philmodjunk

                         Sorry, but this is very different from what I am recommending and it could be very time intensive when working with large data sets.

                    • 7. Re: How to retrieve and sort info from table for a report
                      landry

                           That's what I figured. I'll go back to the links you sent earlier and see where I went off track.

                           thanks

                      • 8. Re: How to retrieve and sort info from table for a report
                        landry

                             Good news! I have resolved the issue. I'm meeting with my client tomorrow to present my solution and I am confident it will meet her needs.

                             After working on the script above and realizing that it would never work with a large dataset, I went back to the spreadsheet I'd built to help me map out the data. I realized that by adding a few formulas and linking worksheets together, I could achieve the desired result quickly and easily. It is not a perfect solution, but it's good enough to do the job at hand.

                             So, thanks Phil and Steve for helping me think through this issue.

                             Best regards
                             Landry