1 Reply Latest reply on Feb 18, 2015 11:47 AM by philmodjunk

    Display records by specific date columns

    Salem

      Title

      Display records by specific date columns

      Post

      Hi All,

      I am currently trying to set a field to display a value from a record of a specific date. For example when I have a scenario like such :- 

      Project      Date      Fruit     Count

      Project A   01/Feb/2015  Apple  5

      Project A   02/Feb/2015  Banana 10

      Project A   03/Feb/2015   Apple 2

      Project A   03/Feb/2015   Banana 5

      I am trying to be able to build a summary report where I can display as such :-

      Sub-Category = Project A

      <Fruit>  <Day 1> <Day 2> <Day 3>

      Apple          5                         2 

      Banana                    10           5

      Treating this like a stock-take system, I enter in items and the count on a daily basis. Hence here I have 2 main tables one to capture the daily_id ( containing the dates) and another table item_id (containing fruit_type and count). At the end of the month, I want to be able to see the count of my items day by day.

      For a start, I am creating fields to store the counts for each day (Ex: Field;DAY1 = count from 01/Feb/2015). Which means I will be creating 31 new fields. I then proceed to creating a script to help me find the records for each day and store the counts into my (DAY*)fields.

      So far this is what I have.
      ---------------------------------------------------

      Show All Records

      Go to Record/Request/Page [First]

      Loop

            If[site_diary::date = Date(site_diary::prevMONTH; 1; site_diary::prevYEAR)]

                       SetField[site_diary::day1; site_diary_manpower::count]
                  End If
             Go to Record/Request/Page[Next;Exist after last]

      End Loop.

       

      *prevMONTH - is to help me identify the previous month.

      *prevYEAR - is to help me identify the year of the previous month.

      Hope someone can shed some light on this.

      Cheers.

       

        • 1. Re: Display records by specific date columns
          philmodjunk

          What you are trying to set up is often referred to as a "cross tab" report where data from different records is organized into columns as well as rows.

          A frequently used method in FileMaker to produce such a report is to set up a list view layout where each row is a record or a sub summary layout part and one row portals are used to display the data from a related table occurrence in columns. Sometimes a set of different match fields are set up to define a different relationship for each column, other times a different portal filter is specified in order to control which data appears in each column.