You need a calculation field in School Dates that extracts just the year: Year ( your date field here ).
Then you add a new occurrence of School Dates so that you can link this occurrence to SignUps by year fields. You can then set up your value list from this new occurrence and then select: Include Only Related Values, starting from SignUps.
This is is called a conditional value list.
You can find working examples of such a basic conditional value list as well as a number of other approaches to creating conditional value lists here:
The file you attached will come in handy for sure.
I did exactly what you put in the post and it didn't work. (no data displays in the dropdown)
I either (a) didn't understand a step and misapplied it, or (b) you miss-read what I wrote. Judging on your contributions to this forum, I'd say it was more like "(a)"
I'll be specific with what I did to follow your instruction:
1. I created a new field in School Dates called calc_year. This pulls just the year. I confirmed by putting it on the layout.
2. Setup a new TO of School Dates.
3. I didn't have a year field in Signups, and no use for one. Confused now (but going with it anyway), I created it--so I could create a new relationship as you suggest. So now I have: Signups : year <---- > School Dates : calc_year.
4. I created a new value list. I set the table for "Use Values from First Field" to the new TO and selected the PK. Then I selected the new TO for "Also Display Values from Second Field," and selected the field that I want to show in the dropdown (the field that combines Date, Track and Location). The I selected "Include only related values starting from" and the TO was not there, because we had selected it in the first two options.
I must be taking your instructions too literally, but I feel for my level, the instructions were not complete enough. I then looked at your file and tried to reverse-engineer it, but couldn't figure it out. I think it's a bit above my level. Plus, the way you have your data doesn't match how our data works.
I appreciate the help though.
Okay...I got this to do what I wanted it to do, but in a different way, using a calculation.
Table: School Dates
Fields are: Date1, Track, School Type
I created a field called Date_Track_Combine, that combines those fields, as I mentioned in my first post. So the result looks like this: 3/17/2017, Laguna Seca, 2-Day Camp
I then created a calc field called: calc_year. The formula is:
Case ( Year ( Date1 ) ≥ Year ( Get ( CurrentDate )) ; Date_Track_Combine )
As you can see, this field will only show the current year, which is all I wanted in my dropdown. The value list is then set to calc_year in the section "Also Display Values from Second Field."
This works. Do you see any problem with the way this is done? It tested it to make sure previous year records don't disappear from the Signups table, and they remained intact.
SignUps should be your starting from TO.
You then have to select a year in the year field in sign ups before values appear in the value list. If you check your original post, you said:
Drop Down Select Year user selects 2017.
This is the new field for year that I had you add to SignUps and link to the new TO of SignUps by Year fields.
Think of it this way: if you were to set up a portal to school dates on your SignUps layout, how would you do it? The portal's TO would be the same as that used for your value list and the layout's TO is your "starting from" TO. (This portal comparison to conditional value lists is also demonstrated in the Adventure file. )
That all makes sense.
I'll have to be more careful the way I word/structure my posts.
Using a dropdown to select year, is not what I wanted to do. I wrote that in my post and gave an example, but the way it was worded, it looked like the dropdown was what I wanted.
Then what DO you want?
I wanted the user to only see current years, without needed to select the current year. It would just be one less step for data input and eliminate the possibility of signing up a student to a school that already happened.
Turns out they wanted to be able to also search the dropdown. So I created popover, added a portal with a search field. The portal is set to filter only the current year with: Year ( T33e_signups_SCHOOL_DATES||id_school_dates::Date1 ) ≥ Year ( Get ( CurrentDate ) )
So it's working just they way they want it, and I learned a lot along the way. Cheers for your guidance.
This is what it looks like:
Just because there's a field for specifying the year does not mean that the user has to be the one to put that year in place. A script can do that or it can be an unstored calculation that calculates the year from the current date.
If you go back to the adventure file, take a look at the "hard wired conditional value list" examples and you'll see two methods for setting up a CVL that do not require the user to select the filter value used to limit the values in the value list. The first method could be adapted to what you need here.