14 Replies Latest reply on Jun 10, 2017 3:58 PM by ThinkSoft

    Percentage complete

    m.mcdonell

      I am wanting to have a percentage complete visual based on the number of fields entered. Any ideas? This would be used for my crm before it is handed off. I can use it for many other applications as well. To complete a new account entry, etc.

      suggestion?

        • 1. Re: Percentage complete
          siplus

          you have to incorporate some rules, because simply putting an "a" into an email field won't cut it.

           

          Basically, if you know you have 20 fields on your layout, you can have a calc field ftot defined as List(f1; f2; f3...) and valuecount(ftot) versus how many fields you have could be an approach to the problem.

          • 2. Re: Percentage complete
            erolst

            One way:

             

            Let (

              all = 7 ; // see below

              filledIn = Count ( field1 ; field2 ; field3 /* andSoOnAndSoForth */ )

              ] ;

              filledIn / all * 100

            )

             

            You need to either hardcode the value of all, or explicitly list the field names, but you need to do it in two places, because short of a recursive function, you cannot use that list to evaluate the filled count. Either way, this formula must be adapted whenever the number of involved fields changes.

             

            It is usually better to use a related table for storing and managing similar things, not the least because the formula

             

            Count ( Task::entryField ) / Count ( Task::primaryKey ) * 100

             

            simply uses the tasks that it finds.

             

            We cannot tell if that approach makes sense in your scenario

            • 3. Re: Percentage complete
              erolst

              That is assuming that none of the fields can itself hold multiple values.

              • 4. Re: Percentage complete
                siplus

                Well in a scenario like this I would enforce field validation, that way I'm sure I'm getting valid data in the fields.

                The actual question should be "how many VALID field contents I've got".

                Ad yes, I'm assuming. Let me be unassumed.

                • 5. Re: Percentage complete
                  erolst

                  Assumin' ain't no crime. Just wanted to point out the potential fly in the ointment.

                  • 6. Re: Percentage complete
                    philmodjunk

                    There is a function that will return a list of all fields on a layout and thus you would not need to hard code the number of fields, nor, with the use of evaluate and a custom function, hardcode the field names.

                    • 7. Re: Percentage complete
                      m.mcdonell

                      That sounds the most promising. Can you give me a simple example of a layout with 3 fields.

                      Thanks for the help

                      • 8. Re: Percentage complete
                        philmodjunk

                        As an unstored calculation field, don't even need CF.

                         

                        Let ( [ FList = Fieldnames ( "" ; get ( layoutName ) ) ;

                                  FTotalCount = ValueCount ( FList ) ;

                                  FilledCount = ValueCount (

                                                   Evaluate ( "List ( " & substitute ( 

                                                   FList ; <pilcrow> ; "; " ) & " )" ) )

                                   ] ;

                                    FilledCount / FTotalCount

                                  ) // Let

                         

                        I typed this in on my smart phone where I can't easily  enter the pilcrow so replace <pilcrow> with the paragraph symbol. And since I used my phone, I haven't tested it. Might be a ; or ) wrong in it. Note that FieldNames returns field names without table occurrence names when field is from layout's table occurrence. Thus this works as a calculation field from the context of the layout's table occurrence. To generalize it for all layouts would require using a custom function to loop thru the list of field names and add the needed table occurrence names.

                        • 9. Re: Percentage complete
                          TomHays

                          Here is a variation of the function to work properly with fields with line breaks in its data.

                           

                          Let([

                          FList = FieldNames( Get(FileName); Get(LayoutName) ) & "¶";

                          FTotalCount = ValueCount(FList);

                          EvalCalcListTemp =

                          "If(IsEmpty(" &

                          Substitute (FList; "¶"; "); 0; 1)¶+If(IsEmpty(" );

                          EvalCalcList = LeftValues(EvalCalcListTemp; ValueCount(EvalCalcListTemp) -1);

                          FilledCount = Evaluate(EvalCalcList)

                          ];

                          FilledCount & "/" & FTotalCount

                          ) // Let

                           

                           

                           

                          Note that this function and philmodjunk's earlier version both tally up all fields on the layout.  This may include fields that you have on there which are not for user data entry.  For example, in my test database I have an auto-enter primary key field displayed on the layout containing 5 fields.  This is reported always as 1/5 completed since the auto-enter is filled in on any new record.

                           

                          -Tom

                          • 10. Re: Percentage complete
                            TomHays

                            If you want to be selective on which fields on the layout are required for completion and omit fields that are not for user data entry, you can specify the fields explicitly in the calculation.

                             

                            Let([

                            FList =

                            GetFieldName(MyTable::Firstname) & "¶" &

                            GetFieldName(MyTable::Lastname) & "¶" &

                            GetFieldName(MyTable::Phone) & "¶";

                            FTotalCount = ValueCount(FList);

                            EvalCalcListTemp =

                            "If(IsEmpty(" & 

                            Substitute (FList; "¶"; "); 0; 1)¶+If(IsEmpty(" );

                            EvalCalcList = LeftValues(EvalCalcListTemp; ValueCount(EvalCalcListTemp) -1);

                            FilledCount = Evaluate(EvalCalcList)

                            ];

                            FilledCount & "/" & FTotalCount

                            ) // Let

                             

                             

                             

                            -Tom

                            • 11. Re: Percentage complete
                              philmodjunk

                              You can also set up a utility layout with only the fields  you want to check. This is a common tactic with this technique.

                              • 12. Re: Percentage complete
                                siplus

                                The bad thing is that we live in a world in which we must encourage every bozo to fill out a form by patting on his shoulder with a "great job, man, you filled 5 fields out of 12, keep going, you're doing great"

                                • 13. Re: Percentage complete
                                  TomHays

                                  philmodjunk wrote:

                                   

                                  You can also set up a utility layout with only the fields you want to check. This is a common tactic with this technique.

                                   

                                  That's a good way to avoid another pitfall which is having the calculation performed in an unstored field and having that field on the layout (not as a merge field).  The calc field then becomes involved in a self-referential spiral.

                                   

                                  One significant problem with the calc is update triggering.  Ideally the user should be getting feedback as fields are filled in.  Putting a ModificationTimestamp field in as a trigger field for Evaluate() works when the record is committed.

                                   

                                  Immediate feedback as the fields are entered and tab-navigated is more involved.

                                   

                                  -Tom

                                  • 14. Re: Percentage complete
                                    ThinkSoft

                                    you can use a button bar with 10 buttons on it each representing 10% - either horizontally or vertically

                                    each button would have conditional formatting on it to turn green, like LED counters, when the required % value is attained.  ensure you name each button so
                                    have a calc field work out the percentage complete - placing the calc field either on the layout as a "visual" cue or place it outside the layout boundary, this allows it to be referenced while the layout has focus

                                    -------------------------

                                    alternative 1 - use a webviewer and some javascript to display the percentage complete, in a variety of formats either a progress bar, histogram, circular graph or change colour progressively from red to green on a single square.

                                    There are lots of examples - check out Soliant's WebView example for a few

                                    ---------------------

                                    alternative 2 - conditional format for all those fields that need completion.  Each field has it's own rules concerning content and completion.  If the field is empty or doesn't match the rules, colour it bright red.

                                    ---------------------

                                    alternative 3 - place all the mandatory fields in order, set your tab order on each field so that the details are completed in the required order.  set a script trigger on each field so that they user can't fill in the next field until the previous required fields are complete.  This one's a bit complex and a lot of work BUT it can be useful to guide the "bozos" as described above.

                                    --------------------

                                    alternative 4 - follow on to 3 above -add a calc field for the % complete, say "_pcComplete".  place it on the screen, but hide it by entering 1 in the "Hide Field When" property.  For each field in the "mandatory group", enter a calc that will hide the field unless the previously required field is completed.

                                     

                                     

                                    Hope these ideas spark some ingenuity...

                                    Tony