I'm at my wit's end trying to manage this value list. I'm writing out the way I have it set up, not because it's working (it's not), but in hopes that a real-world explanation of what I'm trying to do will foster discussion.

The goal is for a list of events to display only events that are in the future. The records being referenced must continue to encompass the past events, but in the place where we're seeing them, we only want to see events that occur today or in the future (because there's no sense letting people register for events that are over).

The base value list is already set up as field contents:

    Use values from: rel_events::UID
    Also display values from: rel_events::DateAndEventName
    Show values only from second field

Because the second field is built in the form

    2015-03-26 Mon • Bicycle trip
    2015-03-30 Fri • Puppet show

, the alphabetical sort field is also chronological, which is fine. But now I want to screen out events that have already started. To that end, I created two fields in the Events table:

    - IsInFuture, a binary calculation that returns 1 if StartDate is equal to or greater than Get (CurrentDate) and otherwise returns 0; and
    - One, a global calc field that returns the value "1".
    
I created a self-join relationship with new TOs based on the Events table with the conditions that IsInFuture = One AND One = IsInFuture (because I wasn't sure which value I wanted to reference, and I figure 1 = 1 = 1 = 1, right).

 

When I ask the box to include only related values from that self-join relationship, I get results I don't understand (two or three of my 43 records show up).

 

Open to suggestions; thanks in advance.

 To that end, I created two fields in the Events table:

    - IsInFuture, a binary calculation that returns 1 if StartDate is equal to or greater than Get (CurrentDate) and otherwise returns 0; and

    - One, a global calc field that returns the value "1".

 

That's not going to work, because the IsInFuture calculation must be unstored in order to update as the time goes by - and an unstored calculation cannot be used as the matchfield on the "other" side of the relationship (and neither can be a global field).

 

To create a self-join relationship of the Events (?) table that would show only future events, you should define an unstored calculation field (result is Date) =

 

cToday =

Get (CurrentDate )

and make the relationship:

 

Events::cToday ≤ Events 2:;StartDate

 

Set your value list to use values from fields in Events 2, include only related values starting from Events.

 

This is assuming you are on a layout of Events when you need to use this value list. This part of your description is not that clear, and I wonder if it's really true. I would have thought you are on a layout of another table (Registrations?) - and if so, the cToday field would be defined in that table, and the relationship wouldn't be a self-join.

Thanks. Your assumption was correct - I was on a Participants table layout - and the step I was missing was that the "limiting" relationship needed to be based on that table. Solved!