14 Replies Latest reply on Feb 1, 2017 10:55 AM by philmodjunk

    Count unique values in records after Perform Find

    cnschulz

      Hi,

       

      I have found this article : Counting the number of unique values in a field | FileMaker that allows me to count the unique number of clients we have in our job database. This database contains many jobs for each client. We have tried option 2 and 3 in the link above and both are successful at showing the distinct number of clients for *all* records in the database. We then do a search for another attribute to limit the result set (eg: by year) and then the resultant count just shows a "?".

       

      Can anyone offer a suggestion on how to show a summary field that counts the number of distinct clients in a job database and is then filterable by other parameters contained in each record?

       

      Many thanks.

        • 1. Re: Count unique values in records after Perform Find
          taylorsharpe

          I assume the method you used is the ExecuteSQL function with something like this:

           

               ExecuteSQL ("SELECT COUNT (DISTINCT LocationField) FROM ContactTable" ; "" ; "")

           

          If you get a question mark in return to an ExecuteSQL, it means your SQL statement is incorrectly formatted.  The most common reason is that you use field names that FileMaker is very loose about formatting, but are not OK for SQL.  SQL does not allow fields to have spaces or begin with a number of underscore and you cannot use any word that is a reserved SQL function (there are about 250 of them).  To be safe, format fields in SQL like this:

           

          FileMaker way

               ContactTable::LocationField

          SQL way

               \"ContactTable\".\"LocationField\"

          This makes your ExecuteSQL look like:

           

               ExecuteSQL ("SELECT COUNT ( DISTINCT \"ContactTable\".\"LocationField\" ) FROM \"ContactTable\"" ; "" ; "")

           

          Can you show us your SQL and we can help make sure you have formatted it correctly. 

          • 2. Re: Count unique values in records after Perform Find
            cnschulz

            Thanks for your response however you are referring to option 1. I'm not using execute sql because that will *always* return the total of *all* records as it operates independently to the view I am in.

             

            have tried option 2 and 3 in the supplied link as stated above.

            • 3. Re: Count unique values in records after Perform Find
              philmodjunk

              If you add a WHERE clause to your SQL query that then returns the same record set as your find, you will get the count you want.

               

              There is also a way to get a unique count of a found set using a method called sum the reciprocal. It requires the use of summary fields, a calculation field that uses get summary with sorting the records to group records th have the same value.

              • 4. Re: Count unique values in records after Perform Find
                cnschulz

                I can NOT use ExecuteSQL as the WHERE clause can not be dynamically/programmatically linked to the Find parameters.

                 

                Your suggestion simply lists one of the two alternatives I have tried as listed in the supplied link. These work fine when Find All is used however after a Perform Find the summary field shows a "?"

                • 5. Re: Count unique values in records after Perform Find
                  taylorsharpe

                  Summary Fields showing a ? simply means the field is not wide enough to show all of the numbers.  Just make the field wider. 

                  • 6. Re: Count unique values in records after Perform Find
                    taylorsharpe

                    cnschulz wrote:

                     

                    I can NOT use ExecuteSQL as the WHERE clause can not be dynamically/programmatically linked to the Find parameters.

                     

                    I do this all the time.  WHERE certainly can be dynamically programmed because with simple text manipulation.  I can have it work on the found set by grabbing the list of primary keys and using an and or I can have the where statement grab fields or values from other fields or variables.  Here is an example of counting the number of records in the current found count which is the same as Get ( FoundCount ).  But I could have had is display fields or summaries or whatever in SQL. 

                     

                    Let ( [

                     

                    F1 = PrimaryKeySummaryListField ;  // assume it is a # field

                    F2 = Substitute ( F1 ; ¶ ; ", " ) ;

                    F3 = "SELECT

                                   COUNT ( * )

                              FROM

                                   SomeTable

                              WHERE
                                   PrimaryKey IN ( " & F2 & " )" ;

                    F3 = ExecuteSQL ( F2 ; ¶ ; ¶ )

                     

                    ] ; F3 )

                    • 7. Re: Count unique values in records after Perform Find
                      philmodjunk

                      Yes, I mentioned  of the methods in the list, but you haven't told us exacrly what you set up nor how it failed for you. I did also point out some key factors that can keep it from working. I would be willing to discuss it further if you are willing to do so. I can also tell you that I've used this method and it does work.

                       

                      A question mark result means one of two possible things: the field is too narrow or you have a calculation error such as dividing by zero. My guess would be that your calculation field to get the reciprocal isn't returning a value and thus you get a divide by zero. Maybe you haven't sorted your records by the break field, maybe the break field is from a different table than the summary field. Maybe there is some other issue.

                      • 8. Re: Count unique values in records after Perform Find
                        cnschulz

                        Sorry Taylor, if its working with a find all, the perform find is only going to return a number equal to or less than the total amount of records in the database...

                        • 9. Re: Count unique values in records after Perform Find
                          cnschulz

                          Hmmm, lets explore this,

                           

                          I would need to generate the WHERE clause from the entry section prduced after hitting the "Find" button. As this is a complex area I may have 3 or 4 rows of entry for the perform find. ie: status A in 2016 and status B is 2015 or widget = true. We can search on literally any field in a job, not just primary keys. Im not sure how to grab the perform find critera programmatically however Ill keep this thread free from that discussion.

                          • 10. Re: Count unique values in records after Perform Find
                            cnschulz

                            Thanks philmodjunk,

                             

                            Option 2 and 3 are set up *exacty* as outlined in the link supplied. Only the field names have been changed) They work as documented initially. The problem is that *after* a subsequent perform find is applied to the view, a "?" is returned in the summary field.

                             

                            Option 2 does require the results to be sorted and after sorting according to the category required, the question mark remains.

                             

                            Option 3 states that sorting is not required

                            • 11. Re: Count unique values in records after Perform Find
                              philmodjunk

                              Are you using Mac or Windows?

                               

                              Last time I discussed "sum the reciprocal", the other person had a Mac and I had windows, in the version that was current then, they ran into an issue with getting the fields to update after sorting that I did not encounter on a Windows system. That was several versions ago so that it might not be the issue here.

                               

                              What version of FileMaker are you using?

                               

                              I can upload a demo as long as I know that you are using a fairly recent copy of FileMaker.

                               

                              With regards to ExecuteSQL, you would set up a layout where, you can enter some values into some global fields, then you do not perform a find but just use the values of the globals as the values used in your WHERE clause to get your counts. This does limit the type of finds you can perform, but if you can get this to work, it's often your best option here.

                               

                              I don't see any way to get the self join to work short of importing the found set into another table used solely for this purpose--which is pretty ridiculous.

                               

                              There is another method not described in this article that will work using a list of primary keys, a conditional value list, and a relationship using that list of primary keys as a match field. This method uses a "list of" summary field to get a return separated list of Primary keys based on your found set, a calculation field that simply copies the value of this field to get a field that can be used as a match field in the conditional value list's relationship and then you count the values returned by the ValueListItems function.

                              • 12. Re: Count unique values in records after Perform Find
                                JackRodges

                                The simplest method requiring the least debugging and confusion is to create a self-link on your customer id field. In the editor click on the field and drag to the left and then back onto the field. This creates a self join to.

                                 

                                I name it something like "Invoice | SelfJoin | Invoice"

                                 

                                Now add a calulated field  Count( selfjoin field) or sum(FieldOne) where it has the calculation of 1

                                 

                                The nice thing is you can sum all number fields, using this self join and not need SQL, etc. just a FIleMaker calc.

                                 

                                Using this self join you could add to your invoice field Total Sales, Total Payments, Balance due using this idea.

                                • 13. Re: Count unique values in records after Perform Find
                                  philmodjunk

                                  As stated earlier, this does not work as there can be records not in the found set that are matched to by the self join and thus the count does not reflect just those records in the found set.

                                  • 14. Re: Count unique values in records after Perform Find
                                    philmodjunk

                                    Here is a demo file that illustrates the three methods that I described earlier. There are three layouts: Data Entry: Shows the "raw" data and I used it to create the records used in the demo. The "expanded summary" layout shows how some of the "working parts" do their thing. The Dashboard illustrates that all three methods can be used without any type of list view or summary report style layout at all.

                                     

                                    In both layout 2 and 3, there's a category drop down list at the top. When you select a category, a script trigger performs a script. That script performs a find and does the needed sorting so that the sum the reciprocal method will work. The ExecuteSQL method ignores the found set, but because the query uses the same criteria as the scripted find, it will produce the same count as the other two methods. The value count conditional value list items method does not require sorting the records but is based directly on the current found set.

                                     

                                    And there are very specific picky details that have to be correct for each of these methods so it's not really a surprise to me when someone tries to implement one of these approaches and has trouble. Please compare the demo file to your own and see if you can spot what is keeping your attempts from working.