11 Replies Latest reply on Aug 8, 2013 2:19 AM by ohannis

    If Statement quation

    ohannis

      Good Day guys,

      what's the best way or format to construct a function that will result the following.

       

      If ( DATE_FIELD ≥ DATE_FIELD_1 and DATE_FIELD ≤ DATE_FIELD_2); "Report"; "Don'r Report";

       

      In human language I am trying to check if the selected date (DATE_FIELD) is within the range of DATE_FIELD_1 and DATE_FIELD_2. If it is then show result 1 or result 2.

       

      It seems I cannot user and in the if statement.

       

      Please help.

        • 1. Re: If Statement quation
          Hudi

          Your statement's syntax is slightly off.

           

          If statement Syntax: If ( test ; resultOne ; resultTwo )

           

          If ( date_fielddate_field_1 and date_field date_field_2; "report"; "Don't Report")

           

          HTH

          • 2. Re: If Statement quation
            keywords

            Try this calc:

             

            Let ( [

             

            thisDate = DATE_FIELD ;

            firstDate = DATE_FIELD_1 ;

            lastDate = DATE_FIELD_2  ;

            inRange = If ( thisDate ≥ firstDate and thisDate ≤ lastDate ; 1 ) ] ;

             

            If ( inRange = 1 ; "Report" ; "Don't Report" ) )

            • 3. Re: If Statement quation
              ohannis

              Hudi,

              I have tried the statent you mentioned but I still have a problem with the statment. check results below.

               

              DATE_FIELD   DATE_FIELD_1  DATE_FIELD_2     RESULT

              8/6/2013          8/5/2013               8/82013                Report

              8/5/2013          8/1/2013               8/4/2013               Report << I guess the DATE_FIELD_2 is not being checked.

              • 4. Re: If Statement quation
                ohannis

                Keywords, Thanks alot this worked perfect.

                • 5. Re: If Statement quation
                  taylorsharpe

                  One thing to look at careful in such situations is to consider what will happen if any of these dates are blank (no value). 

                  • 6. Re: If Statement quation
                    keywords

                    Good point. For what it's worth, the effect in my calc is as follows:

                     

                    thisDate blank—not in range

                    firstDate blank—in range

                    lastDate blank—not in range

                     

                    thisDate AND firstDate blank—in range

                    thisDate AND lastDate blank—not in range

                    firstDate AND lastDate blank—not in range

                     

                    I think it would be correct to summarise the logic here as: null can be LESS THAN, but cannot be GREATER THAN,  any value.

                     

                    Another case could be that firstDate is later than lastDate; if so the result is not in range.

                    • 7. Re: If Statement quation
                      ohannis

                      Well in my case this will do the trick. for the following reason.

                      Date_Field is the invoice date.

                      Date_Field_1 and Date_Filed_2 is user controlled. In this case the user selects the calander date from a drop down box.

                       

                      On a differant note. Is there a way that I can force and field to be populated with an identical date. eg. I have a report layout with the following info

                       

                      Part number     Description     Customer info      Amount     Invoice date     Week Start Date     Week End Date

                       

                      In this situation user changes the Week Start Date and Week End Date. Is there a way i can make all the cells/Fields under the Weeks Start and Week End change value when i select a date ? This is handy if my report is long.

                       

                      Thanks

                      • 8. Re: If Statement quation
                        keywords

                        If the dates are selected for the purpose of a one off report could you make the fields global? Better still why not just enter Week Start Date and have Week End Date auto complete by calculation—Week Start Date + 7?

                        • 9. Re: If Statement quation
                          ohannis

                          Good Idea,

                          I never used gloal fields. How can i get that done ?

                          • 10. Re: If Statement quation
                            keywords

                            Go to the field definitions (File –> Manage –> database), select the field name, click options button at bottom right, click on the storage tab, check "choose global storage (one value for all records)".

                            • 11. Re: If Statement quation
                              ohannis

                              Works like a charm.

                              Thanks