7 Replies Latest reply on Oct 19, 2012 10:31 AM by ErikWegweiser

    How do I write the proper calculation using "case" to link "city" and "region" contact information?

    kelpgriffin

      I am about to import 10,000 contacts into my FMP 11 database. What will be the proper calculation to automate a link between "city" and "region" such that if "city" = San Antonio, "region" = San Antonio? I need to write a case calculation that includes hundreds of cities (not just "San Antonio"), which will link to only nine regions. How would I adjust the following calculation to include hundreds more cities?:

       

      ("Region" Field Calculation): Case ( City1 = "San Antonio" ; "San Antonio" )

       

      Thanks!

        • 1. Re: How do I write the proper calculation using "case" to link "city" and "region" contact information?
          comment

          Instead of a calculation, create a table of cities and their regions, then define a relationship between this table and Contacts, matching on City.

          • 2. Re: How do I write the proper calculation using "case" to link "city" and "region" contact information?
            ErikWegweiser

            Hello, kelpgriffin:

             

            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

            • 3. Re: How do I write the proper calculation using "case" to link "city" and "region" contact information?
              kelpgriffin

              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!

              • 4. Re: How do I write the proper calculation using "case" to link "city" and "region" contact information?
                kelpgriffin

                "Load up the table with a record for each city

                and its region in the respective fields."

                 

                In order to do this, do I have to make the new table visible in Browse Mode?

                 

                Also, does my relationship look correct?  (See picture)?  Thank you again!Screen shot 2012-10-18 at 12.12.46 PM.png

                • 5. Re: How do I write the proper calculation using "case" to link "city" and "region" contact information?
                  ErikWegweiser

                  kelpgriffin,

                   

                  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.

                   

                   

                   

                  FileMaker Pro AdvancedScreenSnapz006.png

                  Your relationship with City=City as the predicate.

                   

                  FileMaker Pro AdvancedScreenSnapz007.png

                  Relationship with City=City AND State=State as predicates, since the same city name may exist in multiple states.

                  1 of 1 people found this helpful
                  • 7. Re: How do I write the proper calculation using "case" to link "city" and "region" contact information?
                    ErikWegweiser

                    OK, Michael:

                     

                    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.).

                     

                    FileMaker Pro AdvancedScreenSnapz001.png    

                     

                    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.

                     

                    FileMaker Pro AdvancedScreenSnapz002.png

                     

                    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.

                    1 of 1 people found this helpful