Create constants in the Company table for each type you want to filter by, then, for each filtered drop-down create a self relationship to the Company table that includes the desired filter (Companies::CompanyID=CompaniesTypeA::CompanyID and Companies::CompanyType = CompaniesTypeA::TypeAConstant), then create your value lists based on the filtered table occurence (CompaniesTypeA::CompanyName)..
I hope im not sounding too much like an amateur but what do you mean by constants?
No worries - FileMaker doesn't really deal in constants - I'm used to other programming languages. My bad for using that terminology. A constant is simply a field with a constant, unchanging value. Let's say one of your types is "Vendor" - set up a new field called Companies:TypeVendor with an auto-enter value of "Vendor". The auto-enter value will be automatically assigned on new record creation. You will have to perform a Replace Field Contents to populate the field for all the records that existed prior to the field being created. Do this for each type you have, then create the relationship as described in the prior post.
Let me know if you have any further questions.
I think i kind of get it, would you happen to have a sample showing how this works? Sorry, i'm more of a visual hands on person. Thanks
Working up an example now... quick question. What is the base table for the layout that you wish to place the filtered company dropdowns? Is it the company table, or is at a different table?
It is a different table called Transactions
1 of 1 people found this helpful
On 26/02/2012, at 1:58 PM, MattLeach:
I have a value list labeled Companies which is set to "Use values from field: Companies::CompanyName".
There are multiple fields on a layout where the user would like the drop down to be a list of Companies based on their type, for example:
FieldA - drop down of only companies where the company type = typea
FieldB - drop down of only companies where the company type = typeb
Is this possible?
Yes, it's possible. But the existing value list will not be useful. You will need to add the option to use related values only and have a relationship between the table that you are on and companies. That is two relationships, for type A and B. Create two value lists for typeA and another for typeB. In each value list you'll select the appropriate relationship for the filter.
Here is a sample based on a real estate agent being related to a brokerage, preferred title company, preferred escrow company, and preferred home inspector. Only one companies table exists, and the value list for the filtered company drop-downs are based on relationships from a "constant" value in the Agents table to the type field in the companies table. Take a look and let me know if you have any other questions.
TestRealEstate.fp7.zip 8.9 K
I took a look at your example file and if i'm looking at it correctly, it looks like the constant you are referring to is basically a field where the user must select the company type in order for the list to populate based on the type?
Using your example, if i were looking for a title company i would need to set the constant to Title and then the drop down would list the companies?
No, in the example, I set the constant field when I created them. I just
left them on the layout so you could see (and so I could do a replace field
contents if needed). Those fields can be removed from the layout.
The user, at this point, doesn't need to do anything for the brokerage and
preferred vendor drop downs to populate with the corresponding type of
Alternatively, you could have a setup like you just mentioned, where the
user has a dropdown of a related company type, then another dropdown
populates with companies of this type. This possess some conceptual
problems and I would change the underlying framework.
More information about your solution would be needed to determine the best
route to take.
Do you need to provide many relationships per company type (ie multiple
I think i'm understanding it now and really hope i'm on the right track here, sorry for dragging this out if i'm off base:
A constant is setup for each company type, then a relationship is setup for each different company type based on it's relative constant?
Yep, exactly! :-)
Awesome!, thanks for your help!