Instead of a calculation, create a table of cities and their regions, then define a relationship between this table and Contacts, matching on City.
The first thing you'll need is a "lookup table," rather than simply a calculation, for that many cities. It <sounds> like you're new to this, so just in case...
Create a new table, "Cities," that contains at least two fields; one for "City" and the other for "Region." Load up the table with a record for each city
and its region in the respective fields.
Create a relationship from the City field in your Contacts table and the City field in a table occurrence of the "Cities" lookup table (e.g., "contact CITY," and
if you're familiar with the "anchor-buoy method," you'll know why I suggested this, but it is of less importance here).
Re-define your Region field in Contacts to be a lookup, based on this new relationship, so it copies the Region field from the lookup table when a City name match is found.
You may then import your contacts from the source data file and (most likely) select the "perform auto enter options" check box in one of the import dialogs (there are ways
to perform the "lookup" function after the import as well).
From this point on, any time the City field is modified in Contacts (including new contacts), it will copy the appropriate Region.
A quick answer. I'm sure there will be follow-up comments and/or questions. One item I want to address right away is the caveat that if you are dealing with multiple states, not just Texas,
this lookup will not work properly (due to Cities with the same name in different states). You would need to add a "State" field to the lookup table and your lookup
relationship (or the lookup table is based on something more unique, such as ZIP/postal code).
HTH -- Erik
Thanks so much for your help. I'll give it a shot--I am totally new to FMP, and have not done extensive work with field calculations or scripts in other programs either. It's all new, but these basic functions will save days or weeks of lost productivity. Thank you again!
1 of 1 people found this helpful
Close, but you'd be better off creating a relationship directly to your "Contact Management" table occurrence, rather than to "Contact_Self."
The new "Cities" table you've created probably already has its own new layout now. You can use that layout or create new one(s) based on the "City" table.
You may also 'hide' these layouts so they're not visible to users without layout design privileges. The relationoship and lookup will still work with those layouts hidden.
Your relationship with City=City as the predicate.
Relationship with City=City AND State=State as predicates, since the same city name may exist in multiple states.
Thanks again, Erik! I'm starting to love this program.
1 of 1 people found this helpful
Are you ready for this, then? -- This is how I'd start re-configuring the relationship graph, using the anchor-buoy ("squid") method. Note that every base table (CONTACT, NOTE, CITY) has its own table occurrence group (or TOG), "anchored" by the base table, in red. CITY and NOTE don't have much happening here, but when things start to be added to the graph, it will all start to make a lot of sense and be much easier to understand than a complex single TOG database. Some may consider this whole concept "advanced," but it is one that you should comprehend early and consider adopting as a personal standard (no, not everyone gloms onto it, preferring other methods, similar methods or less 'strict' hybrid approaches. It's up to you.).
If you try this, here's one important point: FileMaker "remembers" the first occurrence ("instance") of a base table in the graph when a table is first created. For example, when you created a new "CITY" base table, it added that base table to the relationship graph and created a new default layout for it. What I mean by "remembers" is that later, when you create and modify field definitions utilizing calculations, FileMaker will default to this "first" occurrence as the "context" of the calculation — something very important, but often an oversight by a developer, and can trip you up badly when that's the case and the default is not as expected. Making sure the "first" occurrence is also your base table "anchor" will reduce the risk of forgetting to check the context of a calculation.
In my graph, above, I've colored it red and left it alone. Then I created another occurrence of that CITY table and related it to CONTACT. That "NOTE" has a new relationship to "note CONTACT" is not relevant to any existing layouts, but may be useful later if, on the "NOTE" layout for example, you want to show the name of a NOTE's CONTACT from the NOTE perspective.
What you can't see, above, is that I also took the existing NOTE table occurrence, disconnected it from CONTACT to make it the anchor of its own TOG (now in red), because it was the first (and only) occurrence of the NOTE base table. Then I created a new occurrence of NOTE and re-connected that to CONTACT, being careful to specify the same predicates AND behaviors (in this case, the 'delete related' option). I then went to my CONTACT layout and re-established the "NOTE" portal and its fields to reference the new "contact NOTE" table occurrence instead. AND I similarly altered the "Add a note" script, that included a "go to field" step requiring re-pointing to the correct "contact NOTE" table occurrence.