6 Replies Latest reply on Jul 13, 2012 1:15 PM by Jason_Farnsworth

    Incorrect Formula?

    Jason_Farnsworth

      I seem to not be getting the result I was wanting with this formula,

       

      Let ([

      DataDate = GetAsDate ( Data_Packages_Sent ) ;

      IrrDate = GetAsDate ( Job_Interrupted )

      ] ;

      Case (

      IsValidExpression ( 1 * DataDate ) ; 1 * DataDate + 14 + not Int ( Mod ( 1 * DataDate + 687 ; 365 * 4 + 1 ) / 365 );

      IsValidExpression ( 1 * IrrDate ) ; 1 * IrrDate + 14 + not Int ( Mod ( 1 * IrrDate + 687 ; 365 * 4 + 1 ) / 365 ) ; ""))

       

      What I would like is...

       

      Only one of DataDate or IrrDate is possible to have a date.

       

      Which ever one hase one add 14 days to that date and display it.

       

      If neither have any date do nothing

       

      The solution works while only using one test of the case, but while adding the second test it work the first case and if the second case is needed it displays 1/14/0001

       

      Thanks

       

      Jason

        • 1. Re: Incorrect Formula?
          erolst

          Maybe I'm missing something, but if you just want to add 14 days to a date, you don't need these complications:

           

          Let (

           

          myDate = Max ( Data_Packages_Sent ; Job_Interrupted ) ;

           

          case ( not isEmpty ( myDate ) ; Date + 14 )

           

          )

           

          Since you want to have a null result if none of the dates is set, you can forego the Case () default result.

           

           

          Message was edited by erolst, to use the shorter date addition method I always forget about

          • 2. Re: Incorrect Formula?
            ch0c0halic

            Jason may not have made his case,

             

            To add 14 to a date ... Date + 14. If the fields are defined as Type Date you don't need the "GetAsDate()" function. You don't need to multiply by 1.

             

            Case (

            //nothing in either field

            IsEmpty ( Data_Packages_Sent & Job_Interrupted ) ;

            "" ;

             

            //nothing in the default field "Data_Packages_Sent"

            IsEmpty ( Data_Packages_Sent ) ;

            Job_Interrupted + 14 ;

             

            //use default field

            Data_Packages_Sent + 14

            )

             

             

            I don't know why you've complicated things with the IsValidExpression ( 1 * X), which is a boolean test function, or what the (not Int ( Mod ( ..." stuff is for.

            • 3. Re: Incorrect Formula?
              Stephen Huston

              Is this calculation being used for field validation or in a field definition?

               

              For field validation, assuming you are trying to stop dates in both fields, you could use a case calc that returns true if the other field is empty, true if this field is empty, and false as the default (if both have dates entered).

               

              If it is the basis of a field value itself, then you can have it auto-enter nothing if both are blank, return "Error" of neither field is blank, return field1+14 if field 1 is not blank, and return field2+14 if field2 is not blank.

              • 4. Re: Incorrect Formula?
                jaylkjr

                Jason,

                 

                The "IsValidExpression" is used to verify syntax. It returns a boolean response of 0 or 1 as to whether the syntax is correct. Although I have rarely played with it, I don't beleive it kicks off unless a field is missing or there is something like a parenthesis missing in a formula.  This is a useful function to help isolate issues in a large formula.

                 

                Try This:

                Let ([

                          date1= If( IsEmpty( Job_Interrupted ); Data_Packages_Sent /* use the other date */ ; Job_Interrupted )

                /* Assuming if not one it's the other date*/

                 

                                 ] ;

                  Date ( Month ( date1 ) ; Day ( date1 ) + 14 ; Year ( date1 ) )

                )

                 

                You could also add some kind of error trap and modify it slightly to say that if both fields are empty, it will simply use a date 14 days from today:

                 

                Let ([

                          date1=Case( IsEmpty(Data_Packages_Sent) AND IsEmpty(Job_Interrupted) ;  Get ( CurrentDate );

                IsEmpty(Data_Packages_Sent) ; Job_Interrupted ;

                /* Otherwise use */  Data_Packages_Sent )

                ] ;

                  Date ( Month ( date1 ) ; Day ( date1 ) + 14 ; Year ( date1 ) )

                )

                 

                I hope that helps!

                 

                Jay Knight

                • 5. Re: Incorrect Formula?
                  comment

                  Jason_Farnsworth wrote:

                   

                  Let ([

                            DataDate = GetAsDate ( Data_Packages_Sent ) ;

                                  IrrDate =     GetAsDate ( Job_Interrupted )

                   

                  These variables sre entirely redundant; just use the fields directly. Also, as these are - hopefully - date fields, the GetAsDate() conversion does nothing.

                   

                   

                   

                  Jason_Farnsworth wrote:

                   

                   

                  Only one of DataDate or IrrDate is possible to have a date.

                   

                  Which ever one hase one add 14 days to that date and display it.

                   

                  If neither have any date do nothing

                   

                  Try =

                   

                  Let ( 
                  d = Max ( Data_Packages_Sent ;  Job_Interrupted ) 
                  ;
                  Case ( d ; d + 14 )
                  )
                  
                  • 6. Re: Incorrect Formula?
                    Jason_Farnsworth

                    Thanks guys,

                     

                    Michael I used your example it seems to work well,

                     

                    I am not sure why I over did it

                     

                    I like your approach it is clean and simple.

                     

                    Jason