3 Replies Latest reply on May 25, 2011 10:17 AM by philmodjunk

    Help with Report Views

    fredmckinnon

      Title

      Help with Report Views & Data (Compact Groups by Week or Month?)

      Post

      Hey Gang,

      We've been using DabbleDB for a couple of years extensively, and since they are going out of business, we've migrated everything over to FileMaker.  I am missing some of the easy features that were available in Dabble (though I'm sure FileMaker can make them happen).

      One thing that Dabble offered was the ability to have a "compact" view by Group ...

      So, for example, if I wanted to show the Net Profit & Loss by week ... it would show one, single line, with the total - they called it "Compact View".

      So, let's say there were 500 sales entries for that week ... rather than showing all 500 entries and a trailing summary ... it would just show a single row that looked someting like this:
      Date                      Net P&L
      May 1-7, 2011        $25,000
      May 8-14, 2011      $30,000   etc.

      Dabble had these pre-defined searches as "views" that were saved ... I had views like:
      Sales by Week (Compact View)
      Sales by Month (Compact View)

      etc.

      So .. I'm having trouble figuring out how to do all of this ... any help?  If you can point me in the right direction with the keywords, features, etc., I can read the manual (again) and try to figure it out - but need a kick start.

        • 1. Re: Help with Report Views & Data (Compact Groups by Week or Month?)
          philmodjunk

          In FileMaker, you can achieve this with subsummary parts in a list view layout where you've removed the body part. You'll need a field with a calculation such as: DateField - DayofWeek(dateField) + 1 so that all records from the same week have the same value in this field. Then you'd add a sub summary part to your layout with this calculation field specified as the "when sorted by field". Add your fields to this layout part, (A summary field that computes the total of your Net P&L field can produce the weekly sub total), click th body part label and press delete to remove it. Las step is to find your records and sort them by this same field that you specified in the "sorted by" option for the sub summary part.

          • 2. Re: Help with Report Views & Data (Compact Groups by Week or Month?)
            fredmckinnon

            Phil,

            OK - I went to Layouts.  I created a new List Layout.

            I created a field called "Calculated Month" whose formula is:

            Timestamp  - MonthName ( Timestamp ) +1 (where "Timestamp" is the auto-entered timestamp of a new record created)
            (see attached screenshot)

            I removed the Body part (all the fields were originally in the Body part when I created the layout, so as you can see, I added the Summary part and just dragged the fields out of Body down ... and removed Body).

            Now, when I go to Browse - I have no fields to use for search/find, or browing - I assume, because there is no body?  What am I missing?

            • 3. Re: Help with Report Views & Data (Compact Groups by Week or Month?)
              philmodjunk

              First, your calculation mixes TimeStamp numbers with text (monthname) so it won't produce the expected value here. My example is set to work with a date field, not a timestamp.

              If you want a common date for all records from the same month and have this date entered as part of a timestamp. use this:

              Let ( D = Getasdate ( TimeStamp ) ; D - Day ( D ) + 1 )

              Use this calculation field as your "when sorted by" field in the sub summary part.

              In order to find records for this report, you'll need to either use a script, use a different layout where the fields are available in the body, or both. After finding the records you want, you can sort them and switch to this layout.

              Note that if you don't sort the records in an order that uses the "sorted by" field, you will not see much of anything in this report.