3 Replies Latest reply on Oct 2, 2012 10:09 AM by philmodjunk

    Count of fields IsEmpty vs Total count of All fields (Each Record)

    RickM_1

      Title

      Count of fields IsEmpty vs Total count of All fields (Each Record)

      Post

           I am new to FM (1 day). I am trying to count the number of fields in each record where the field value is empty. Then compare that to the total number of fields in the record and return a % of completed fields. I want to report on the records where not all fields have been completed. I am importing in the records so I cannot mandate the fields be filled in.

            

        • 1. Re: Count of fields IsEmpty vs Total count of All fields (Each Record)
          philmodjunk

               A simple expression that counts the non empty fields is:

               Count ( Field1 ; field2 ; field3 ; .../and so forth )

               which requires you to manually list all fields, but the fields do not have to be physically present on the layout.

               You can then subtract this count to get the number of empty fields.

               A more elaborate expression that will count the number of empty fields on the current layout and that will adjust automatically if you add or remove fields from that layout is: (but now the fields have to be on the layout to be counted.)

               Let ( fieldList = FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ;
                       ValueCount ( fieldList) - Evaluate ( "Count ( " & Substitute ( FieldList ; ¶ ; "; " ) & ")" )
                     )

                

          • 2. Re: Count of fields IsEmpty vs Total count of All fields (Each Record)
            RickM_1

                 Thanks for the quick reply.

                 The simple expression worked perfectly. ( i called it CountofComplete )

                 I have tried to get the %complete by using Div(CountofComplete;12) but get 0

                 If CountofComplete  = 6 and my total field count is 12,    I would like to see 50%

            • 3. Re: Count of fields IsEmpty vs Total count of All fields (Each Record)
              philmodjunk

                   Div is integer division. Remember doing long division by hand in school?

                   Div returns the quotient and Mod returns the remainder.

                   If the result is less than 1, your quotient is 0--the result you are getting here.

                   Use CountOfComplete / 12

                   to get a decimal result.