1 Reply Latest reply on Feb 7, 2011 2:40 PM by philmodjunk

    Creating a layout using checkbox data



      Creating a layout using checkbox data


      I am trying to create a layout where the information in a field that has checkboxes can be separated in to different columns.

      For example, I have three fields with four check boxes labelled 1-4. I want to create a layout where I can columnate each separate check box number into four columns 1-4 and then have the number of records where each checkbox is ticked subtotalled down the bottom.  

      Eg. I have three records with a field for colour (drop box) and a field for shape (check box: round, square, oval)

      How can I design a layout that shows in columns that for the colour red I have four round, 8 square and 2 oval?


        • 1. Re: Creating a layout using checkbox data

          Using your example data, it doesn't appear that designating a record as both square and oval would make sense. If only one value at a time may be selected from the checkbox group, you would be better off to use radio buttons as this interface design makes it harder to select two values and makes it more obvious to the user that only one value may be selected.

          For your report, you can set up a layout where you change the layout body into a sub summary part "when sorted by" your color field so that you get one row per color. You can then define a self join relationship that links your current layout's table occurrence to a new table occurrence of the same table by the color field, then, if you are using FileMaker 11, you can specify a one row portal to this new table occurrence for each check box value and use a portal filter to restrict the records shown to just the records where a specific checkbox value was selected. If you place a summary field that computes the "count of" some other field in your table that is never empty (can be the checkbox field if you want), in these portals, you'll get your columns of results for each specified check box value.

          This type of report is called a cross tab report, BTW.

          The exact format of your portal filter expressions will depend on whether a given record can have more than one "shape" value selected in your checkbox field.

          If only a single value may be selected, PortalTable::CheckBoxField = "square" would be used on the portal that displays the count of all "square" values for a given color. If multiple values are permitted in this field, then you would need to use something like:

          Not IsEmpty ( FilterValues ( PortalTable::CheckBoxField ; "Square" ) )