Seems like the Serial ID field is the way to go. You'd just add the date field from your look up table next to the ID field so that the name is displayed when you exit the drop down.
I'm not sure I follow this description of the issue:
All I want to do is have a field for each Date, Age, Grade/Semester, and School, with a pop up value list for each, and if I change one of Date, Age, or Grade/Semester, the other corresponding values will update accordingly.
Does that mean that only one record can exist in your table that has a given date, only one for a given age and also only one for a given grade/semester?
That seems highly unusual. Couldn't you have any number of events for the same ate or grade? Can't have two events on the same date?
IF those values truly are unique in all the records of your table, then you might want to set up your value lists on these other fields with script triggers that take the value selected and use it to perform a find on your table. When it finds the matching record, it can then put the ID number of that record in a variable, return to your original layout and update the ID field with the contents of this variable.
Ok' forget my long question.
Basically, all I need help with now is the issue with my Field Value List showing the Serial ID # and not the "2nd field value" that I have clicked to show (and does show in the pop-up list, but not after it has been selected).
The dates are not specific to the day, it's more like Spring-2008
However, I would like to be able to record multiple "Spring-2008's"
I answered that question in my last post:
You'd just add the date field from your look up table next to the ID field so that the name is displayed when you exit the drop down.
If you have multiple "dates" that are exactly the same, you'll have trouble selecting the correct ID from the value list as they'll look the same. One work around is to define a calculation field in your look up table that combines data from more than just the date field so that you can tell them appart and make this your second field in the value list.
ok, i realized that the drop down list shows the serial # but the pop up list shows the value, as I wanted, so I will just use the latter. however, for the category with only 4 values (school), it only shows the school name for the 1st date/grade/age in that school's "era", but not for the following date/grade/ages, EVEN THOUGH I have plugged in a name of the school for every row in the table.
Since you are using a relationship based on ID#, what exactly have you set up for a value list, on what field and for what purpose?
If school is the second field's value and you are sorting by the 2nd field, you'll only see the first such record--which seems to match what you describe here.
This is a generic representation of the table for values is like this:
Age Date Grade School Serial ID #
4 1991 Pre-K Lakewood Pre-K 1
5 1992 Kindergarten Granville Primary 2
6 1993 1st Granville Primary 3
7 1994 2nd Grandville Primary 4
8 1995 3rd Stonebrook Middle 5
9 1996 4th Stonebrook Middle 6
It's basically more for categorization purpose, NOT to put the records in exact chronological order. Merely just to group them according to the general date, age, or grade, if I so desire to look at my records that way. The values are all stored as text not numbers, even if they are numbers. So when I have Serial ID #3 selected, it will say Age 6, 1993, 1st grade, but then "3" for School. Whereas for serial #2, it DOES say "Granville Primary". I guess I would like to just have "Granville Primary" displayed for Serial ID # 2-4, "Stonebrook Middle" for Serial ID #'s 5-10, etc...
You want this displayed on the layout or as part of the values displayed in the value list?
If you put the age, date, Grade, Schoool fields from the lookup table on your layout and have this relationship:
LayoutTable::ID = LookUpTable::ID
Selecting an ID in the LayoutTable::ID field should result in the LookUpTable::Age, LookUpTable::date, LookUpTable::Grade and LookUpTable::School fields all correctly displaying the data you have entered into the LookUpTable for that ID number.
ok....how do I do that?
i don't know where the layouttable /lookuptable are, ive never seen those?
I don't know the name of your tables so I used LayoutTable to refer to the table your layout is based on. I used LookupTable to refer to the table where you entered the values you gave an example of a few posts earlier.
Does layouttable mean the table where the value list is store? and the lookuptable is the table where I want to be able to look them up and have them displayed?
if so, I think I already did that.
ok, yeah, I already have those two perspective ID# in a relationships, or ResearchNotesID = TimeValueListID
On my TimeValueList, it DOES display the school correctly for evvery different date/grade/age value (not just the first instance of it), but for some reason that gets lost when it is displayed on the other table.
I'm imaginging that the best fix would be some kind of conditional formatting or script that just displays "Primary School" for SerialID#'s 1-12, "Middle School" for #'s 13-25, "High School" for #'s 26-40, etc. But i dont think conditional formatting works for such a request.
*okim guessing a calculation is best. ive never done one before. but the concept is to make a separate field called "school", and write a calculation that "if serial ID = 1-12, then school = "Primary", etc. etc. etc.