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
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.
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.
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?
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.
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"?
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.)
It says, show records from "HTC".
I do have a field in table "Companies" called "CompanyType".
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.
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.
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.)
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.
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?
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.