I've got a field called "expecting" which is a simple check box.

The enduser checks the "expecting" checkbox when they are notified that a client is pregnant.

What I don't want to happen... is that the "expecting" box remains checked indefinitely--since at some point, the person will no longer be in a state of "expecting".

I don't want to rely on people to manually uncheck the box when the baby is born (particularly since a pregnancy doesn't always result in a birth).

Since a person can only be "expecting" for a max of 9 mos... I'd like the box to automatically uncheck at the end of 9 mos. What would this entail? I assume there would need to be a date assigned when the box was originally checked so there would be a starting point. How would I automatically identify 9 mos from that starting date? Is that a script, a function, a trigger? And what would that look like?

Any help would be appreciated. Thank you for your thoughts on this... mp

you would need some script that will perform an update. Typically every day. (if your file is hosted on server you can run a script )

Since this initial date is subjective to accurate information the actual length of term may be -+ 270 days.

You may wish to consider adding in the field "Estimated Delivery Date" - and any value entered in there > today is considered "expecting"

And another field - Delivered On. If this field is filled in then the script or trigger would clear or invalidate the estimated date.

So as for managing individuals either part of your start up routine or your dashboard layout you could have a portal that displays all

persons Expecting +- the threshold of say a week or so.

Mary - 5/8

Joan - 5/8

Kim - 5/9

Marcy - 4/11

Dharma - 5/12 --- today

Nancy - 5/14

Joy - 5/14

Etc....

You get the idea this way you can see the only a short list of people who were expecting during that time frame.

then if people need to amend the delivery date or indicate that they had delivered.

I like that, Stephen.

You can also use startup script to (behind Freeze Window) just find these records with ExpectDeliveryDate > Get ( CurrentDate ) and clear the Expecting field.

Stephen's approach allows more functionality which is ideal whereas startup script and auto clearing is more generic answer.

Oh, and for the ExpectedDeliveryDate, it could be:

Case ( Expecting = "Expecting" ; Date ( Month ( Get ( CurrentDate ) + 9 ) ; Day ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ) ) )

ooops ... I notice that I forgot a closing parenthesis (see red)

Edited May 12, 201213 yr by LaRetta

Thank you, both. These are excellent suggestions. I do appreciate... mp