5 Replies Latest reply on Apr 19, 2011 3:34 PM by philmodjunk

    Limit a value list by date?



      Limit a value list by date?


      My database has two tables, People and Classes, that come together in a many-to-many relationship on a third table named "class registrations."

      On a layout in my People table, I have a portal that shows related entries in the "class registrations," table.  On this portal, there is a pop-up menu that uses a value list that is drawn from the "Class" table. The pop-up displays all the records from the class table.

      The problem is, now several classes have occurred and this pop-up menu is way too big!

      Here is my question, is it possible to limit the pop-up value list to only show records from the class table with a date in the future or within the last three months? If I am able to change this, will it change the data from older classes? Is it possible to change this and then also have the capability of doing a search for classes older than three months?

      Thanks for your help!!

        • 1. Re: Limit a value list by date?

          What you describe is called a conditional value list. If you can set up a relationship to match to just the class records you want to provide values for your value list, you can do this. Here's a hint, you can use an unstored calculation field in the class registrations table with get (CurrentDate) as one of the key fields in your relationship. You can also use inequality operators to match to a range of values or values that are greater than or equal to the date in a date field.

          These changes will only affect values listed in the value lists, they will not affect the data in the records in any way.

          Custom Value List? (Forum tutorial)

          http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list (Knowledgebase article)

          • 2. Re: Limit a value list by date?

            I figured out how to get the data already in there to look right and be limited in the right way, but in entering a new portal line it tells me: "field is not modifiable."

            The field giving me trouble (class registrations::class status) is a look-up of an indexed calculation in another table (classes::class status) and the value list is drawn from all values from classes::class status.


            • 3. Re: Limit a value list by date?

              Take a closer look at what field you are editing and any relaitonships involved. That error message is typical of an action that attempts to modify a calculation field. Either the field itself is a calculation field or it's in a a portal to a related table that uses a relationship that links to a calculation field on the portal side of the relationship. (Which would attempt to enter a value from the parent table into the calculation field and thus triggers this message.)

              • 4. Re: Limit a value list by date?

                Ok. Two weeks later and moderate success!

                I took out the calculation fields and the drop downs work great. Now there is once tiny problem, all the data is screwed up! (And yes, I thought enough ahead to make a backup).

                Here is the issue:

                Before the tables were linked like this: Class Registrations::Kf_class_id=Classes::Kp_class ID

                In order to get the conditional value list to work I cut this relationship and instead joined them through a common text field "class status" (Class Registrations::Class Status=Classes::class status). The problem is the old data that is in there doesn't show up on the portal in my drop down lists the way a new entry would--even though the data is preserved.

                Is there any easy fix to get the portal to reflect the changes I made or do I need to manually update all my data? woof.

                Let me know if you need more info to help.

                Thanks for your help! Kim

                • 5. Re: Limit a value list by date?

                  Sounds like you need two relationships that use different table occurrences to link to the same data source table. One relationship enables your conditional value list and another for your many to many relationship and the portals that are based on it.

                  A table occurrence is what we call one of the "boxes" found in Manage | Database | Relationship. You can create a new table occurrence of an existing table by selecting an existing table occurrence and then clicking the button with two green plus signs.

                  If "table occurence" is a new term, you may want to read this thread: 

                  Tutorial: What are Table Occurrences?