Auto-update field by date

[jimlongo]

Hi, I have a field Status, it's a defined list.

The list is Pending, Confirmed, Paid, Declined

The value can be any of them, BUT If the value is Pending, 60 days after the field DateReferred I want it to change to Confirmed. ALSO it can be changed to Confirmed before the 60 days has passed.

This sort of works as a Calculation, but doesn't allow for input when a record is created.

If (Status="Confirmed" ; "Confirmed" ; Case ( Status = "Paid" ; "Paid" ; Status = "Declined" ; "Declined" ; DateReferred + 60 > Get(CurrentDate) ; "Pending" ; DateReferred + 60 < Get(CurrentDate) ; "Confirmed" ))

What would be the best way to do this? To have the field auto-update.

TIA, jim

[comment]

Data stored in a field will not change merely by time passing. You could either run a script periodically (e.g. on opening the file) to find these records and modify them, or add an unstored calculation field for display purposes only.

BTW, I think the calculation could be simplified to:

Case ( 

Status = "Pending" and DateReferred + 60 < Get (CurrentDate) ; "Confirmed" ; 

Status 

)

[jimlongo]

Thanks so much for that.

So I have a startup script that sets window size, etc., and as the last step I call a script that I've just created

Go to Record/Request/Page [First]

Loop

If [Case ( referrals::rStatus = "Pending"  &  referrals::rDateReferred + 60 < Get (CurrentDate) ; referrals::rStatus = "Confirmed" ;  referrals::rStatus )]

End If

Go to Record/Request/Page [Next,Exit after last]

End Loop

Goes through all the files but doesn't change any.

What is the mistake I've made?

[comment]

It doesn't change anything, because there's no action specified within the If[] block.

Also, it doesn't make much sense to put the entire Case() statement inside the If[] condition.

It should look something like:

...

Loop 

If [ referrals::rStatus = "Pending" and referrals::rDateReferred + 60 < Get (CurrentDate) ]

Set Field [ referrals::rStatus ; "Confirmed" ]

End If

Go to Record/Request/Page [ Next ; Exit after last ]

End Loop

...

You should also make sure that you are on the correct layout, and that your found set includes all records (or at least all records that may need changing). 

As as I said earlier, you could find these directly, instead of having to sift through the entire pile:

...

Enter Find Mode []

Set Field [ referrals::rStatus ; "Pending" ]

Set Field [ referrals::rDateReferred ; "<" & Get (CurrentDate) - 60 ]  ]

Perform Find []

...

---

P.S. Note that [color:red]& and [color:red]and are two very different things.

Edited August 23, 2009 by Guest

[jimlongo]

If [ referrals::rStatus = "Pending" and referrals::rDateReferred + 60 < Get (CurrentDate) ]

Set Field [ referrals::rStatus = "Pending" ; "Confirmed" ]

End If

Makes total sense to me, but I can't find a way to enter that in ScriptMaker.

When you add Set Field it asks for Target Field "rStatus" and then calculation.

I can't see how to get the Set Field result you've suggested.

[comment]

Sorry, I wasn't careful enough with copy/paste. The target field should be referrals::rStatus, and "Confirmed" is the calculation part. I have edited my previous message accordingly.

[jimlongo]

Fantastic, Thanks!