Jump to content

Limiting value list by including only related values


Ian Murphy
 Share

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

Recommended Posts

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.

Link to comment
Share on other sites

 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.

  • Like 1
Link to comment
Share on other sites

This topic is 2827 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.