1 2 3 4 Previous Next 53 Replies Latest reply on Feb 3, 2012 2:36 PM by imedic

    Help with report



      Help with report


      I have a database with classes and attendance for those classes. Each class has a topic (pulled from a value list) and a category (pulled from a value list). The way I am trying to do the report is to list the Topics and Categories down the middle and have the 1 st year hours listed on the left and the 2nd year hours listed on the right. The only information I need for each person is the total hours and date completed. I have manually typed the topics and categories into the report, so I only need to figure out the calculations for the total hours for each and the date. For example

      Date (Year 1)          Total Hours                                                Date (Year 2)         Total Hours

               X                        X                     Topic A Category 1                X                        X

               X                        X                     Topic A Category 2                X                        X

      There are 5 topics and 2 categories.

      I have tried everything and nothing is working right. Thanks in advance.

        • 1. Re: Help with report

          Hours is in attendance, are the topic and category values fields in attendance or classes?

          Does your example show the data for a single student or all students? (Don't see any student info fields here that would identify the student.)

          Summary fields and Sub Summary parts should produce what you need, provided you sort the records to suit the needs of the sub summary parts, but the details have to be figured out here before I can go into detail.

          • 2. Re: Help with report

            Topic and Category are under classes. On the Class layout I have information about the class. Date, number of hours, Topic, and Categories. There is a portal to the join table called registration.

            This layout is designed to show data for a single user. I have attached it below. This is page two. Page 1 has all the personal information on it. 

            My database has three tables. Classes, Regsitration (Join), and Personnel. Should I create calculation fields for Topic 1  Mandatory , Topic 1  Flexible, etc. or would the subsummary work?

            • 3. Re: Help with report

              My database has three tables. Classes, Regsitration (Join), and Personnel.

              Seems like you need a 4th table, attendance--while similar to registration, which has one record for each date a given student attends a class.

              But (pretending we are just using paper and pencil instead of a computer for the moment) how do you record, for a single class, how many hours were attended in each category for the same class? I'd think that would need to be recorded in the attendance table.

              • 4. Re: Help with report

                Each class  can only have 1 topic and 1 category. If they do multiple topic on the same day, it is another class on the same day. Makes it easier for me to separate the topics and categories. I have attached a screen shot of a portion of my class layout.

                • 5. Re: Help with report

                  So there can be multiple classes with the same topic? And you need to record the total hours from each topic, combining attendance info for several classes--as long as all are of the same topic?

                  Please describe in more detail how this works. I'm concerned that a lack of understanding on my part may result in a less than optimum suggestion.

                  I'm particularly interested in how you define a "class" and how individual students "register" for them.

                  My original understanding was that you were teaching classes such as "biology 101", "English Literature" or such where students attend multiple sessions of the same clase. It now appears that these are classes that meet a single time and thus students attend that "class" 1 time or miss it entirely.

                  • 6. Re: Help with report
                    "So there can be multiple classes with the same topic? And you need to record the total hours from each topic, combining attendance info for several classes--as long as all are of the same topic?" Yes that is correct. This is for a recertification and the classes take place once a month. I am looking for a calculation I guess to add up the hours for each topic in a given year. Topic 1 may be repeated several times thought the year so I would need a total of those hours for that year and for that person.
                    • 7. Re: Help with report

                      Are you recording attendance in the registration table? (Create the record to register them, enter data to mark them "here" when they attend it...)

                      If so, we can set up a summary Report based on the registration table that groups data by Topic and within Topic by category. The fact that these fields are part of the Classes table will not be a problem here.

                      Are all classes the same time length or do they differ in length of time? (Which affects how we compute the total time in each category.)

                      • 8. Re: Help with report

                        Actually I don't monitor attendance per se.  If they attend I add them to the roster through registration table. If they aren't there then they aren't listed. 

                        The classes differ in length. The thing I am having the most trouble with is that the form has to look like the one above for submission purposes. I was able to create a sub summary report with the information but it was not in the format that was acceptable to the, for submission. 

                        Am I correct in thinking that I need to create a calculation field for each topic to add the hours for a specific date range?

                        • 9. Re: Help with report

                          I started to tell you how to set up a summary report for this but then double checked your original screen shot depicting the desired format. I assume you have no choice on what format to use? The two column structure totaling hours for two different time periods (Year 1 and year 2?) will complicate the process a lot.

                          If you could orgainize the report differently, it'd save you a lot of trouble.

                          Two more questions:

                          Is the text in the header always the same or does it need to differe for different versions of this report?

                          What do 1C and 1D represent? Last Year and this year? A pair of years you need to specify for each report? Or ???

                          To get that two column format for 1C and 1D, I think you'll need to create an additional table with 1 record for each possible combination of Topic and Category. Then Relationships with Filtered Portals can compute and display the needed sub totals for each category within each topic for a specified individual. You'd specify the Person's ID and Year info in global fields and then (perhaps) perform a find for the desired topic/category records. Sort those and you'll have your report. (The portals will give you the needed two column format of subtotaled info.)


                          • 10. Re: Help with report
                             "Is the text in the header always the same or does it need to differe for different versions of this report?" Text header is always the same. What do 1C and 1D represent? Last Year and this year? A pair of years you need to specify for each report? Or ??? It represents a pair of years that needs to be specified. I know this format is a pain. I have been fighting it trying to get it to work.
                            • 11. Re: Help with report

                              OK. That's what I expected, but if we could change format live would have been easier. I'm assuming FileMaker 11 here or we'll need to use a different approach...

                              Create a table, Categories with these fields:

                              gYear1 (number, global storage specified)
                              cYear2 (calculation: gYear1 + 1)
                              gPersonnelID (Same data type as ID field in Personnel table, global storage)

                              Define a summary field in Registration that computes the total class hours if you have not already done so. This takes two fields if I understand your database design correctly.

                              cHours: has Classes::Hours as its single term (need for the next field in order to compute total hours for each person.)

                              sTotalHours: Summary field, Computes the total of cHours.

                              Link in the Categories table like this:


                              Categories::Category = Classes::Category AND
                              Categories::Topic = Classes::Topic

                              Create one record in Categories for every combination of Category and topic that exists in your class list. If new categories or topics are added to classes, add matching records in Categories.

                              Create a list view layout based on Categories. Add a sub summary part "when sorted by" Categories::Topic and put this same field in the sub summary part to serve as your sub heading for each topic.

                              Put a one row portal to Registration on the left hand side of the body layout part.

                              Give it this portal expression:

                              Categories::gYear1 = Registration::cYear AND
                              Categories::gPersonnelID = Registration::PersonnelID

                              Put Registration::sTotalHours in the single row of this portal. (You can make portal boundaries transparent or 0 width to keep them invisible.)

                              Put Categories::Category in the center of the body, then copy and paste the left hand portal and summary field to make a copy of it on the right hand side. Double click portal setup and change the filter expression to:

                              Categories::cYear2 = Registration::cYear AND
                              Categories::gPersonnelID = Registration::PersonnelID

                              To produce the needed report for a specific person, you'd enter/select a year in gYear1 and a person in gPersonnelID.

                              Note, cYear can be either a calculation field that uses the Year function to extract the year from a date field defined in the registration field or, if no such date field exists, it can use the same function in an auto-enter calculation to extract the year from a date field in classes.

                              A copy of this portal with this expression would show the two year total of all topics/categories for a specified individual:

                              Categories::gYear1 = Registration::cYear OR Categories::cYear2 = Registration::cYear AND

                              • 12. Re: Help with report


                                Is it possible for me to send you a pdf of my entire report? I have the report pulling up, but it is becoming increasingly diffcult to have all the different fields. I thought of running a script and setting each section to a different report. That way when the script runs it opens each report. I don't think I am going to be able to run this with a single report. 

                                It's hard for me to explain unless I can send you the report somehow. You can PM me if you like or vice versa. Just let me know.

                                • 13. Re: Help with report

                                  Don't see any reason why you'd need separate layouts for different parts of your report here. "all the different fields" concerns me here. Either there is a structure to the tables your database different than I assumed, or you have misunderstood how to set up this report layout.

                                  You could also upload a clone (empty copy) of your file to a file sharing site and post the download link here.

                                  • 14. Re: Help with report

                                    Yeah, I think that i have had a hard time explaining as well. I have included the pdf's of the reports as well. Unfortunately the reports have to look like these. I will PM you the password to the file. Here is the link. http://dl.dropbox.com/u/34544546/IST.zip.


                                    Thank You

                                    [Edited by PhilModJunk to make link "live"]

                                    1 2 3 4 Previous Next