10 Replies Latest reply on Apr 21, 2016 10:07 AM by Extensitech

    Cleaning up a calculation

    SteveMartino

      Hi Forum.  In my effort to try to do things the a better way, I was wondering if there is a better way to do this calculation.

      What I am trying to accomplish is to not allow a user to leave this screen without entering data in 4 specific fields, while creating a new record.

      I don't want to use field level validation (not is empty checkbox), because these fields are used on other layouts where the fields can be empty).

      Another option I considered is using all globals for creating a new record, then validating, then committing.  I think this may be the better approach.

      But here's what I have.  Here's the script in a screen shot, and the calculation:

      Let( [
             @state=Case(IsEmpty(DESIGNCHART::State);1);

             @city=Case(IsEmpty(DESIGNCHART::Station);1);

             @DesignTemp=Case(IsEmpty(DESIGNCHART::Design_Temp_Winter);1);

             @Dday=Case(IsEmpty(DESIGNCHART::Heating_DDAYS);1);

             @Count=Count(@state;@city;@DesignTemp;@Dday);

             @txt=Case(@Count>1; "The following fields require data-";
                @Count>0; "The following field requires data-")
            
           ];

      @txt & Substitute(
               List (
                     Case(IsEmpty(DESIGNCHART::State);"State") ;
                     Case(IsEmpty(DESIGNCHART::Station);"City") ;
                     Case(IsEmpty(DESIGNCHART::Design_Temp_Winter);"Design Temperature-Winter");
                     Case(IsEmpty(DESIGNCHART::Heating_DDAYS); "Heating Degree Days")
                      ); //End List
                             
                "¶";", ")  //End Substitute

      )//End Let

       

      Any thoughts, suggestion, help are always greatly appreciated.

      Thanks

      Steve

        • 1. Re: Cleaning up a calculation
          bigtom

          if you want to maintain other areas where the fields can be empty using globals sounds like the right way to go.

          1 of 1 people found this helpful
          • 2. Re: Cleaning up a calculation
            Extensitech

            Played with this for a bit. The function below would allow you to add more fields at the top, or re-use this in other tables without a lot of repointing.

             

            It also does a few new niceties.

             

            Incidentally, it's not necessary to use "Case ( condition ; 1 )" since condition all by itself would produce a Boolean 1 or 0. (Some prefer to be explicit... I prefer to educate. )

             

            So.... FWIW

             

             

            Let ( [

            // just a placeholder. If you're curious (or want to seem more) see http://extensitech.com/calculation-function-formatting/

            x=1

             

            // get a list of human readable field names for each error

            ; fld_err =

                 List (

                      Case ( IsEmpty ( DESIGNCHART::State ) ; "State" )

                      ; Case ( IsEmpty ( DESIGNCHART::Station ) ; "Station" )

                           ; Case ( IsEmpty ( DESIGNCHART::Design_Temp_Winter ) ; "Design Temp-Winter" )

                 ; Case ( IsEmpty ( DESIGNCHART::Heating_DDays ) ; "Heating Degree Days" )

                 )

             

            // capture the count

            ; cnt_err = ValueCount ( fld_err )

             

            // turn the list into csv, with "and" for the last comma, for legibility

            ; csv_err = Substitute ( fld_err ; ¶ ; ", " )

            ; csv_err_rep = Position ( csv_err ; ", " ; 1 ; PatternCount (  csv_err ; ", " ) )

            ; csv_err_cnj =

                 case (

                      cnt_err > 1

                      ; Replace ( csv_err ; csv_err_rep ; 2 ; " and " )

                      ; csv_err

                 )

             

            // conditional s for singular verbs

            ; sing_s = Case ( cnt_err = 1 ; "s" )

            // conditional s for plural nouns

            ; plur_s = Case ( cnt_err > 1 ; "s" )

             

            // message

            ; err_msg =

                 Case (

                      not IsEmpty ( fld_err )

                      ; "The following field"

                      & plur_s

                      & " require"

                      & sing_s

                      & " data: "

                      & csv_err_cnj

                 )

             

            ] ;

             

            err_msg

             

            )

             

            HTH

            Chris Cain

            Extensitech

            1 of 1 people found this helpful
            • 3. Re: Cleaning up a calculation
              SteveMartino

              Thanks Chris for responding with your thoughts and ideas.

               

              Incidentally, it's not necessary to use "Case ( condition ; 1 )" since condition all by itself would produce a Boolean 1 or 0. (Some prefer to be explicit... I prefer to educate.  )

              Feel free to educate!

              I was aware of that, but I couldn't think of another way to 'count' the fields that were empty.  I couldn't get ValueCount to work (for me).

              Nice, easy to read calculation!  I'll have to delve into it tomorrow.

              Thanks again

              Steve

              • 4. Re: Cleaning up a calculation
                SteveMartino

                Yes I agree.  Either way I still have the long calculation.

                Thanks again

                Steve

                • 5. Re: Cleaning up a calculation
                  user19752

                  It should be able to check empty fields as listing up fields in Count() function.

                  If [ Count ( DESIGNCHART::State ; ... ) < 4 ]

                  1 of 1 people found this helpful
                  • 6. Re: Cleaning up a calculation
                    erolst

                    Adding to what Chris said, I would use whitespace more liberally´– and of course, do it totally different

                     

                    Let (

                      missingList =

                        List (

                           Case ( IsEmpty ( DESIGNCHART::State ) ; "State" ) ;

                           Case ( IsEmpty ( DESIGNCHART::Station) ; "City" ) ;

                           Case ( IsEmpty ( DESIGNCHART::Design_Temp_Winter ) ; "Design Temperature-Winter" ) ;

                           Case ( IsEmpty ( DESIGNCHART::Heating_DDAYS ) ; "Heating Degree Days" ) ;

                           )

                       ;

                       Case (

                          not IsEmpty ( missingList ) ;

                         Let (

                          missingMessage =

                            Case (

                              ValueCount ( missingList ) = 1 ;

                              "This field requires data:" ;

                               "The following fields require data:"

                              ) ;

                          missingMessage & ¶ & Substitute ( missingList ; ¶ ; ", " )

                          )

                       )

                    )

                    1 of 1 people found this helpful
                    • 7. Re: Cleaning up a calculation
                      keywords

                      On a slightly different tack from the other posts, note that in the first part of your calc

                      (@state=Case(IsEmpty(DESIGNCHART::State);1) and similar lines you have a superfluous Case() statement.

                      Case ( IsEmpty ( fieldName ) ; 1 ) is essentially the same thing as IsEmpty ( fieldName ). Both will return 1 if the referenced field is empty. The only difference is that your method will return a blank if the referenced field is not empty, whereas the other will return 0—which are essentially the same thing anyhow, although 0 is more explicit.

                      1 of 1 people found this helpful
                      • 8. Re: Cleaning up a calculation
                        user19752

                        So Count ( IsEmpty ( ... ) ; ... ) will fail since IsEmpty() always has value.

                        1 of 1 people found this helpful
                        • 9. Re: Cleaning up a calculation
                          SteveMartino

                          Thanks everyone for your help, and pointers.  Explaining my misuse of case is starting to sink in

                          Of course thanks Oliver & Chris for providing cleaner calculations. 

                          • 10. Re: Cleaning up a calculation
                            Extensitech

                            Shorter calculations are nice, of course. I try to minimize the number of things I'd need to change in order to reuse the function, and get those things into a nice obvious place at the top. In order to do that, I don't mind making my function as long as it needs to be. It makes the current function take a bit longer to type, but it makes future uses (and readings) of the function much easier, so it tends to pay off in the long run.

                             

                            FWIW.

                             

                            Chris Cain

                            Extensitech