one field depends on the values in the other field

[effa]

I have 2 fields: STATUS and DATE.

Status field has 5 pre-defined values to choose. I change them manually from time to time.

However I need one status value to be set automatically when the date value is in future (date is higher than today's).

[steigrafx]

I assume STATUS is a text field with a value list.  Go into the Options for the STATUS field and select "Calculated value."  Use this calculation:

 

Case (

Date > Get ( CurrentDate ) ; "YourSpecialStatusInWords" ;

Date = Get ( CurrentDate ) or Date  < Get ( CurrentDate ) ; Status

)

 

Uncheck "Do not replace existing value of field (if any)."

 

I'm no expert, but I think that may work for you.

[effa]

It works and better than nothing, but I made a mistake: status value should be set automatically when the date value is in the PAST (date is EARLIER than today's).

I tried to change sign (greater to less), but it does not change value automatically unless we manually touch DATE field.

I want STATUS to change automatically when a new day comes (and DATE value becomes earlier).

[doughemi]

Status must be an unstored calculation field to automatically update.  A text field with an auto entered calculation will not update. This means you cannot manually change Status as you have in the past.

 

If you can tell us a little more about the process you are automating, perhaps a different approach can be found.

[LaRetta]

I want STATUS to change automatically when a new day comes (and DATE value becomes earlier).

 

Unfortunately, this will not work since a standard field will not know to update itself when the current date passes.   Only unstored calculations can keep themselves up to date.  And since you need the ability to change the value, you can't use unstored calculation. 

 

You will need to leave Status alone (remove it's auto-enter calculation) and instead create a new unstored calculation as:

 

Case ( dateField < Get ( CurrentDate ) ; "your special phrase" ; Status )

 

... and in Storage Options, check 'do not store calculation results'

 

Also consider what you want if the date field is empty because this calc will currently produce the special phrase.  If you wish to skip the evaluation if date is empty then it would be adjusted to:

 

Case ( dateField < Get ( CurrentDate ) and dateField ; "your special phrase" ; Status )

 

This calc will produce regular Status result if date field is empty.

Sorry, Doug, I hadn't seen your response.  

[effa]

Thanks for the replies!

Doug, I have a db with products that have expiration date(this date may change) and we should change products' statuses when the date becomes less than today's(i.e. product is expired). In future we may renew product and set a new date and change status to other-than-expired (and all again till the new date comes).

Also we have several other statuses for products and need to change them manually (that's why unstored calculation offered by LaRetta seem to not work here).

 

So, in short the logic is: many available statuses that should be available to change manually. If according to the DATE a product is expired status should automatically change to 'expired'.

 

I thought about script which may run on start of db, check all records and change statuses, however I'm not sure how to realise it.

[doughemi]

A looping script will be quite slow if you have several thousand records in the table.  

 

Create an unstored calculation field NewExpired = If(DateField < Get(Current Date) and Status ≠ "expired"; 1; 0)

This gives a field that contains a 1 if the record is expired and you have not updated the status.  In other words, only newly expired records.

This is bound to be a much smaller set than the entire table, and you can do a find for NewExpired = 1 .  At that point, if the only thing you need to do is change the status to  "Expired", you can use a Replace Field Contents[] script step.

 

To have a script execute upon startup, select File->File Options... , check the Perform Script checkbox and enter the name of the script.