Calcuation base on todays date.

[RET]

Does anyone know how I can create a calculation that will GetRepatition whenever a date field(Expiration date) comes within 90 days from todays date.

In other words the first Repetitions when the "expiration date" field comes with in 90 days from today date. another when it more then 90 days and the 90 days are up.

With each of the "GetRepetition" it would choose a different picture from Repetitions 1,2 and 3 based on this calculation.

RET

[danjacoby]

So it's a calc field defined as a container that returns one of three different images.

First of all, get rid of the repeating field. Replace it with three global container fields, each one containing one of the images.

The calc is a "Case" statement, i.e.

Case (Status(CurrentDate) < ExpirationDate and

ExpirationDate - Status(CurrentDate) < 91, ImageA,

Status(CurrentDate) > ExpirationDate and

Status(CurrentDate) - ExpirationDate < 91, ImageB,

Status(CurrentDate) - ExpirationDate > 90, ImageC)

This returns no image if the ExpirationDate is still more than 90 days away.

[CobaltSky]

If I understand your question correctly, you want one image to show when the expiry date is more than 90 days away, another to show when it is 90 days or less away and a third to show when it has passed.

I agree with Dan that there may be a case for making the field you reference global (unless you have different images for each record) but I'm not sure why he suggests that you abandon repetitions in this instance. In many situations the use of repetitions is a bad idea - but I'd have thought this is one of the very few instances where their use may be acceptable.

If you want to persist with repetitions despite Dan's caution, (and assuming I've correctly understood your requirements) then a formula which would allow you to achieve this is:

Case(Expiration date - Status(CurrentDate) >= 90, PictureField,

Expiration date - Status(CurrentDate) < 0, GetRepetition(PictureField, 3),

GetRepetition(PictureField, 2)).

This assumes that the name of the repeating field where your pictures are stored is 'PictureField', that it has the image you want displayed before the 90 day mark is reached in repetition 1, the image you want displayed when there is 90 days or less to go in repetition 2 and the image to be displayed when the expiration date has passed in repetition 3.

It also assumes that your calculation us set to be unstored and that its result type is specified as container.