6 Replies Latest reply on Jun 7, 2017 11:18 AM by philmodjunk

    Fields in Layout Count


      I haven't been able to find anywhere where anyone has remotely mentioned this task but I was curious if anyone here might have come across it and just hadn't posted or had any ideas about the task.


      To sum it up, my task stems from compliance review, and in the end I'd like a count of empty fields for each record on specific layouts to show record incompletion (aka where compliance is lacking).


      Like I said, im also very open to suggestions, so if anyone has any, I'd be very appreciative.




        • 1. Re: Fields in Layout Count

          Since you mention it's for specific layouts, I'd imagine you also need to check specific fields for the record as well (rather than all fields at once).


          You can easily create some auto-enter calc fields that will respond as your record is completed.


          Say for instance you have six fields, Field1 through Field6. These fields are divided on two layouts, layout1 and layout2.


          An auto-enter number field of L1CompPct could look something like:







          ) / 3

          Whenever the values for field1 - field3 were filled out, that calculation would update to display the percent complete for filling out the fields in that "list". The List() function would trim off any empty values for you, resulting in a simple fraction like 1/3, 2/3, 3/3 stored as a decimal value of course.


          You could then setup more auto-enter fields for each layout of your process, adjusting the list as necessary. Then you create an "overall complete" field that averages the values from each one of those fields.


          This is only one way to do it, there are other ways as well.

          1 of 1 people found this helpful
          • 2. Re: Fields in Layout Count

            The FieldNames function can list all fields on a given layout. So you could create a generic approach that works with any layout that you choose to set up by using this FieldNames, GetField and either a looping script or a recursive custom function to loop thru the field names and return a count of the ones that are empty. Note that this only works if you need a count of every empty field on a layout. If it's OK for a few to be empty, you'd have to modify this approach.


            //EmptyFields ( FieldList ; LayoutTOname )


            Let ( [ Fcount = ValueCount ( FieldList ) ;

                      FieldName = GetValue ( FieldList ; 1 ) ;

                      FieldRef = If ( PatternCount ( FieldName ; "::" ) ; FieldName ; LayoutTOname & "::" & FieldName )

                    ] ;

                    If ( IsEmpty ( FieldList ) ;

                        0 ;

                        IsEmpty ( GetField ( FieldRef ) + EmptyFields ( RightValues ( FieldList ; Fcount - 1 ) )

                       ) // If

                   ) // Let


            A typical call of this function would look like this:


            EmptyFields ( FieldNames ( "" ; Get ( LayoutName ) ) ; Get ( LayoutTableName ) )


            The second parameter is needed because fields from the layout's TO are listed without the "LayoutTO::" text that is needed for the GetField function. Fields from a related table do include that text, hence the use of PatternCount here.

            1 of 1 people found this helpful
            • 3. Re: Fields in Layout Count

              This is a great example of the "context free" approach. And would be a great way to do it.

              • 4. Re: Fields in Layout Count

                Only if the fields are ON the layout. I love the Design Functions, but they are very context specific.

                Your original idea of having the fields in the List is "hard coded" but more flexible. Not all fields are going to be "required", so one wonders if the validation needs to be on the layout for each field. Annoying as it is, the "your field is empty", can be helpful.

                Another thought is the PlaceHolder text for the field. If it's empty, the Text is "REQUIRED" and/or using conditional formatting to flag a field as required.

                It all boils down to validation and when you want to have the "alert" show...


                • 5. Re: Fields in Layout Count

                  Of course, on the layout one could have calculation fields that are empty just because their calc results in empty, and these fields have to be excluded from the computations.


                  This can be achieved probably by intersecting the found field names with an ExecuteSQL directed to the table structure, which only selects "normal" fields.


                  Some input fields are often non-mandatory, like "Sports you are good at", so probably these could be excluded by prefixing their names with NM_

                  or by autoentering "none", "N/A" etc.

                  • 6. Re: Fields in Layout Count

                    All good points, but a utility layout of just the fields you want to check with this method does exactly that, it limits the check to just those fields you put on the layout and placeholder text etc would not be in use on that layout--so it can be pretty flexible after all--though I agree that pulling meta data via ExecuteSQL is definitely another option to consider.