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

# date calculation: changing deadline field based on current date

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:

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

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 (

)

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

danielone wrote:

I have the following date fields:

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

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.

Greets,

Karina

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

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.

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

• ###### 4. Re: date calculation: changing deadline field based on current date

To clarify this a bit:

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

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