1 2 3 Previous Next 31 Replies Latest reply on Jun 3, 2011 9:04 AM by j_rameses

    How to create a value list with a search criteria?

    j_rameses

      Title

      How to create a value list with a search criteria?

      Post

      I am trying to create a vlue list that only displays the values from a specific department.  We have a table called "Companies" and in that table all the different types of companies are populated including the names.  The only fields populated are what is specific to each type of company.  Then we have other tables for the different types of companies.  For example, "Insurance Companies" & "HTC".  What I need is for the values in the value list to populate with the values(Name of company) from the "HTC" table.  In the "HTC" table there is a field called "HTC_Name" which I entered to be a calculation field.  The calculation is "Companies::CompanyName". So on the "Specify Calculation" windows it shows "HTC_Name = Companies::CompanyName".  When I create a sample layout to see if it displays the names of the HTCs it displays it.  On the "Specify Calculation" window, if I click on the "Storage Options" button, under "Indexing" the checkmark is on the selection "Do not store calculation results -- recalculate when needed".  If I try to uncheck it, I get a message that says "The calculation "HTC_Name" cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage."  If i create a value list called "HTC Company Names" and choose under "Values", "Use values from field:" and click on the button "Specify Field", I get another window for making my selection of "Use Values From First Field", I choose "HTC".  Then I choose "HTC_Name".  Then I choose the option "Include all values".  Then I click on "OK".  Then I get a pop-up box that says "This value list will not work because the field "HTC_Name" cannot be indexed.  Proceed anyway?"  I click on "OK".  Then when I add the value list on the layout, nothing displays.  What is the correct way to do this?  I tried different scenarios/samples and nothing works.

        • 1. Re: How to create a value list with a search criteria?
          philmodjunk

          You have two tables: Companies and HTC  (We'll just focus on those two for now).

          How are they related to each other?

          If you have a field in Companies that identifies which records are HTC, Which are Insurance, and so forth, you can use that field in a relationship to set up a conditional value list that only lists companies of a selected type. The first step is to set up a relationship that works.

          BTW, linking records by company name is not a good idea. Company names are not always unique, they change from time to time and it can be tricky to correct typographical errors in the company name after you have created a related record. Using a company ID field defined in Comapnies as an auto-entered serial number avoids those issues.

          Here are some links on conditional value lists that may be helpful, once you have your relationships figured out:

          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: How to create a value list with a search criteria?
            j_rameses

            Hello again PhilModJunk.

            In the table "Companies" there is a unique key field called "__pk_CompanyID".

            In the table "HTC" there is a corresponding key field called "_fk_CompanyID".

            There is also a unique key field called "__pk_HTC_CompanyID".

            The "Companies" & "HTC" are joined by "__pk_CompanyID" & "_fk_CompanyID".

            Under "Edit Relationship" window, under "Companies", the selection is made for "Allow creation of records in this table via this relationship".

            Under the section for "HTC", there is no selection made.

            I will check now the links you provided.

            • 3. Re: How to create a value list with a search criteria?
              philmodjunk

              Ok, then you can add a new relationship that uses a "type" field to produce your company list:

              HTC::constHTC = CompaniesByType::CompanyType

              Define constHTC as a calculation field with the literal string: "HTC" in it. For each HTC record in Companies, Put HTC in the CompanyType field. CompaniesByType is a new occurrence of Companies you create by selecting Companies and then clicking the button with 2 green plus signs. Double click this new occurrence to bring up a dialog where you can rename the occurrence.

              • 4. Re: How to create a value list with a search criteria?
                j_rameses

                I am a bit confused.

                I forgot to mention that we have a layout called "HTC Companies", when data is entered into that layout some data goes to the "Companies" table and other data goes to the "HTC" table.  In the "Companies" table the following fields are entered there:

                Company Name, address, phone numbers, website, etc.  General info that all types of companies have.

                The other fields such as Department, Contact name, and other specific type of data goes to the "HTC" table.  On this layout when a new record is created, a unique ID is created, called the "__pk_HTC_CompanyID".  And on the "Companies" table the unique ID, "__pk_CompanyID" is created as well.

                Can you please clarify what you meant?

                Thank you.

                • 5. Re: How to create a value list with a search criteria?
                  philmodjunk

                  Your HTC companies layout, should be based on HTC not companies in order for a value list based on my suggested relationship to work.

                  The previous post describes the relationship you'd need so that a value list on a field defined in HTC can list only values from Companies that are of the "type" HTC.

                  This is a relationship you would add to the existing relationships that you already have as it's only purpose is to enable the conditional value list.

                  • 6. Re: How to create a value list with a search criteria?
                    j_rameses

                    Are you saying that I should create a field in the "Companies" table called "CompanyType"?

                    And then when a value is entered in the "HTC Layout" place a value to the "CompanyType" of "HTC"?

                    So when I do a search under the "Companies" table I look for a value of "HTC" in the field "CompanyType"?

                    • 7. Re: How to create a value list with a search criteria?
                      philmodjunk

                      In "Show Records From" in Layout setup..., does this layout list Companies or HTC?

                      Yes, you would label each company by type in a CompanyType field. You will need to add the field if you do not already have such a field defined.

                      No, you would not search companies under this field, this is to enable a conditional value list of companies to list only company data of a specific type, HTC in our example. (You could use such a field in searches, but that's not the point here.)

                      • 8. Re: How to create a value list with a search criteria?
                        j_rameses

                        It says, show records from "HTC".

                        I do have a field in table "Companies" called "CompanyType".

                         

                        • 9. Re: How to create a value list with a search criteria?
                          philmodjunk

                          Show records from HTC is good for this value list and the relationship I described. This allows you to set up a value list that lists values from "CompaniesByType", Include only related values starting from HTC.

                          This value list will then only list values from Companies that have a company type of HTC.

                          • 10. Re: How to create a value list with a search criteria?
                            j_rameses

                            Is there a way to automatically add the value "HTC" to the field "CompanyType" in the "Companies" table when a new record is created in the "HTC Company" layout?  This way the user does not have to put a checkmark or type it in, in the field.  Because we have a main company layout where there are buttons for different types of companies.  Depending on which button they click that takes them to a specific layout to the corresponding button.  So if they click on the "HTC" button it takes them to the "HTC Company" layout.  Thats where some of the information goes to the "Companies" table and the "HTC" table.  I would like for the value to automatically be entered into the "CompanyType" field.

                            • 11. Re: How to create a value list with a search criteria?
                              philmodjunk

                              What are the steps you take to create a company and related HTC record for a new company of type HTC? (That will help me suggest a way to add the HTC automatically.)

                              • 12. Re: How to create a value list with a search criteria?
                                j_rameses

                                When they get to the "HTC Company" layout, they have to click on the menu button on top for "Records"--> "New Records".

                                When they enter data into this layout some goes to the "Companies" table and other goesto the "HTC" table.

                                Why they did this, I do not understand why.  I would of created a separate table for each type of copany.  But I have to work with what I have.

                                • 13. Re: How to create a value list with a search criteria?
                                  j_rameses

                                  Can I use a "Script Trigger" such as "OnRecordCommit" on the layout?  So when someone enters data on the layout, it loads up a script that automatically enters a value of "HTC" on the "CompanyType" field on the "Company" table?

                                  Would that place it on that current record if it is run from a script?

                                  • 14. Re: How to create a value list with a search criteria?
                                    philmodjunk

                                    That sounds like you don't have this relationship:

                                    Companies::__pk_CompanyID = HTC::_fk_CompanyID

                                    in FileMaker the Companies primary key (__pk) should be defined as an auto-entered serial number while HTC::_fk_CompanyID should just be a data field of matching data type. That, however, requires that a new record be created in Companies, before a new record is created in HTC. That can't be done by selecting "New Record" in the records menu unless a custom menu (requires FileMaker Advanced to set up), has been used to run a script in place of the normal new record command.

                                    Can you check the relationship in Manage | Database | Relationships and then click the field tab and check to see how Companies::__pk_CompanyID and HTC::_fk_CompanyID are defined? I don't want to suggest something here based on a false assumption of how your database works.

                                    "I would [not] have created a separate table for each type of company." 

                                    I've been wondering about why the separate tables. It's an approach that makes the mose sense if each type of company needs very different sets of fields on their dedicated layout. Then the Companies table stores the data for fields common to all types of companies and the separate "type" tables stores the data unique to each type of company. If you find that the data stored in the "type" tables are nearly the same for every type of company, then there is no need for separate tables.

                                    "But I have to work with what I have."

                                    True, but since you have full access to the structure of your database, you could, in theory, rationalize the structure of the tables without changing the look of the individual layouts and thus your users wouldn't see the difference even as you reduce the complication of the system's designed structure.

                                    1 2 3 Previous Next