3 Replies Latest reply on Apr 9, 2013 12:57 PM by philmodjunk

    Searching and Summarizing fields



      Searching and Summarizing fields




           New to FM12 so any help is greatly appreciated. I've built a database that has information regarding time sheets. Inside the time sheet will have a drop down list (value) from Job Number. I need to build a form that when you select a job number it will search all time sheets for that job number. Once it has found all time sheets that have that job number inside of it, it will give me job activities that will have a total amount of time given for each job activities over the whole job. I know I'm being pretty vague on it, so please ask any questions you may have. But I will try to describe it with a graph below.


                                                    Job Number: 12345

           Work Type: Install                       Hours: 42

           Work Type: Programming         Hours: 6                               Total Hours: 50

           Work Type: Training                  Hours: 2  


           Basically, when you choose the job number it will poll all the records for that found job number, find all work type activities that is tied to that job number and total those numbers for each activities then total all the numbers.


           Ive got the time sheets built and I've been able to get the system to search for the records but I can seem to figure out how to tell it to find by work type when only attached to the job number. Or even if I could get a script to run after I choose the drop downs to search for the selected items then run a sum. I'm not sure but thanks for the help                        


        • 1. Re: Searching and Summarizing fields

               You are using Form view with what appear to be different portals for different days of the week? Hope these arent' repeatin fields or we have a real mess here to straighten out.

               You should have a relationship that looks like:


               Jobs::__pkJobID = HoursWorked::_fkJobID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               HoursWorked would have one record in place each row shown in your portals or repeating felds.

               Then you can produce a Summary Report baed on HoursWorked where you can perform a find or use Go To Related Record to pull up all the HoursWorked records for a given Job. Sort these records on a list view with summary fields and sub summary parts and you can get the same report shown here, but grouped by type of work.

               See this thread for a tutorial on Summary Reports: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Searching and Summarizing fields

                 Unfortunately they are repeating fields, but I am in the works of replacing those as I recently read through the forums that they are evil still remnant of the original FM versions.

                 I thought a summary report would be the way to go but I know very little about them and how to manipulate them so when it didnt look right, I kind of went another rout. But that is probably caused by repeating fields



            • 3. Re: Searching and Summarizing fields

                   Repeating fields are almost never the best choice for your database design. They have been superceded by related tables over the last decade and now only have a few very limited uses where they still are advantageous to use.