10 Replies Latest reply on Jul 28, 2014 4:00 PM by philmodjunk

    Custom Value List?



      Custom Value List?


      Here is the problem:


      Whenever we have someone registering for a new class, the entire list of every class we've ever had or will have shows up.  How do I make it so the only classes available to chose from are classes in the future? When I am registering someone for a new class the search feature is not available, it is only a huge list.  I am a bit of a filemaker novice, so any help would be appreciated! 


        • 1. Re: Custom Value List?

               This feature is called a conditional value list. If you click the advanced search link above and enter that text, you'll find many threads devoted to that subject.

               You essentially have two options for restricting the values that appear in your value list:

               Option 1: Simple but limited

               Define a calculation field that only returns a value if other conditions are met and base your value list on this field instead of the original data field.

               Example: If ( include = "yes"; valuefield ; "" )

               This probably won't work for you as this field must be a stored indexed field and your "today's date" based requirement can't easily be included in a stored calculation.

               Option 2: More work to setup but much more flexible

               Define a relationship that correctly limits the number of records in your value list table to just those records that contain the values you want. Basically, if you can place a portal on your layout that lists the records you want to see in the value list, then you can use this relationship in your value list definition.

               When setting this up, specify the values as coming from the Table Occurrence you used in the above relationship and which points to the table containing your values. Select the "include only related values starting from" option and select the other Table Occurrence (the one that the first table occurrence connects to in your relationships graph) as your "starting from" choice. Usually, this second table occurrence is the same as the Table Occurrence your layout refers to.

          Please keep in mind that this is an old thread. Comments posted to it no longer pop the thread title up in the Recent Items List. Thus, I am unlikely to see any new comments posted to it. If you were referred to this thread from somewhere else, post any comments or follow up questions there rather than here. If you found this thread through your own search of the forum, start a new thread, include a link to hear, and then post your question.

          • 2. Re: Custom Value List?

            Thanks Very much for your response Phil,


            I have followed option 2, and it did create the option List I wanted.

            I can now see the Contact names in my dropdown option list, BUT when I select the name, its will not display in the selected option list

            only the corresponding ID. Please how can I display the actual value I selected OR what is the best way of getting this value?


            It's a little confusing, because even if I placed the related contact name's field on the layout it doesn't display the name either.


            Sorry if i'm not so clear, but i'm a little confused myself. but I think i'm almost there...  :)


            Thx for ur help.



            • 3. Re: Custom Value List?

              I gather you have a two column value list and have hidden the first column?


              Two options:

              1. Format the field as a pop-up menu instead of a drop down list.
              2. Place the related "name" field next to your drop down list field so that the name will appear when you exit the drop down.
              • 4. Re: Custom Value List?

                Thanks for your response... 

                Both options don't seem to work though...


                When I Format the field as a pop-up menu instead of a drop down list,
                It displays the first the name only and the others cannot be selected.


                When I place the related "name" in a text field , I think because its not directly related
                to the CONTRACT table / Layout. it does not display the text data.


                I must be doing something wrong....  :smileymad:

                • 5. Re: Custom Value List?
                     Make sure you are using a field from the same relation as the one you used for the list. But the pop-up should work with no problem.
                  • 6. Re: Custom Value List?

                    When I Format the field as a pop-up menu instead of a drop down list,
                    It displays the first the name only and the others cannot be selected.


                    It should display the name, but "others cannot be selected"? Does this mean you can't click into the field and see other names listed? If there are several rows of related record, do you see the same name for each row?


                    As DeltaTango suggested, make sure that this field is the ContactID field from your Portal's table occurrence (the name you find in portal setup.)


                    When I place the related "name" in a text field , I think because its not directly related
                    to the CONTRACT table / Layout. it does not display the text data.


                    You should have a relationship between your portal's table occurrence and your contact table that links a contact ID field in the portal table occurrence to the contact table's contact ID field. Then, the records are directly related and you can place the contact name field(s) next to the Portal Table's Contact ID field to display the name.


                    Many folks find the terms Table and Table Occurrence confusing. To learn more, click the following link:

                    Table vs. Table Occurrence (Tutorial)

                    • 7. Re: Custom Value List?

                      Really appreciate your help guys, problem has now been resolved.


                      • 8. Re: Custom Value List?

                             Took me awhile to figure this out...

                             How to make a Filtered Custom Drop down menu and to auto select that record (from that filtered pull down menu) without using any relational table connection. ..because what I need is in the same table/database.

                             I have Projects that are in various stages. WATING on estimates, IN PRODUCTION, BALANCE DUE, PAID...etc..

                             I already have a field to find other projects in other status/stages...but it was easler because it didn't need to be filtered by year. If they are open...they are in the current accounting year..so I need to see projects that have been closed during the year for quick reference AND I don't need to see the projects closed from previous accounting years....so seeing data from 5 years ago in a drop down list really blows, scrolling is not fun.

                             As the example below shows......I wanted to find ALL PROJECTS that were Cancelled, Closed or Declined in the year 2013. ( The only issue I have is that I have to plug in the status AND if the project was in 2013 ) What happens when Jan 1 rolls around? I will have to manually go into these scripts and change the year. But until then here is what I did and it works great!

                        YOU WILL NEED:

                             3 FIELDS.
                             1 Value List and
                             1 Script trigger


                        FIELD 1 - A text field you will use to drop down and view a filtered list that will set off a script trigger to pull that record up on it's own.

                        FIELD 2 - A calculation field that will FILTER out records and will show you only the data you want. (This will be your Value List here)

                        FIELD 3 -  A calculation field that pulls together the current values in a record that will match what you will select in your filtered pull down menu.  The script trigger will be using this to perform a find in and then omit other other records.

                             Here is the FIELD 2  calculation I created  that shows me ONLY PROJECTS that had a status of "Closed", "Declined" or "Cancelled" and that they were from the fiscal year of 2013.

                             If ( ( STATUS PROJECT = "CLOSED" and PROJECT YEAR = 2013 )   or
                             (  STATUS PROJECT = "CANCELLED" and PROJECT YEAR = 2013 )   or
                             (  STATUS PROJECT = "DECLINED" and PROJECT YEAR = 2013)  ;
                              PROJ ID & " " & PROJECT NAME & " – " & STATUS PROJECT)


                             Result is - it works AND ONLY shows PROJECTS from this year (2013) that have those statuses....all other projects from previous years are removed from the drop down. GREAT!

                        THE ONLY ISSUE

                             The only way this worked for the current year is if I typed 2013. I would be nice if I could use one of my global "THIS YEAR" or CURRENT YEAR fields in this formulat. I tried but they did not work.

                             I tried creating another calculation to pull the global year in to a separate text field..but no matter what I do ...I can never get the above to recoginize a "Current year" four digit or text.... It always says "Index Missing".

                        Preferred..- once the date/year on the clock/computer changes...to have the database automatically see what year it is..and have all these change automatically on Jan 1st.

                        Here are some visuals.

                        • 9. Re: Custom Value List?

                                    ...without using any relational table connection. ..because what I need is in the same table/database.

                               Just because the data is in the same table does not mean you can't set up the relationship that you need. Both sides of a relationship can be table occurrences of the same table--this is called a "self join" and that's a way to get a match to only records of the specified status with the current (or a user selected) year.

                               Find aProjects in your relationship graph, select it and use the duplicate button (two green plus signs) to create a duplicate Tutorial: What are Table Occurrences?.

                               aProjects::cThisYear = aProjects 2::Project Year AND
                               aProjects::constTermList = aProjects 2::Status Project

                               Define cThisYear as an unstored calculation with this expression: Year ( Get ( CurrentYear ) )

                               Define constTermList as: List ( "CLOSED" ; "CANCELLED" ; "DECLINED" )

                               Now you can set up a conditional value list of values from aProjects 2 and you can select the "Include only related values starting from aProjects" to limit the list to values from the current year that have a status of Closed, Cancelled or Declined.

                               And if you were to replace cThisYear with a global number field, this same set up could produce that value list for any year specified by the user.

                               PS. it's a good idea to check the date shown in the first post of an older thread. Anything 3 months or older will not appear in Recent Items when you post a new comment and thus is not as likely to be noticed by others. In those cases, it's better to start a new thread that includes a link to the original thread so that more people see your comment.

                          • 10. Re: Custom Value List?

                                 There's been a lot of confusion over how to use Option 1 for a value list where you specify an ID number for field 1 and a name field for field 2. Here's more on that variation of this option:

                                 Define your calculation field as:

                                 If ( Category = "PutSpecificValueHere" ; NameFieldGoesHere )

                                 Be sure to specify text as the result type and be sure to keep this as a stored, indexed field.

                                 Then, when you set up your value list, specify the standard ID field for field 1, but select this calculation for field 2. You will then need to either Hide field 1 or specify that the values be sorted on field 2. With either setting, all values where this field is empty, will be omitted from the value list.