values in one field should depend on values in another
I have 2 fields: date, status.
I need status to be changed automatically if date is higher than today's.
How can I do this?
Define status as an unstored calculation. If you mean that "higher than today's" means a date still in the future, you might define it with this calculation:
If ( date > Get ( CurrentDate ) ; "Status Text if Date is still in future" ; "Status text if date is today or in past" )
Select Text as the result type and be sure to click storage options to make this an unstored calculation.
Yes, it works, but not as expected.
I have 5 status values and I need to be able to set statuses manually as well. Just if a date happens to be set in future status should be changed to "Status Text if Date is still in future".
I thought about script that may check all the table and change status if necessary(e.g. on the DB start), however I'm not sure how to create it and if it is a good idea.
You can't have it both ways with a field that needs to use the Get ( CurrentDate ) Function call. If you set up an auto-enter calculation, it is editable but won't update. If you set it up as an unstored calculation, it will update as time passes, but now you can't edit the value directly.
The work around for that is to use two status fields plus your date field. One status field is an simple text field and can be edited. The other uses the date to determine the status and is an unstored calculation, but it uses an expression in that calculation that copies data from the editable status field if it contains text.
Case ( NotIsEmpty ( TextStatusField ) ; TextStatusField ;
date > Get ( CurrentDate ) ; "Status Text if Date is still in future" ;
//you can put as many expressions as you need here for status values calculated from the date field
Retrieving data ...