Calculation field, Index and Value Lists: how to have them work togheter?
I have one table that I use to create a value list, the fields are:
id, startDate, calculatedDateOne, calculatedDateTwo
startDate is a date field that stores the same date for all rows in table. Indexing is set to none and the option "Automatically create indexes when needed" is activated;
calculatedDateOne is a calculation field that uses the startDate field to calculate a new date value. Index options are the same as startDate field.
calculatedDateTwo is a calculation field that uses get(CurrentDate) and the calculatedDateOne to obtain a new/final date value. Index options are the same.
What I need to do is to use these table fields to create a Value List for a dropdown field in a layout: so to create the value list I select "use values from field option", I use the id as the first field and I use the CalculatedDateTwo as the second field.
That seems to work and do what I suppose to get. The problem is that the calculation fields are not updated when the current date changes. The Value list is always the same obtained when the index is created the first time.
I cannot remove the index and I cannot set the calculation fields not to store the index because the Value List does not work anymore due to missing index. So I have a value list that is not updated real time.
Things are even worse if I try to set the startDate field to be a Global Storage: Value Lists do not accept calculation fields that use a Global Storage value.
Does anybody have a suggestion about how to fix this and have a real time Value List for my layouts?
Thanks a lot!