10 Replies Latest reply on Mar 4, 2015 11:10 AM by LauraW

    Create Calculation Field Using Executesql

    LauraW

      Title

      Create Calculation Field Using Executesql

      Post

      Working on setting up a basic database for a small company with very little technical knowledge.  Is it possible to set a calculation field with the following:  ExecuteSQL ("SELECT COUNT(DISTINCT fieldname)
      WHERE FoundSet;" ; "" ; "")

      The current field I have set up with this calculation returns a ?

       

        • 1. Re: Create Calculation Field Using Executesql
          philmodjunk

          What do you mean by "WHERE foundSet"?

          That would not seem a valid SQL Syntax as SQL knows nothing of found sets. And you are missing the FROM clause.

          This query would return a count of all records in YourTable where Field1 = "red":

          ExecuteSQL (
          "SELECT Count ( Distinct Field1 ) FROM YourTable

          WHERE Field1 = 'red' " ;

          "" ; "" )

          • 2. Re: Create Calculation Field Using Executesql
            LauraW

            Thank you so much for your time and reply!  I am attempting to place a field with this calculation in a layout that is used to print a list of records.  I need a count of unique records for a found set only.  If I place this calculation as you have indicated with the FROM table name; it returns a count from the entire database.  Which is the reason I tried to put in the FOUNDSET.  I have tried using a merge variable and creating a script but that also did not work.  So, if you are willing to try to help me again, this is what I am looking to accomplish. 

            First, I am using Filemaker PRO 12. 

            I need a count of unique records from a found record set.  Let's say I have a database with 1,000 records.  I search a a field and pull up a found set of 250 records.  From the 250 record found set, I would like to know the unique count of a field that has repetitions.  The calculation you so kindly suggested returns a distinct count of the entire database of lets say 855 which is more than the 250 record set which is obviously not the count I am looking to get.  The field that needs to be counted is a number field lets say each record is numbered one through 230.    Several records have the same number, for example three records have the number 25 in the field; six records have the number 30 and so on.  I need a calculation that will detect that the 25 was used three times but only count it as one; etc.

            Hope I am making sense.  Any suggestions are greatly appreciated!!!

            • 3. Re: Create Calculation Field Using Executesql
              philmodjunk

              To repeat. ExecuteSQL knows nothing of Found Sets. It interacts directly with tables at the data level. What layout is current, let alone it found set has no effect on this function.

              I did not suggest a function to produce that count but gave it as an example of correct syntax. (I couldn't tell from your original post what exact result you wanted.)

              So you can't use ExecuteSQL to get such a count. You can, however, set up two summary fields and a calculation field that uses the GetSummary function to compute this count. This method is called "sum the reciprocal". (If you had FileMaker 13, there is a less arcane method that can be used that sets up a conditional value list based on the current found set and then you can count the number of values in the value list.)

              For more on "sum the reciprocal": How to count the number of unique occurences in field.

              • 4. Re: Create Calculation Field Using Executesql
                LauraW

                Hi, took a few days of R&R.  Thank you for your response!  Tried the sum of the reciprocals.  It will work only on the first sort, after that it returns a blank.  Searched the forum and found someone else had the same issue.  I could not find where it was ever solved.  I feel like I am so close, I hate to give up at this point.

                • 5. Re: Create Calculation Field Using Executesql
                  philmodjunk

                  I don't know what you mean by "It will work only on the first sort".

                  • 6. Re: Create Calculation Field Using Executesql
                    LauraW

                    The field set up with the summary pointing to the calculation field returns a number once.  I have not pinpointed when it recalculates and supplies a number.  I have gotten the correct number once or twice.  As someone in earlier posts have indicated it may be when changes are made in the database.  Otherwise the summary field with the total count does not return a number.

                    • 7. Re: Create Calculation Field Using Executesql
                      philmodjunk

                      As long as the records are correctly sorted to include the "break" field specified in getSummary, this should not be the case.

                      The break field, summary field and calculation with get summary must be defined in the same table.

                      The layout used to show the results should be based on that table as well.

                      • 8. Re: Create Calculation Field Using Executesql
                        LauraW

                        The "sUniqueCount" field is a summary field of the "sFraction" field which is the calculation field for the sum of the reciprocal.  The "TotalPackages" is the summary field counting all the "TagNumber" field.  I have tried moving the "sUniqueCount" in different parts on the layout and get the same result.  Tends to be sporadic in returning a number.  I do a sort based on the "TagNumber" field which is the break field.  The image is a screenshot of the layout in the layout mode.  I have only one table.  The "TagNumber" field is a number field but not a calculation.

                         

                        • 9. Re: Create Calculation Field Using Executesql
                          LauraW

                          The following is an example of when the field calculates.  There are 40 records but only 20 unique tagnumbers.  When I go to a different sort or layout and come back to this layout with the same found set, the package total is gone.  I have sorted according to tagnumbers but still no count.  If I made a change in the layout it may come back; not quite sure.

                          • 10. Re: Create Calculation Field Using Executesql
                            LauraW

                            Phil - Thank you for your help.  I believe my issue has been solved.  The problem was that I did not have under the Options for the Summary Field, the running total box checked and then the restart summary for each sorted group checked, choose the table and field and it appears to be recalculating with every new sort