1 2 Previous Next 17 Replies Latest reply on Sep 2, 2016 7:15 PM by LabsRock

    Counting unique related records

    realgecko

      Hello everybody,

       

      I have a database defined by the relationship visible in the first screenshot.

      In the Species table (see screenshot 2) I would like to define a field to count the number of studies in which each species occurs. As in the All_records table a specific combination of Species and Study code can occur more than once I need to find a way to return the count of unique records in the field All_records::Study code related to each value in the field Species::Species.

      Counting non-unique related values posed no problem with the function Count ( All_records::Study code ).

      For counting the unique records I found the following support page on counting unique records in the same table Counting the number of unique values in a field | FileMaker .

      I tried the sql function

      ExecuteSQL ("SELECT COUNT (DISTINCT All_records::Study code) FROM All_records " ; "" ; "")

      and played around with it for a while but it only returns "?".

      Now I am not sure whether this is because this specific sql query does not work for related records or because I can't find the error I made in code.

      Any help on this or alternative solutions for achieving the count of unique related records would be greatly appreciated.

      Screen Shot 2016-09-02 at 12.55.42 PM.png

      Screen Shot 2016-09-02 at 1.09.20 PM.png

        • 1. Re: Counting unique related records
          jbrown

          Morning.

          In your ExecuteSQL, your Count (Distinct …  , get rid of the table name. YOu don't need that in SQL statements. And your field name needs to be dealt with specially because it contains a space. It should look like this:

           

           

          ExecuteSQL ("SELECT COUNT (DISTINCT \"Study code\" ) FROM All_records " ; "" ; "")

           

          As I understand your structure, that should count the unique study codes in the join table.

           

          The space causes difficulty in SQL statements, and I often forget to do this, so I always make my field names with no space.

           

          EDIT: The post below mine is more accurate. Since you're trying to find the studies based for a species, the WHERE clause is needed.

          I should have read more carefully.

          • 2. Re: Counting unique related records
            okramis

            As ExecuteSQL() works context independent, you have to specify it.

            If you put the calculation in a field of Species it would be something like this:

             

            YourCountField = ExecuteSQL ( "

            SELECT COUNT(DISTINCT \"Study code\"

            FROM All_records

            WHERE \"Sc. Name\"=?

            " ; "" ; "" ; Species::Species )

             

            as the fields "Study code" and "Sc. Name" contain blanks/dots, they have to be escaped by \"

             

            Otmar

            1 of 1 people found this helpful
            • 3. Re: Counting unique related records
              Mike_Mitchell

              Another, simpler, option is to create a value list based on the related records, then use a combination of ValueCount and ValueListItems to return the value. For example:

               

                   ValueCount ( ValueListItems ( "" ; {your value list name here} ))

               

              It does create the extra overhead of having a value list, but it's simpler to create, and will typically be faster than ExecuteSQL.

              1 of 1 people found this helpful
              • 4. Re: Counting unique related records
                jbrown

                I love this method. Only within the past year did I find that ValueListItems function. It isn't part of my normal development as I don't remember what it is capable of, but it is a good function to use.

                • 5. Re: Counting unique related records
                  beverly

                  ditto! so many ways to do something in FileMaker. 'native' can be easy or complex, efficient or not. so many variables that determine what works best and when.

                   

                  beverly

                  • 6. Re: Counting unique related records
                    realgecko

                    Hello Mike_Mitchell,

                     

                    Thanks for your suggestion. I already have a value list, which I used to list the unique values of the All_records::study code field. These are displayed in the field species::recorded_in via the this function:

                    Substitute ( ValueListItems ( Get ( FileName ) ; "Studies" ) ; "¶" ; "; " );

                     

                    Thus you suggestion seems the easiest, a value count however returns "1" for each species if I just run it on the Species::Studies field

                    ValueCount ( Studies )

                    or returns 0 if I specify it as you suggested

                    ValueCount ( ValueListItems ( "" ; Studies ))

                    or if I replace the "" with get (FileName)

                    ValueCount ( ValueListItems ( Get ( FileName ) ; Studies ))

                     

                    Any suggestions on what I'm doing wrong?

                    • 7. Re: Counting unique related records
                      Mike_Mitchell

                      What does it look like if you just use the ValueListItems function by itself?

                      • 8. Re: Counting unique related records
                        realgecko

                        It returns a list of the unique values; this is  the function I used to return the values in the species::recorded_in field; which can be seen in the second screenshot above.

                        Substitute ( ValueListItems ( Get ( FileName ) ; "Studies" ) ; "¶" ; "; " )

                        So basicall I need a count for the number of values displayed in the field species::recorded_in field

                        • 9. Re: Counting unique related records
                          dtcgnet

                          Your "Recorded_in" field already has the unique values. You just need a count of them.

                           

                          Could be:

                          PatternCount ( Species::Studies ; ";" ) + 1

                           

                          Also, your last example:

                          ValueCount ( ValueListItems ( Get ( FileName ) ; Studies ))

                           

                          will work if you put quotation marks around "Studies", as you did in the formula above where you are substituting out the return characters for the semicolons.

                          1 of 1 people found this helpful
                          • 10. Re: Counting unique related records
                            realgecko

                            Thank you Otmar,

                             

                            I changed the field names to avoid the issue with blanks and dots. Thus Sc. Name is now Scientific_name and Study code is now Study_code (see screenshot1). Also in the species table I changed the field name species to id.

                            Thus I modified the function to be:

                            ExecuteSQL ( "SELECT COUNT(DISTINCT"Study_code" FROM All_records WHERE "Scientific_name"=?" ; "" ; "" ; Species::id )

                            However, the code is not accepted as it FM refers to "Study_code": This field cannot be found.

                             

                            Any idea what is wrong?

                            Screen Shot 2016-09-02 at 3.34.56 PM.png

                            • 11. Re: Counting unique related records
                              realgecko

                              Thanks a lot! It is these little things that I keep missing... Sorry new to filemaker and learning slowly.

                              Thank all of you for your support and patience!

                              • 12. Re: Counting unique related records
                                realgecko

                                Thank all of you for suuport and patience! I am new to FM and a rather slow learner, I really appreciate your help a lot!

                                I now have a count of the studies which report each medicinal plant species. This serves as a criterion for determining the respective species importance as a herbal remedy. However as the return of the valuecount is numbers without leading zeros, sorting my table according to this value is of little help. Any easy way to add leading zeros to the numbers??

                                • 13. Re: Counting unique related records
                                  dtcgnet

                                  Change the field so that it returns Numbers instead of Text. Then you don't need the leading zeros and things will sort properly.

                                  1 of 1 people found this helpful
                                  • 14. Re: Counting unique related records
                                    okramis

                                    Almost there, should be:

                                    ExecuteSQL ( "

                                    SELECT COUNT(DISTINCT Study_code

                                    FROM All_records

                                    WHERE Scientific_name=?

                                    " ; "" ; "" ; Species::id )

                                     

                                    as you removed the blanks and the dot, if you remove the backslash, you need to remove also the quotation, leave the field name alone...

                                    1 of 1 people found this helpful
                                    1 2 Previous Next