3 Replies Latest reply on Dec 23, 2010 12:17 PM by philmodjunk

    Record queries into a table

    RobinParker

      Title

      Record queries into a table

      Post

      Hi all,

      Hope you can help me.  I'm trialling Filmaker at the moment (that's how much experience I have!!)

      I have put a load of records into my database.  Each of the records have (at least) 2 fields, call them field1 and field2

      I need to generate a report/table where you have the possible values of field1 down the side and the possible values of field2 along the top and the bits in the middle populated by the counts of the number of records that have that value for field1 and the other value for field2.

      Hope you can help me with that.  I've done database stuff before but filemaker seems to have it's own way of doing things.

      Once I've done that, the fun starts! These records go back to 2003 and I have to produce a similar report for each year (this is just the totals one)

      Anyway, thanks in anticipation!

      Robin

        • 1. Re: Record queries into a table
          philmodjunk

          This is called a "cross tab report". Since FileMaker doesn't include a Wizard for Crosstab reports the way some database systems do, it takes a bit more work to set up, but can be done.

          Usually, in a cross tab report, the "row" values are variable, but the "column categories" are not as you usually have a fixed amount of space available for columns, but rows can continue on to additional pages as needed. I'm making that assumption here with this suggested solution.

          There are a number of different variations possible here. This approach requires Filemaker 11 as it uses portal filtering to set up the column counts of data in the Field 2 categories.

          1. Define a Summary Field.

            Define sCount as a summary field that computes the Count of Field2.

          2. Define a Self Join Relationship based on year and Field 1

            If you don't have a field that records the year in each record, define one. The Year () function can be used to extract the year from a date field.
            Open Manage | Database | Relationships and drag from YourTable::Field1 to outside the box and then back to Field1 before releasing the mouse button. This creates a new table occurrence of YourTable. Name this new table Occurrence SameValue.
            Drag from YourTable::YearField to SameValue::YearField to add this field to your relationship. If you double click the line linking the two table occurrence boxes, you should see:

            YourTable::Field1 = SameValue::Field1 AND
            YourTable::Year = SameValue::Year

          3. Create a "Horizontal Portal". A horizontal portal is a series of one row portals that simulate what you'd get if a portal could be tipped on its side.

            Create a list view layout based on YourTable in the Show Records From drop down.
            Place YourTable::Field1 on the lefthand side of the body layout part.
            Use the portal tool to place a portal to SameValue next to this field.
            Specify a Portal Filter with this expression: SameValue::Field2 = "Value to be counted for column 1"
            Make this a one row portal
            Put sCount as the sole field inside your one row, filtered portal.
            Copy and paste this one row portal, using portal set up to update the portal filter expression to select for different values in Field2 as needed until you have all the rows you want.

           

          Note that there are ways to use a series of global fields in the portal filter expressions if the column categories are not fixed.

          • 2. Re: Record queries into a table
            RobinParker

            Thank you for the reply....

            This works almost perfectly.  There are just a couple of niggle and questions:

            The first niggle is that on my report, I get the correct data but I get a row for each record in the database.

            Field 1 and Field 2 are drop down lists which do not change very often.  On the report, I would like a row for each possible entry in the list rather than for each record in the database.

            The second niggle is that I would like a '0' in the portal if there are no records counted.

            My question is that I can see you've used the year in the solution but I can't see how to filter on that year.  So, if I wanted to pull out a 2004 or 2005 report, what would I need to change to see the different reports?

            Many Thanks and Merry Christmas!,

            Robin

            • 3. Re: Record queries into a table
              philmodjunk

              Hmm, I'd create a table where you have one record for each value in field 1. This table can even serve as the source of values for your value list to make sure that they stay synchronized. The other option is to change the body part of your layout into a sub summary part "when sorted by Field 1". You could then limit your data to a given year by performing a find for just records for that year and sort it by field 1, but then you've still got blank squares when the count is zero.

              I'm inclined to use a calculated field approach with multiple relationships from this value table instead of filtered portals. It's a lot more work to set up, but will given you your zero counts as requested. (If you are reading this and can think of a less laborious way to do this please chime in!)

              Let's call this table, Field1Values so we have a name for it. I'm only going to set this up for two columns, but you just have to repeat the process for additional columns.

              Define a pair of Global fields, gCol1, gCol2 in Field1Values. Define a number field, gYear in this table.

              Create these relationships:

              Field1Values::Field1 = YourDataTable 1::Field1 AND
              Field1Values::gCol1 = YourDataTable 1::Field2 AND
              Field1Values::gYear = YourDataTable 1::Year

              Field1Values::Field1 = YourDataTable 2::Field1 AND
              Field1Values::gCol2 = YourDataTable 2::Field2 AND
              Field1Values::gYear = YourDataTable 2::Year

              YourDataTable 1 & YourDataTable 2 should be different table occurrences of the same data source table.

              Define these Calculation fields:

              cCol1 : Count ( YourDataTable 1::field1 )
              cCol2 : Count ( YourDataTable 2::field1 )

              For both of these calculation fields, clear the "do not evaluate if all referenced fields are empty" check box. (If you still get empty fields, try adding "+ 0" to these expressions, but I don't think that's necessary here.)

              Now create a list or table view layout and place Field1Values::Field1 in the first column, cCol1 in the 2nd and cCol2 in the third. Put gCol1 and gCol2 in the header as column labels for your columns. Add gYear to the layout so that you can select a year. You can format gCol1 and gCol2 with the same value list you use for Field2 in your original table or you can create a script that uses the ValueListItems function to load each of these global fields with values from your value list.

              Once you load the global fields with values that match to records in your table, you should see the cross tab report that you want.