In order to set up such a calculation, it has to be an indexed field. (Only indexed fields can serve as value sources for a value list.) That requires that the field be defined in Accounts and that it only reference fields in the accounts table. (Calculations that reference fields from other records cannot be indexed.)
If you have a stored field in Accounts that identifies it as a "sales Rep" account, then you can use a calculation field such as:
If ( AccountType = "Sales Rep" ; AccountID ; "" )
This field could be set up to auto-enter such a value from contacts to give you a stored, indexed field for your calculation field.
There may be other options that work better, including using a filtering relationship for the value list instead of such a calculation field, but I don't have a complete picture of what you are attempting to set up here.
Presumably, you have this relationship:
Contacts::ContactID = Accounts::ContactID
Though other structures are possible. In which table are you defining the field which you want to format with this drop down? Will this be a global field used in a scripted find, a filter for a portal or some other purpose ?
Thanks for your help!
I'm trying to setup the "accounts" as a generic table so that it can store all of our types of accounts. For example, we have vendors that are also customers and Sales Reps. I'd rather not have 3 separate accounts under separate tables.
So.. I set up a field to relate to a table of types (Sales Rep, Customer, Vendor), and implemented a checkbox group with a value list.
What I have learned just this morning is that with the checkbox object, it actually stores the values in the field of the accounts table (i.e. "Sales Rep Vendor Customer).
So.. what I did is set up a calculation field that uses the "pattern" formula to determine if it contains the word "Sales Rep"; if it does, the the value of the calculated field is the accountname. Now my value list works as desired. I'm just not sure about referential integrity, etc.
Regarding globals; my backgroud is computer science. I haven't coded in 10 years (C++), but my gut tells me that globals are not the way to go unless you have to.
As a years ago Comp Sci grad myself, I understand your reluctance to use global fields or variables, but in FileMaker they are very, very useful problem solvers that often cannot be dispensed with. Using a set of global fields for a script to use to set up search criteria in a find is one such example.
Patterncount works and used to be your only option here. It can fail if two of your values are too similar. If one value is Apple and another Applesauce, for example, you have to put extra details in your calculation before it can discriminate between the two values.
Thus, I prefer to use the FilterValues functions as it makes for a cleaner expression while avoiding those problems:
Not IsEmpty ( Filtervalues ( CheckBoxField ; "Value" ) )