ValueList which relies on CurrentDate calc

[xochi]

I have a field "Status" = If(EndingDate < Get(CurrentDate); "Expired"; "Current")

Then I have a relationship which is set to match "Status = kCurrent" where kCurrent is a calc field "Current".

This works great, except...

As the date changes, the value of Status doesn't change, since it's an indexed, stored calc field.

If I change Status to an unstored field, then the calculation updates in real time. However, the relationship breaks, since you can't have relationships that terminate on an un-indexed field.

Is there any clever way to have a value list which works the way I want it to? I suppose I could change the field to a Lookup, and then run a script which re-lookups all the records in the table once per day, but that seems inelegant.

Edited June 4, 2014 by xochi

[comment]

I have a field "Status" = If(EndingDate < Get(CurrentDate); "Expired"; "Current")

 

Clearly, this field needs to be unstored (if it's even needed at all).

 

 

Then I have a relationship which is set to match "Status = kCurrent" where kCurrent is a calc field "Current".

 

Clearly, the relationship needs to match the EndingDate date field with an unstored calculation (or a global field) on this side of the relationship, so that:

 

ThisTO::cToday < ThatTO::EndingDate

 

 

Is there any clever way to have a value list which works the way I want it to?

 

Uhmm... what way would that be? You didn't say anything about a value list.

[xochi]

Thanks for the help - to answer your questions in reverse order:

 

* This was posted in the "ValueLists" sub forum, so I thought that it was self-evident as the topic.

 

* Moving the calculation (which determines which records are selected) out of the calc field, and into an actual relationship, makes sense. 

 

* My actual calculation has 3 states: "Current", "Expired", and "Future" --  fortunately, I really only care about "Current" for my value list, so I can probably set that up as follows:

  This::cToday < That::EndingDate  AND

  This::cToday >= That::StartingDate

 

 

* In other circumstances, this technique may not work.  Imagine if the calculation was more complicated, e.g. something like "A and B or (C but not D) and (E < F)"  - that would not be easy to translate into FileMaker's relationship system.

 

But I think your idea will work, so I'll go for it.  Thanks!

[xochi]

I realized one hitch:  this only works if Both StartDate and EndingDate are not blank.   I had cases where there was a valid StartDate, but no ending date.    I fixed this by making a calc field for EndingDate - if it's blank, then it sets to a date far in the future)

[comment]

In other circumstances, this technique may not work.

 

 

So far, I have not encountered such circumstances.

 

 

This was posted in the "ValueLists" sub forum, so I thought that it was self-evident as the topic.

 

I am afraid you overestimate me - I am still not sure what the value list does. But it seems like it's working for you, so ...

[xochi]

I am afraid you overestimate me - I am still not sure what the value list does. But it seems like it's working for you, so ...

 

The value list is simply set up to follow the relationship from ThisTO to ThatTO - I guess that at its core, this question doesn't have much to do with ValueLists, so much as it is about relationships in general.  

 

It would be handy if, say, one could set a Find operation as the source of a ValueList values - that would give a lot more flexibility...

[comment]

It would be handy if, say, one could set a Find operation as the source of a ValueList values - that would give a lot more flexibility...

 

That won't happen unless Filemaker change one of their core paradigms - namely that value lists are based on an index (as are relationships).