7 Replies Latest reply on Aug 14, 2014 1:48 AM by wheymans

    Value List based on date-field

    wheymans

      Title

      Value List based on date-field

      Post

           Hello,

           I have a PERSONS-table and a related WORKSHOP-table (with 2 fields: workshop_name and workshop_date).

           On the PERSON-table i have a portal. In this portal want to create a value list-field that includes all the workshop_name-fields that have a workshop_date that is higher than the current date.

           Is this possible? How to do so?

            

        • 1. Re: Value List based on date-field
          philmodjunk

               Do you want a portal to Workshop that only lists these names? That appears to be what you want, but it's not a value list, it's records listed in a portal.

               Option 1

               Set up this relationship between PERSONS and an occurrence of WORKSHOP (You may need to create a new Tutorial: What are Table Occurrences? of WORKSHOP if there is already a relationship between the two.)

               PERSONS::anyField X WORKSHOP::anyField (you can select any field you want as match fields here, but change the = operator to X.)

               Add your portal to this occurrence of WORKSHOP and specify this portal filter:

               WORKSHOP::workshop_date > Get ( CurrentDate )

               Option 2

               Add this unstored calculation field, cToday, to PERSONS defined as: Get ( CurrentDate ). Select Date as the result type.

               Define this relationship between PERSONS and an occurrence of WORKSHOP:

               PERSONS::cToday < WORKSHOP::workshop_date

               Now put a portal to this occurrence of WORKSHOP on your Persons layout. This second option is also the relationship to use for a conditional value list if it truly is a value list that you want.

          • 2. Re: Value List based on date-field
            briancrockett

                 You can't change the value list but the newer versions of FM allow you to Filter the actual portal. In Layout mode double click on the portal and check "Filter portal records"  Enter "workshop_date > Get(CurrentDate)" in the calculation dialog for Filter Portal. 

                 Caveats:

                 I find that the filter doesn't automatically change when the data changes. If someone else changes workshop_date and you are on the layout with the portal. The change won't display until your window refreshes.

            • 3. Re: Value List based on date-field
              wheymans

                   I think i was not clear enough.

                   In the portal i have a WORKSHOP_name field. I want to be able to assign a person to different WORKSHOPS through this portal. Each portal-row represents a different workshop where a person is assigned to.

                   Within a year this will be a very long list so i want the list only to include those workshops that have a date that is higher than the current date.

              • 4. Re: Value List based on date-field
                philmodjunk

                     What is not clear is what table your portal references.

                     From here it seems like there is a third table needed. You'd need one table for workshops, one for Persons and the third for your portal where a person would register for one of the workshops listed in the workshops table:

                     WorkShops|ValueList>-----Persons----<Registration>-----Workshops|Retistration

                     The new objects here named "Workshops" would be two table occurrences of your workshops table.

                     The second option that I mentioned  would define the relationship between Persons and WorkShops|ValueList. It can be used to set up a conditional value list of only workshops that have a date in the future to use for selecting Workshops in a portal to Registration. Do you need assistance with how to set up that conditional value list?

                • 5. Re: Value List based on date-field
                  wheymans

                       I have indeed three tables.

                       Table 1: Persons

                       Table 2: Workshops

                       Table 3: Persons_Workshops

                       Table 1 is connected to table 3, Table 2 is calso connected to table 3 (like this: table 1 -- table 3 -- table 2). I have a portal in the Persons-layout where I have a workshop_name-field. I can create records in the third table by assigning a person to a workshop. So far so good.

                       I think, like you say, that i need help for setting up the conditional value list that shows only those workshops that have a date in the future.

                        

                       Many thanks in advance!

                  • 6. Re: Value List based on date-field
                    philmodjunk

                         Here are some links on conditional value lists.

                         There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                         The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                         Forum Tutorial: Custom Value List?

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

                         Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                         Hierarchical Conditional Value lists: Conditional Value List Question

                         Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                         Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                    • 7. Re: Value List based on date-field
                      wheymans

                           After reading the above info and watching the demo-file i succeeded!

                           Many thanx!