6 Replies Latest reply on Dec 7, 2011 9:30 AM by Stephen Huston

    Need Help with Subsummaries

    james_callery

      Hi,

      I'm trying to make a FileMaker Pro 7 database where I can track all of our employees and the checks we wrote to them as independent contractors. From this I'd like to create a layout that is a letter that we send to them confirming that the total amount is accurate.

       

      Underneath their total amount earned, I would like to list every check (no more than 7) as well as the check #, the description of the job, and the amount on the check. I believe this is something like a subsummary but I'm having problems setting it up.

       

      So far, I have a personnel records table setup just fine, which has their first name, last name, address, city, state, zip, SSN#, etc...but all I have for the income is a total amount which is calculated through an excel spreadsheet I have.

       

      The excel spreadsheet looks something like this:

       

      First Name
      Last Name
      Title
      Date
      Description
      Check #
      Amount
      JohnDoeAdministrator7/1/11Tournament Administrator5568$1000.00
      JohnDoeAdministrator7/8/11Tournament Administrator5768$1250.00
      JohnDoeAssistant7/13/11Admin. Assistant5789$500.00





      Total:$2750.00

       

       

       

       

       

       

      Now, I have a layout in Filemaker Pro where it's a letter of confirmation so they can check if there are any discrepencies. The letter is like the following:

       

      Dear <<First Name>> <<Last Name>>

       

      You earned over $600 as an independent contractor working with us this past summer...blah blah blah...and we want to make sure that your total amount earned is correct. If you believe there are any discrepencies, please call our office promptly.

       

      (Here is where I would like the table to be that describes the information in the Excel spreadsheet I have shown an example of above).

       

      Would I have to create another Table in the database and use relationships? Inside the confirmation letter layout, would I be using "fields" to get these values or do I have to use "portal" which is entirely foreign to me.

       

      Any simple workaround will suffice, since we only have about 65 employees and most of them only had about 4 or 5 checks issued to them.

       

      Any help would be greatly appreciated!

      Thanks ahead of time for anyone who can help!

        • 1. Re: Need Help with Subsummaries
          Stephen Huston

          Hi James,

           

          You could this in a subsummary report, but you may have to think about the layout and its base-table a little differently. When doing reporting, the correct base table to use is usually the Child table, the one with finest record detail.

           

          In this case, for a subsum report, you should be reporting from the Checks table as the base table of the layout, using subsummaries (by recipient name) for the letter content as both leading and trailing subsummaries with the list of checks (body). Your subsum totals will fall in the letter content areas of the leading/trailing subsum parts.

           

          Then you can do a find on the date range of the checks, sort by recipient name (for the subsum sections) with page breaking after each trailing subsum to break them into letter-like pages.

           

          Stephen Huston

          1 of 1 people found this helpful
          • 2. Re: Need Help with Subsummaries
            james_callery

            So are you saying that my "Main" table should look like my Excel spreadsheet, and then use the other "personnel records" table (child table?) as the subsummary fields to include in the letter layout?

             

            Essentially I'd just want 1 letter layout with no page breaks or anything like that so I can just click print all records and it fills everything in for me.

            • 4. Re: Need Help with Subsummaries
              Stephen Huston

              It's a bit counter-intuitive because if we want a letter to an employee, we naturally think in terms of the employee record as the where to write it. But not when reporting more granular detail, such as individual checks to employees.

               

              The base or Main table of the layout from which you print the letters should have as it BODY the check records. (The base table of the layout is set from all of the available TOs in that file via the Layout Setup dialog.) The only "child" table involved would be if you need to pull info from the personnel record for address, etc. That info would be merged text in the letter content within the subsummary parts, which is the layout part repressenting the recipient/employee for this report.

               

              To generate the letters, perform a Find on the date range of the check records you want to report as letters, sort by recipient/personnel-names, and place  the letter text above/below the grouped checks (the body part) in the subsummaries parts sorted by recipient name with a page break after each trailing subsum to place the letters on separate pages.

               

              Remember that you will usually get the best-formatted report by reporting from a layout based on the table with the finest/most-detailed records. In this case, thats the checks themselves. This assures that you will never need portals in such reports. Portals simply don't report very nicely in most cases.

               

              Final report layout parts:

               

              • Header (letterhead logo, etc, but no personalized or record-based content)
              • Leading subsummary (top of letter above the check list, when sorted on recipient), including any address, greeting, etc. from te related personnel record.
              • Body (the Check records, formatted for a single check but will display/print in list view), will repeat automatically in list view for the number of checks per recipient when records are sorted)
              • Trailing subsummary (letter part after the list of checks, when sorted on recipient), including letter closing, signature area, etc. Can also include the subsummary amount (Sum field of Check Amounts in the Checks table, not a calc or sum from fields the Personnel table).
              • Footer (optional)

               

              This has the additional bonus that you do your Find in the checks records by date-range for the letters, and there's no letter even generated for names which have no checks to report.

               

              Stephen Huston

              1 of 1 people found this helpful
              • 5. Re: Need Help with Subsummaries
                james_callery

                Stephen,

                 

                I believe that is exactly what I need, and I greatly appreciate the detailed help you've given me. I'm not entirely clean on the process because I'm rather new to Filemaker, but I can learn fast. When you say insert page breaks, am I doing this in the Layout view? What I do is input all my data when in the Browse view, then I usually just switch my layout to the pre-written letter that contains all the fields I want and it does everything automatically from there.

                 

                This, I'm sure you know of and is not what you were trying to explain. However, that is what I've been limited to in terms of generating these automatic "letters".

                 

                I guess what I am asking is, what should my "Main" table look like that contains all of the detailed income data (the example spreadsheet from my initial post).

                 

                If you could just create a table for me with the appropriate headers (aka-what it would look like in browse view in Filemaker) and then advise me on how to total up each check for each individual (remember there's about 65 with an average of 5 checks each).

                 

                Again, I am extremely grateful at how helpful and detailed your replies are. This is really the best forum I've ever joined when having an issue that needs prompt attention!!

                 

                Regards,

                James

                • 6. Re: Need Help with Subsummaries
                  Stephen Huston

                  Hi James,

                   

                  Once more with a bit more detail on the layout:

                   

                  This layout uses the Checks table as it base table. It has a relationship to the employee/recipients table for pulling info into the sub summarie layout parts.

                   

                  There is NO letter table involved. All "letter" content is either text or related data from the related recipient/employee table, all of which is placed into the subsummary parts of the layout. This won't even be visible in browse mode until the records are sorted on the correct break field as specified in the part setup dialog when defining the subsum layout parts.

                   

                  Final report layout parts:

                   

                  • Header (letterhead logo, etc, but no personalized or record-based content)
                  • Leading subsummary (top of letter above the check list, when sorted on employee/recipient), including any address, greeting, etc. from the related employee/recipient record.
                  • Body (the Check records, formatted for a single check per record/line so it will display/print in list view), will repeat automatically in list view for the number of checks per recipient when records are sorted)
                  • Trailing subsummary (letter part after the list of checks, when sorted on recipient), including letter closing, signature area, etc. Can also include the subsummary amount (Sum field of Check Amounts in the Checks table, not a calc or sum from fields the Personnel table).

                   

                  Both of the Subsummaries are set to display data when sorted by the recipient/employee name. The creation of subsummariy parts is done from the layout menu while in layout mode

                   

                  partSetupmenu.jpg

                   

                  The trailing subsummary is set to have a page break after each occurence so letters (actually grouped lists of checks with letter content int he subsummary parts) will start on new pages for each recipient.

                   

                  The letters you will end up with, one per page per employee, with all checks for that employee will show:

                   

                  • Header content (letterhead?)
                  • Leading subsum: Employee address, greeting, paragraphs saying things like please look over the checks we show were sent to you, etc.
                  • Check 1 row data
                  • Check 2 (etc if multiple checks for same recpient)
                  • Trailing subsum: letter text following list of checks: If any of the checks above is incorrect, please do xxx, etc. Closing/Thank you, signature.

                   

                  You do the find in this layout for the checks by date range you want to report/verify/include in the letters, sort the resulting found set of checks on the recipient name (or whatever the break field was you set for the subsummary parts in the Part Setup), and print all found records.

                   

                  If done correctly you will get individual letters for each recipient, each showing all the checks to that person in the middle of the "letter" area.

                   

                  Stephen Huston