Calculated field based on date

I’m trying to add a calculated field on a (contract) type to indicate if that contract is active, based on the rule that the date needs to be in between dates in 2 other fields.

But whatever I do, my calculated field seems to be giving false as response. As a test, I added some fields to check the behavior, and both seem to be false when I do a fetch on the records:

afterStart : boolean calc “(now() >= startDate)”
beforeStart : boolean calc “(now() < startDate)”

Am I missing something regarding the use of now() or boolean values in a calculated field? Does comparison just not work with datetime values?

I have tested the expression in an evaluate function and works as expected returning true when the condition is verified

c3Grid(MyType.evaluate({projection:"(now() >= status.timestamp)",limit:10}))

can you do a test into an evaluate ?

@KrisBoyen As this is a non-stored calc (e.g. it’s a “read calc”), it will only get evaluated when you request that field explicitly in a fetch include spec. It won’t be evaluated automatically (even with an include of “this”) and it will never get calculated just reading the field from an obj you instantiate. Could that explain it?

with evaluate, I’m getting different answers, although in the grid itself, it is indicating false values as blanks.

I modified my fetch query to specifically include the fields, and even then, they keep being both false.
VASContract.fetch({include: ‘id, startDate, afterStart, beforeStart’})

Also, I’m not sure if your statement is fully correct. To test some things, I added following calculated field:
now : datetime calc “now()”
That one seems to be displayed in all records, with a regular fetch on the type without any include spec. So it doesn’t seem that the evaluate is not running if it is not included explicitly.

Seems that, even if it would evaluate correctly, my reasoning was false, as I’m not allowed to do a fetch, based on calculated values. But since my rule would be time based, it is not something that can be done with a stored calc.

In general, I want to fetch all accounts which have a contract that is currently active, which means that the current date needs to be between start and end of the contract, as I need to trigger specific processing for those contracts.
Is there a clean way to do this, or is the only option to fetch all accounts in the job and do the check then, or to have another job running before this one that stores this check on a field in the database itself.

@KrisBoyen Are you on 7.8 or 7.9? My comment was specific to 7.9. I would suggest creating a ticket with the case you want/expect to work (include what version you are on).

Hi Kris,
Any reason why you don’t simply add a filter in your fetch?
Something like the following:
VASContract.fetch({filter:"startDate < now() && endDate > now()"})

@trothwein it is indeed 7.8. Will indeed check for a ticket

@ishka I was hoping to hide the complexity of the select query in a way. The current business request is to exclude customers in a job that has no active contract, but others reasons are planned to be introduced. So the final goal of my thinking was to have a flag on customer level that indicated if the job was allowed to run for him or not.

But I can currently indeed add an exists statement in the fetch, and if the rules get too complex in the future, I maybe need to foresee a step that will precalculate the value and store it on customer level (as it is not expected to change a lot).