5 Replies Latest reply on May 15, 2016 9:27 AM by karina

    date calculation: changing deadline field based on current date

    danielone

      Hi all,

       

      And nice to meet you all! I have a question I am sure you can help me to solve

      I am designing a solution and I try do describe my need:

       

      I have the following date fields:

      "first deadline"

      "second deadline"

      "third deadline"

       

      and so on until "twelfth deadline".

      First deadline is user input with a drop down calendar, other deadlines are calculation fields with first deadline with +1 month each. So far so good.

       

      Now I have in my solution a field when I need to display the next deadline based on current day.

       

      EXAMPLE

       

      first deadline -> 1/2/2000

      second deadline ->1/3/2000

      third deadline -> 1/4/2000

      and so on.

       

      On the days from 1/1/2000 to 1/2/2000 I have to see in this field "1/2/2000"

      On the days from 2/2/2000 to 28/2/2000 I have to see in this field "1/3/2000"

      On the days from 1/3/2000 to 31/3/2000 I have to see in this field "1/4/2000" etc.

       

      I created a "current deadline" calculation field with the following calculation:

       

      Case (

       

      first_deadline  ≥  Get(CurrentDate) ; first_deadline;

      first_deadline  <  Get(CurrentDate) ; second_deadline;

       

      )

       

      and it works for the first and the second deadline only. I tried several solutions but no one works.

      Can you kindly give me an input?

       

      Many thanks!

       

      Daniele

        • 1. Re: date calculation: changing deadline field based on current date
          erolst

          danielone wrote:

          I have the following date fields:

          "first deadline"

          "second deadline"

          "third deadline"

           

          and so on until "twelfth deadline".

          That's not really a good data structure; but if you really must have all these fields in one table, at least use a repeating calculation field with 12 repetitions, so you don't have to repeat the same calculation over and over, and keep your schema somewhat clean.

           

          With a repeating field, the calculation would be

           

          Let (

            cd = Get ( CurrentDate ) ;

            Date ( Month ( cd ) + Get ( CalculationRepetitionNumber ) ; 1 ; Year ( cd ) )

          )

          • 2. Re: date calculation: changing deadline field based on current date
            karina

            Hi,

             

            You can make two fields, current date and month current date and base your calculation on that fields.

             

            I also added an PDF with the calculations I used.

            Screen Shot 2016-05-15 at 16.58.40.png

            Greets,

            Karina

            • 3. Re: date calculation: changing deadline field based on current date
              erolst

              karina wrote:

              You can make two fields, current date and month current date and base your calculation on that fields.

              I also added an PDF with the calculations I used.

              Screen Shot 2016-05-15 at 16.58.40.png

              Greets,

              Karina

               

              If you really advocate using twelve fields, you may be interested in a few things:

               

              1. If you use the Date() function as a constructor, rather than text, you a) get a date that is not locale-dependent, and b) are free to let FM's calculation engine take care of any carry-overs [so you get Date ( 12 + 2 ; 31 ; 2016 ) = Date ( 3 ; 3 ; 2017 ) without any effort on your part].

               

              2. If you really advocate using twelve individual fields, know that GetFieldName ( self ) can be used in a calculation; so with a consistent field naming scheme (e.g. deadline01, deadline02 etc.), you can simply use

               

              Let (

                cd = Get ( CurrentDate ) ;

                Date ( Month ( cd ) + GetAsNumber ( GetFieldNameSelf ( self ) ) ; 1 ; Year ( cd ) )

              )

               

              i.e. the same calculation in each of those twelve field.

               

              which of course is the same calculation as recommended above for the very simple solution of using a repeating field (where each repetition knows its “identity”, so you can implement a “poor man's recursion” without having to rely on a naming scheme, or do lots of repetitive work).

               

              3. And of course you don't need those two new fields.

               

              Regards,

              Oliver

              • 5. Re: date calculation: changing deadline field based on current date
                karina

                Hi Oliver,

                 

                You're right.

                An repeating field can be an perfect solultion, we use them in several cases.

                I don't know what the purpose in the future is.

                 

                If she want to base further calculations on the fields, make a relationship with the fields, or export the data?

                In that case I prefer not to use a repeating field.

                 

                Daniele, if possible you should use Olivers solution.

                 

                Greetz,

                Karina