Limiting value list

[Davidatlamont]

I need help. I have a list of Jobs with corresponding Job Titles. I want to use this field as a value list, HOWEVER I only want displayed as part of pop-up list Titles for Jobs that are not 'completed'.

Completed jobs are flagged as such. Removing these from value list will limit pop-up list to around 30 items, as opposed to several hundred if value list contains ALL jobs.

Can I use the "Include only related values starting from" box to accomplish this.

I'm new to FM so please be specific.

Thanks.

[QuinTech]

Hi, David. You have the right idea in using "Include only related values starting from". Check out these links to get started.

Jerry

[Mandu]

I have a similar requirement: I want a value list of names of Events whose starting date is still in the future. The Event records from which the value list is generated contain an EventName and a StartingDate; how do I create a value list of EventNames whose StartingDate is greater than today?

I am running FMP6. QuinTech's link is to an example given in FM7, and I don't really follow the explanation. Plus I'm not sure that "related values" will get me the records I want (i.e. whose date is after today).

Thanks for any clues!

Chap

[RalphL]

If you have a completed flag for those events with the older dates you could generate a list of completed events and a list of all events. Then using the Troi Text Plug-in you could XOR the 2 lists to get of the list of uncompleted events.

http://www.troi.com

[Mandu]

Thanks - I'm part of the way there. I should reiterate that I am using FMP6, not 7, and some of the info in this thread seems to be FMP7-centric.

I created a calc. field (EventFuture) in each Events record which is 0 if StartingDate is less than today, and 1 if StartingDate is greater than or equal to today. That is the "completed flag" you mentioned.

I'm still stumped by how to define a Value List consisting solely of EventNames from Event records whose EventFuture is 1. When I specify the field to use for the value list, I guess I should use "Only Related Values" and specify a self-relationship between Event::EventFuture and Event::c_One (a calc field that evaluates to 1).

From my limited understanding of self-relationships, this should create a set of Event records where EventFuture = 1, so the Value List will only contain EventNames from Event records where EventFuture = 1.

But my pop-down list is empty. Where have I screwed up? ;-)

Thanks,

Chap

[Vaughan]

Firstly, do not use the Today function: it only reclaculates itself when the file is opened, which poses great problems when the database is shared with FM Server. Instead, use the Status (CurrentDate) function instead of TODAY. Additionally, the Today function no longer exists in FMP 7, so get used to doing without it.

However, calculations that use Status() functions cannot be stored, and unstored calculation fileds cannot be indexed, and unindexed fields cannot be used to create value lists; so the whole approach of using a calculation based on the current date will not work for what you want to do (at least not without a whole lot of very complicated work).

The solution I have used is to get somebody to manually mark off each event as it is passed. In my case I made an "Active" field which starts off with the text "active" in it, but which is unchecked when it is no longer needed. This Active field is used in a calculation field to conditionally display the event information upon which the value list is displayed.

There is an alternative that involves multi-key relationships based on the Smart Ranges technique, but it too will be complicated.

http://www.onegasoft.com/tools/smartranges/index.shtml

[Mandu]

Thanks for all the useful information! -)

It looks like the stored mark is the way to go for me. I suppose that it is possible to have a periodic, scripted sweep so that it need not literally be manual, but that is another topic that I'm sure has been discussed elsewhere.

Again, I appreciate the help.

Chap