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