Editing information in a field based on another fields data

[MikeAz]

I wish to auto enter data into a field called “ProductStatus” in a table called “MSDSRegister” based on another field in the same table called “ValidUntil”. The idea behind this is:

1. When ValidUntil field is greater than or equal to the current date, the ProductStatus field will read “Current”

2. When the ValidUntil field is less than the current date, the ProductStatus field will read “Outdated”

3. When the ValidUntil field is left blank, the ProductStatus field will read “Deleted”

 

I’m very new to FileMaker, and I am used to creating Macros in Access to do this type of job, so I’m not sure where I should be entering a formula to enter data into a field (or how to express the information). I gather I should be entering it via the table by selecting the field (ProductStatus) going to options and entering a script into the calculated value section. I expect the script will read something like:

 

If [ValidUntil] ≥ Get(CurrentDate) Then “Current”

If [ValidUntil] ≤ Get(CurrentDate) Then “Outdated”

If [ValidUntil] = “” Then “Deleted”

 

If anyone has any suggestions, any example of similar scripts they might have used, or can identify a helpful thread I may have missed it would be greatly appreciated.

 

If there is anymore information I can provide to help you further understand my problem, please do not hesitate to post a request. I am using FileMaker Pro 9

 

Thankyou in advance,

 

Mike.

[LingoJango]

Not a script but a calculation

 

Case ( 
     ValidUntil ≥ Get(CurrentDate) ; 
     “Current” ; ValidUntil < Get(CurrentDate) ; 
     "Outdated" ; IsEmpty ( ValidUntil )  = "Deleted" ; 
     "Error" )

 

 

Case is used in a calc more or less like If... Else If... Else If... Else... End If in a script.

[LingoJango]

Sorry, didn't format that well.

 

Case ( 
     ValidUntil ≥ Get(CurrentDate) ; “Current” ; 
     ValidUntil < Get(CurrentDate) ; "Outdated" ; 
     IsEmpty ( ValidUntil )  = "Deleted" ; 
     "Error" )

 

Same calc, just more legible.

[MikeAz]

Thankyou for your help. That calculation did exactly what I needed it too.

 

Regards,

 

Mike