1 2 Previous Next 15 Replies Latest reply on Jul 1, 2011 9:52 AM by philmodjunk

    How to sync Date, Age, Grade, School fields?

    MarkPeters

      Title

      How to sync Date, Age, Grade, School fields?

      Post

      I want to have Fields for Date, Age, Grade/Semester, and School for various events throughout the course of one's life.  I have already calculated the values on excel, so for each event, there is the corresponding Date, Age, Grade/Semester, and School value.  Date, Age, and Grade/Semester are all unique values, except of course School has only 4 values.  

      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.  

      I was able to do this by forming a relationship between a serial ID field on the table where I imported the value lists, and a serial ID field on the table where I want to display the Date/Age/Grade/Semester/School fields according to the event that record refers to.  However, this requires me to maintain a field for the serial ID, and to update the other fields via the Serial ID only.  I edited the value list for the serial ID so that it would display "Date" values and I clicked "only show second field", and it does show the Dates when I click the pop-up, but after it has been selected, it shows the serial ID number in the field, and not the date.

      I'm sure I could fix it if I keep playing with it, but I feel like there has to be a really simple way of doing this.  Anybody know?  

        • 1. Re: How to sync Date, Age, Grade, School fields?
          philmodjunk

          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.

          • 2. Re: How to sync Date, Age, Grade, School fields?
            MarkPeters

            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).

            • 3. Re: How to sync Date, Age, Grade, School fields?
              MarkPeters

              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"

              • 4. Re: How to sync Date, Age, Grade, School fields?
                philmodjunk

                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.

                • 5. Re: How to sync Date, Age, Grade, School fields?
                  philmodjunk

                  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.

                  • 6. Re: How to sync Date, Age, Grade, School fields?
                    MarkPeters

                    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.

                    • 7. Re: How to sync Date, Age, Grade, School fields?
                      philmodjunk

                      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.

                      • 8. Re: How to sync Date, Age, Grade, School fields?
                        MarkPeters

                        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...

                        • 9. Re: How to sync Date, Age, Grade, School fields?
                          philmodjunk

                          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.

                          • 10. Re: How to sync Date, Age, Grade, School fields?
                            MarkPeters

                            ok....how do I do that?

                            i don't know where the layouttable /lookuptable are, ive never seen those?

                            • 11. Re: How to sync Date, Age, Grade, School fields?
                              philmodjunk

                              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.

                              • 12. Re: How to sync Date, Age, Grade, School fields?
                                MarkPeters

                                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.  

                                • 13. Re: How to sync Date, Age, Grade, School fields?
                                  MarkPeters

                                  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.

                                  • 14. Re: How to sync Date, Age, Grade, School fields?
                                    MarkPeters

                                    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. 

                                    1 2 Previous Next