2 Replies Latest reply on Aug 14, 2012 5:00 PM by Dekade

    REVISED - Multiple "selecting / counting"



      REVISED - Multiple "selecting / counting"


      I am wanting to set up a list of 50 to 75 topics that will show globally on each record. I want to be able to place a "checkmark" per say (or some kind of a selection notation) beside one or more topics that I want associated with each individual record. I would also like there to be a "total count" of each individual topic. This count would show either to the left or the right of the 'topic' and might be shown inside of parentheses, brackets etc. The total count would also show globally.

      I don't think I can do something like that with a drop down list - and I'm not sure about the repeating field procedure being applicable either.

      Does anyone have an idea of how to set up this scenario?




      1)  Field:Category_Series1     [this field is global, and repeating, and each repetition has a different one or two word description]

      2)  Field:Series1_Select    [this field is global, and repeating, and is currently a radio button 'yes' or 'no']; if any repetition has "Yes" selected then that repetition requires to be counted in the respective Series1_Count repetition.

      3)  Field:Series1_Count    [this field is currently global and a repeating field]

      4)  All three fields are vertical repetitions and are side by side left to right

      5)  Desired calculation:

           a.)    Category_Series1,example: Record #12,    has Repetition 6 = "Brown House"; and Repetition 23 = "2 Story House"

           b.)     There are maybe 345 records in the database

           c.)     Series1_count,  example: any record (because the field is global) will show that Series1_Count, Repetition 6 in the database has a total of 57 records that have a 'Brown House' and the record will also show that Series1_Count, Repetition 23 in the database has a total of 11 records that have a '2 Story House'.

      Thus, what is the calculation that I need to input into "Series1_Count"?

      Any help on this will be greatly appreciated.

      Thanks Again,


        • 1. Re: REVISED - Multiple "selecting / counting"

          There are several options that you can play with here. The obvious option (and not my first recommendation), is to add a text field to your record formatted with check boxes for selecting your category values. This makes it very easy to select multiple category values for each record, but tabulating how many times each category value was selected over your entire table (I'm assuming that's the count you want here), will not be so simple.

          My suggestion creates what looks and functions like such a group of check boxes, but actually creates a related record for each selected category value. This then makes it relatively easy to count the number of records in the related table with each value to compute and display the counts that you want. It has the added benefit that you can use a scroll bar with your apparent checkbox set, if such is needed, to make better use of the space available on your layout.

          First, you need three tables: MainTable--the table you have now. Categories--one record for each category value, and SelectedCategories--a "join" table linking the other two where you'll create a new record each time you select a new value and delete a record if you clear a selection. These are the records you can count to get the counts for each category value.

          Start with these relationships:

          MainTable----<SelectedCategories>-----Categories    (---< represents a one to many relationship)

          MainTable::__pkMainTableID = SelectedCategories::_fkMainTableID
          Categories::__pkCategoryID = SelectedCategories::_fkMainTableID

          Make a new occurrence of Categories, name it AllCategories. Make a new occurrence of SelectedCategories and name it SelectCatByCategory. Add these relationships:

          MainTable::anyfield X AllCategories::anyField

          AllCategories::__pkCategoryID = SelectCatByCategory::_fkCategoryID

          Put a Portal to AllCategories on your MainTable Layout. This portal will look and function like a group of check boxes and also display the category totals for each.

          Put the AllCategories::CategoryName field in this portal row. To the left of it, use the text tool to add a single character to the portal row. Typically, this character is the letter X or a symbol character that looks like a "tick" mark, but it can be any visible character that works for your interface design. You can use the rectangle tool to add a box behind this character to further simulate a check box look. Select this character and use conditional formatting to change the font size to 500 when this expression is true:

          IsEmpty ( FilterValues ( List ( SelectedCategories::_fkCategoryID ) ; AllCategories::__pkCategoryID ) )

          Use button setup to convert this single layout text character into a button that performs this script:

          Freeze Window
          IF [ Not IsEmpty ( FilterValues ( List ( SelectedCategories::_fkCategoryID ) ; AllCategories::__pkCategoryID ) ) // Category is already selected ]
              Go To Related Record [Show only related records; From table: SelectedCategories; Using layout: "SelectedCategories" (SelectedCategories)]
              Delete Record [no dialog]
              #Category is not selected, create a new record in SelectedCategories to "select" it
              Set Variable [$MainID ; value: MainTable::__pkMainTableID ]
              Set Variable [$CatID ; value: AllCategories::__pkCategoryID ]
              Go To Layout  ["SelectedCategories" (SelectedCategories) ]
              New Record/Request
              Set Field [SelectedCategories::_fkCategoryID ; $CatID]
              Set Field [SelectedCategories::_fkMainTableID ; $MainID]
          End If
          Go to Layout [<original layout.]
          Refresh Window []

          last step is to show the totals for each category.

          Define a calculation field in Categories as:

          Count ( SelectCatByCategory::_fkCategoryID )

          In the context drop down at the top of the Specify calculation dialog, be sure to select AllCategories.

          Now you can add this to your portal row and you should now have a portal where you can both select values and see category counts for each category.

          If "Table Occurrence" is an unfamiliar term, see this thread: Tutorial: What are Table Occurrences?

          • 2. Re: REVISED - Multiple "selecting / counting"


            Again - thanks - for your expertise. Up front I feel as though I see right where you are going. I will start tomorrow morning studying your reply and get back with you when I hit a speed bump. Maybe I won't hit one to begin with - the perfect world.