2 Replies Latest reply on Feb 23, 2015 7:30 AM by ixpose

    Report Help

    ixpose

      Title

      Report Help

      Post

      Hi Forum,

      I was an ace on FMP 1 thru 5 - way back, so-so on FMP7 and now I find myself a beginner on FMP13 Adv. This is my first shot at the forum. 

      I am trying to build a database, which is a checklist with 84 parameters. The 84 parameters are divided into 7 categories. The target is to replace a MS Excel spreadsheet operation where values are manually copied/pasted. I want to be able to expand the number of parameters as well as the number of categories.

      The check is performed on weekdays, setting values 1 or 0 for each of the 84 parameters.

      On some days, the check is not performed.

      The values are summed, per the 7 categories/day.

      The daily sums are then summed per week and the average is displayed. It looks like this.

      My approach: I have opted for a simple data model with just a few fields, with one record for each of the 84 parameters:

      Param_No
      Param_Name
      Cat_No
      Cat_Name

      I have a prep table. For each day that the check is performed the 84 records are imported to a second table, which also includes a date field, set to Today.

      In the second table I have a Value field set to either 1 or 0 by pushing buttons.

      In the second table I also have 7 unstored calculation fields that presents the values for the 7 categories. (Case Cat_No = 1;Value), (Case Cat_No = 2; Value), etc. 

      A third table has one record per day, with a Date field and 7 calc fields; Sum_Cat_1, Sum_Cat_2, etc. The date field lets me calculate Year, Week_Of_Year and Day_Of_Week.

      How can I present the data in a layout like in the spreadsheet?

      TIA :)

      //Per

        • 1. Re: Report Help
          philmodjunk

          No need for importing the data into another table. What you describe is usually referred to as a "cross tab" report and you can search this forum and others using that phrase to find numerous discussions and examples of such a report.

          One approach for what you show is to set up a list view layout where each record in the layout's table represents a different week of a given year. The columns of data would be produced with either a series of one row filtered portals with a summary field from the portal's table (your table 1) placed inside the portal row as the sole field. It's also possible to use calculation fields with ExecuteSQL() calculations in place of the portals to produce the same result.

          • 2. Re: Report Help
            ixpose

            Thanks a lot for pointing me in the right direction!