Jump to content

ValueList which relies on CurrentDate calc


xochi
 Share

This topic is 2617 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by xochi
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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 ...

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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).

 

:offtopic:

Link to comment
Share on other sites

This topic is 2617 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.