5 Replies Latest reply on Jul 13, 2012 9:25 AM by philmodjunk

    Total Count of 1 Field Constrained by another Field

    bookman

      Title

      Total Count of 1 Field Constrained by another Field

      Post

      What does a calculation look like that counts the number of records with aDate Created 2012 but omits or is constrained by a field called Selected as a Previewer?

       

      Should the field type be a Calculation or a Summary?

      The result should be a Number.

       

      I keep getting error messages re: field doesn't exist, wrong function, or needs an operator.

       

      I am a novice, this is the first time I've tried to create a calculation, but I'm good at following directions.

      I'm guessing my errors are related to the  "parentheses" or the position of "not" or the "2012" in the Date Created, or whatever.

       

      The attachment shows my last attempt.

       

      Please help.

       

      Mel

      Post1.png

        • 1. Re: Total Count of 1 Field Constrained by another Field
          philmodjunk

          You can't JUST use a count function or JUST a summary field. For a count function to selectively count records, you'd need a relationship that matches only to the records you want to count. For a Summary field to selectively count records, you need to design a layout and produce a found set with the appropriate sort order in order to get the count of records.

          And if you have FileMaker 12, there's a third option: Execute SQL that can selectively count records for you.

          What version of FileMaker do you have and what do you want to do with this count?

          • 2. Re: Total Count of 1 Field Constrained by another Field
            bookman

            Hi Phil,

            I know how to create a find that does what I want it to do. I just don't what to have to perform the same find each time I want to know how many records fit the found set criteria. I want it to auto-fill a number result in a field designed for that sole purpose. For example, not quite the same, I have a Count of People Field that shows the total of records with an entry in First Name for any Found set. My desire is to create a field that shows the count of records that were created in 2012 (under Date Created) but excludes from the count records that are in another specified field.

            Are you saying that this calculation can't be created in the Specify Calculation window? 



            • 3. Re: Total Count of 1 Field Constrained by another Field
              philmodjunk

              Not without defining and using a relationship that matches only to the records you want to count or (with fileMaker 12) constructing a SQL query that counts the records. 

              I think you want to do something like this:

              Count all records created in a specified year that have a specific value in another field (or which do NOT have that specified value). To  use your example, let's assume that "selected as Previewer" stores a 1 if it is selected and you want to count all records created in 2012 that have a 1 in this field. Presumably, you have a date field that stores the creation date.

              Method 1: (Requires fileMaker 11 or newer).

              Define a relationship such as: OccurrenceOfTableForLayoutWhereYouWantCount::gYear X YourDataTable::AnyField.

              gYear is a field with global storage specified, but you can also use a non-global field if you want. You use the cartesian join operator (x) instead of = to match to any record in YourDataTable. You can literally choose any field in this table for the match field and it works.

              Define a summary field in YourDataTable as the "count of" any field in your table that is never empty. Often, the primary key field is used for this.

              Put a one row filtered portal on the layout where you want to display the count. Use this filter expression:

              Year ( YourDataTable::creationDate ) = OccurrenceOfTableForLayoutWhereYouWantCount::gYear AND YourDataTable::Selected as Previewer = 1

              In the one row portal, put the summary field from YourDataTable. OccurrenceOfTableForLayoutWhereYouWantCount and YourDataTable can be two occurrences of the same table.

              This is a "display only" method. It doesn't give you a count you can use in a calculation.

              Method 2: (works in all relational versions of FileMaker)

              Define this relationship:

              OccurrenceOfTableForLayoutWhereYouWantCount::gYear = YourDataTable::cYear AND
              OccurrenceOfTableForLayoutWhereYouWantCount::constSelectedAsPreviewer = YourDataTable::Selected as Previewer

              cYear is a calculation field: Year ( CreationDateField ) , constSelectedAsPreviewer is a calculation field defined to always return the value 1.

              Now the count calculation can be used like this: Count ( YourDataTable::cYear ) //any non blank field in YourDataTable works to count your records.

              • 4. Re: Total Count of 1 Field Constrained by another Field
                bookman

                Hi Phil,

                Thank you for getting back to me.

                Unfortunately, I'm still in FileMaker 10.

                Nevertheless, I think I'm out of my element. I've learned that the smallest mistake can prevent a solution from working, including simple formatting errors.

                I've decided that the benefits of creating a more elegant solution aren't worth the learning curve. Since my non-elegant solution works and the time it takes to do what I know how to do is not great, I'm going back to my manual methods.

                Thank you for your time.

                Mel

                 

                • 5. Re: Total Count of 1 Field Constrained by another Field
                  philmodjunk

                  Note that method 2 works in FileMaker 10.