6 Replies Latest reply on Oct 28, 2013 8:18 PM by zhuynick21

    Filter value list?

    MikeF

      Title

      Filter value list?

      Post

       Is it possible to filter a value-list?

      Example ... A specific value-list is generated from the Companies table, showing CompanyID and Company.

      But I would like only the records that are in [field] CompanyType [number] 4 to show up in the value-list.

      Thanks,

         Mike

        • 1. Re: Filter value list?
          philmodjunk

          This is called a conditional value list. The most common method employs a relationship to filter the values, but a calculation field can also be used--though the result is less flexible than using a relationship.

          Option 1, in the first linked article describes using a calculation field.

          Forum Tutorial: Custom Value List?

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

          Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

          • 2. Re: Filter value list?
            blewvelvet

            The problem with this discussion is that none of the Answers are CLEAR or offer step by step instructions. The tutorial link loses me at...

            -------------------------------------------------------

            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 ; "" )

            ---------------------------------------------------------

            When I create a Field that calculates this...I get an error dialog box because "include" is not allowed or a function of any of the language. This is VERY confusing to someone who is trying to do this for the first time.

            Some of the examples are not clear when responses are given on this site.

            Here is an example of a REAL problem that everyone can relate to:

            A drop down menu that returns a list of ONLY ACTIVE PROJECTS!

            These projects could have the status such as "ESTIMATE SENT", "IN PRODUCTION", "BALANCE DUE", "PAID" and "CLOSED"

            When PROJECTS are closed..No one would want them to end up in a long list of accumulated list of 3 years of projects so that I have to wait for a long drag to get to the bottom of the active projects. We have separate layout to look at older projects.

            So I really really need a simple step by step on creating a Drop Down list and exactly step by step on how to filter that value list.

            If the solution involves creating a new table or new relationshiop....steps should include simple things like.

            --------------------------------------

            Ok..now "Create a New Table"

            Create a New Calculation Field called "CustomProjectList"

            Type exactly the Calculation (xxxxxxxxxxx)

            Now create a New Value list

            Create script trigger that calls up the Project ID to the Project ID field to find that record.

            ------------------------------------

            I have yet to find a real answer here on this Value List filtering.

            • 3. Re: Filter value list?
              philmodjunk

              When I create a Field that calculates this...I get an error dialog box because "include" is not allowed or a function of any of the language.

              "Include" would be a field name defined in the table where you keep your values for the value list. You can name your field any way you want as long as you use that actual field name in the calculation. In your case, you would put the name of your status field there instead of "include".

              If you use option 1, you do not create a new table.

              Create a new calculation field inside your existing table, We'll call it cOpenProjectIDs. Define it as:

              If ( Status ≠ "Closed" ; Project ID )

              Now define your value list to list values from cOpenProjectIDs instead of PrjectID. You can select a project name field or some such for the second column "field 2" value to make selecting a project easier.

              Please note that I'm guessing a bit about your fields being named "Status" and "Project ID", you'll need to use the actual field names that exist in your database in place of what I have guessed.

              If that is in insufficient detail, you'll need to respond back with a more detailed description of this table in your database so that I can use the actual fields named in it.

              Please note that while "option 1" is the simplest to set up, it is also the least flexible method.

              • 4. Re: Filter value list?
                blewvelvet

                WORKED.

                 

                Ok...so I guess how you have to think of it is......when you need to detail or extract something...the details of what you want comes first; then the field you want to pull it from.

                 

                if (CATEGORY FIELD ≠ "TEXT DESIRED" ; MAIN FIELD LISTING )

                 

                So whatever details you need out of a field....just comes first and then ; THE MASTER FIELD at the end of the expression.

                 

                So NOW..what would I do to add another status item here such as "Declined" jobs in this formula so..no CLOSED or DECLINED text in that field shows in the list?

                 

                • 5. Re: Filter value list?
                  philmodjunk

                  Keep in mind that functions such as patterncount, If or Case can be researched in FileMaker  Help to learn more about them. In this case, if the first expresion evaluatesas True, the value of the field, Main field listing, is returned, otherwise the field is empty.

                  if (CATEGORY FIELD ≠ "Closed" and CATEGORY FIELD ≠ "Declined" ; MAIN FIELD LISTING )

                  Is one possibility. Here's another:

                  If ( not PatternCount ( "declinedclosed" ; category field ) ; main field listing )

                  • 6. Re: Filter value list?
                    zhuynick21

                         Hi good day!

                         Please help:

                         I have a table name "CodeMaster" contains:

                         Fields: CodeMaster | CodeList | CodeDesc

                         Data:    DEPT            | ACCT       | Accounting

                                       DEPT            | FAC          | Facilities

                                       STATUS        | AC            | Active

                                       STATUS        | IN             | Inactive

                                       .....                  | ......           | .......

                         then i have another table wherein i want to use a drop down or pop-up filtered by CodeMaster and value from CodeList.

                         please help me how can i do that.... tnxs!

                          

                         a sample or demo database i greatly appreciated....

                         regards,

                         zhuynick21@yahoo.com