3 Replies Latest reply on Jan 9, 2015 7:10 AM by philmodjunk

    Need a calc for counting a unique value

    WF7A

      Title

      Need a calc for counting a unique value

      Post

      Greets, all!

      I'm drawing a blank how to do this elegantly using either an ExecuteSQL calc or "straight" FileMaker calc so I need some help.

      There's a field in a table named CompanyName__lxt where whenever a user either copies a record or creates a new one _and_ enters a company name--either manually or already entered from the duped record--it's checked against all the records in a table for the same company name and gives a count of those uses. For example, if a user enters Bozotronics into the CompanyName__lxt field and that company name already exists in to two other records, the calc presents a [3] since s/he just entered Bozotronics for the third time.

      I already have the tail end of the calc figured out so if there is a count of more than one then conditional formatting will cause the field's text to turn yellow; it's the front end I'm having trouble with.

      As always, your help is appreciated.

      Cheers!

        • 1. Re: Need a calc for counting a unique value
          philmodjunk

          What "front end" is that? Do you want a count of the number of different company names present in the table or the numbers of times that a specific company name is present in the table?

          To count the number of different company names are present in a given table: A new way to count unique values in FileMaker 12

          To count the number of times a particular company name is present in the table:

          Select Count ( \"CompanyName__lxt\" ) From \"Your Table Here \" WHERE \"CompanyName__lxt\" = ? ; "" ; "" ; YourTable::CompanyName__lxt )

          • 2. Re: Need a calc for counting a unique value
            WF7A

            Perfect! Thank you!

            I needed the latter calc to count the number of times a specific company name is present in the table. I touched it up below so other users can cut-and-paste it; I saved it as Number, unstored:

            ExecuteSQL (

            "SELECT

            COUNT ( CompanyName__lxt )

            FROM JOB

            WHERE CompanyName__lxt = ?"

            ; "" ; "" ; JOB::CompanyName__lxt

            )

            • 3. Re: Need a calc for counting a unique value
              philmodjunk

              The \" \" characters were there for a reason. Your names didn't need it but it's often necessary to quote field and table names inside an ExcuteSQL query and it's a good practice to simply quote everything in order to avoid the infamous ? result you'll get if a name is a reserved word or has characters (such as spaces and leading underscores) that the SQL query doesn't like.