Try Year ( Date ).
"2. List (Related::Date) ----->FM doesn't like the word "Related" and won't allow me to enter the calc. I must be missing something here!"
Where you are using "related" you have to use the name of a table occurrence--that's a "box" on the relationship graph. And it must be linked to the table occurrence specified as the context for your calculation or the layout's table occurrence if used in script step. This still won't work for what you want, but that's why it "didn't like" the word 'related'.
As Mike suggested, year (date) can be defined in a calculation field in the same table as your dates. Your value list can then list values from this calculation field.
Thanks for the quick response!
Mike, I wasn't aware of that function, thanks. However it just shows one record.
philmodjunk, that's what I figured, but there must be something wrong with my relationship. (I'm still working up to a complete understanding on how that all works.)
All the data is in the same table. Do I need to create a self-join relationship? I thought I did, but must have messed something up.
I created a number field: ID_School_Dates (indexed, auto-serial)
I created a number field: id_schooldates (indexed)
Then I created a new TOC for the table and linked the two fields together with the = sign.
Where's my mistake?
Here's some screenshots:
calc_year is set to: Year ( Date )
I also tried: Year ( sum_date )
You don't need a relationship just to get a value list of years.
You can use a relationship that matches a number field formatted with the value list to the calculation field that computes the year if you use a script with Go To Related Records to pull up your found set. But you can also set up a script to perform find and then no added relationship is needed.
And are you sure that T34 and T34c are occurrences of the same table? The way that the relationship line connects to each occurrence box suggests that they aren't.
I didn't think so.
I don't think my knowledge is sufficient enough yet, to track with you regarding those script options. Are you suggesting I use a script trigger??
Yeah, they are the same...I expanded them:
It's kind of hard to build understanding when something doesn't respond the way you expect it. For example, the FM manual gives this example for the list function:
So in a new field calculation called "test," I put in: List ( Date1). It doesn't list. It just shows one record. But a summary field will list. However, I've been struggling to find a way to filter that last.
I'm obviously missing something. Appreciate the help and an ear to my rant.
1 of 1 people found this helpful
You don't need list for this. Do you know how to set up a "use values from field value list"? That's the first step here.
While you don't need it here, List, if you go back and check again is normally used like this:
List ( RelatedTable::Field )
And then it lists all values from field over the set of related records. It won't drop out duplicates and we don't need it here for our value list.
There's a simple scripted find that can work from that popup menu via a script trigger. That's where I'm headed here, but first to get that value list of years. Do you know how to do that?
I...we did it.
Here's what I did (in case anyone else comes across this) :
1. Created a calc field called Date_Filter, with calc: Right ( Date1 ; 4)
> This gave me just the year, e.g 2016, 2017.
2. Created a value list from Date_Filter.
> Magically, this gave me a list without duplicates!
3. Created a global field called gFilter_year.
4. Used gFilter_year on the layout and set it as a dropdown for the value list created on step 2.
5. Wrote the below script to execute on OnObjectSave, and boom! It worked.
Allow User Abort [ off ]
Enter Find Mode [ Pause: Off ]
Set Field [mytablename:Date_Filter ; mytablename::gFilter_year]
Set Error Capture [ On ]
Perform Find 
Go to Record/Request/Page [ First ]
Refresh Window [ ]
Scroll Window [ Home ]
Once you got me thinking about how to do this, I just about nailed it, bur had to reference another post (which you just wrote), to figure out the script. Here's the post: Perform Find based on value selected from drop-down menu
. Created a calc field called Date_Filter, with calc: Right ( Date1 ; 4)
This where Mike and I recommended using:
Year ( Date1)
It's bit clearer that way that you are getting the year portion of the date in your calculation.
After needing to combine some dates in another field, I can see now, why that's better.