5 Replies Latest reply on May 6, 2017 2:51 PM by taylorsharpe

    Count ( field_name )

    pbqc

      I got a File from a new customer with a table with 258 fields. From a quick look, it seems quite a few of these fields are always empty.

      I would like to use the Count () function on each of the fields, but the function does not allow calculated field name. Any suggestion on how I could do that in a script ?

       

      I got the list of fields in the table in a global variable, and even created a record for each field name in a utility table, but then I am stuck, don't know what to do next.

       

      Any help will be really appreciated.

       

      Thank you in advance,

       

      Pierre

        • 1. Re: Count ( field_name )
          philmodjunk

          Why count and not IsEmpty?

           

          Not isempty ( GetField ( "tableOccurrence::Field" ) )

           

          or

           

          count ( GetField ( "tableOccurrence::Field" ) )

           

          returns the same result. Where I have text in quotes, you can put a variable or an expression as long as the value returned is in the same format.

          • 2. Re: Count ( field_name )
            taylorsharpe

            FYI, I'm not sure how you got the field names in a global variable, but the easiest way is:

             

            Let ( [

            F1 = "SELECT

                      FieldName

                 FROM

                      FileMaker_Fields

                 WHERE

                      TableName = ?" ;

            F2 = ExecuteSQL ( F1 ; ¶ ; ¶ ; "[Insert Table Name]" )

            ] ; F2 )

             

             

            You will end up with something like

             

                 Field1

                 Field2

                 Field3

                 ...

                 Field258

             

             

            So you then just have a looping script such as that counts nulls and not nulls for each field such as:

             

            Set Variable [ $ListOfFields ; Value: List ( "Field1" ; "Field2" ; ... "Field3" ) ]

             

            Set Variable [ $RecordCount ; Value:

                 Let ( [

                      F1 = "SELECT

                                     COUNT ( * )

                           FROM

                                    TableName" ;

                      F2 = ExecuteSQL ( F1 ; ¶ ; ¶ ) ;

                      F3 = GetAsNumber ( F2 )  ] ;  F3  ) ]          

             

            Set Variable [ $n ; Value: ValueCount ( $ListOfFields ) ]

            Set Variable [ $i ; Value: 0 ]

            Loop

              Set Variable [ $i ; Value: $i + 1 ]

              Exit Loop If [ $i > $n ]

              Set Variable [ $Field ; Value: GetValue ( $ListOfFields ; $i ) ]

              Set Variable [ $NotNull ; Value:

                 Let ( [

                      F1 = $Field ;

                      F2 = "SELECT

                                    COUNT ( \"" & F1 & "\" )

                                FROM

                                     TableName

                                WHERE \"" &

                                     F1 & "\" IS NOT NULL" ;

                      F3 = ExecuteSQL ( F2 ; ¶ ; ¶ ) ;

                      F4 = GetAsNumber ( F3 )  ] ;  F4  ) ]     

              Set Variable [ $Array ; Value: List (  $Array ; $Field & Char ( 9 ) & $NotNull & Char ( 9 ) & $RecordCount - $NotNull ) ]

            End Loop

            • 3. Re: Count ( field_name )
              pbqc

              Thank you !

               

              That count ( GetField ( "tableOccurrence::Field" ) ) did it !!!

              • 4. Re: Count ( field_name )
                pbqc

                Thank you Taylor.

                 

                I got the fields names in a variable by using a custom function I found on Brian Dunning :

                 

                ListFieldsFromTable (TableOccurrenceName)

                 

                works great.

                 

                Pierre

                 

                • 5. Re: Count ( field_name )
                  taylorsharpe

                  You might notice the custom function you cite from Brian Dunning has the same type FileMaker Table ExecuteSQL statement I showed above in the script (I think mine is more nicely formatted to see what is going on).  I personally don't see the advantage of making it into a custom function but if it is easy and works for you, then good.  As with many things in FileMaker, there can be multiple ways to accomplish the same goal.