1 Reply Latest reply on Jul 20, 2010 9:15 AM by philmodjunk

    Calculating/printing a field based on another field



      Calculating/printing a field based on another field


      Hello and thank you in advance for looking. I am using FMP 10 on OSX and I am a noob.

      I have a large database of addresses with with the State field as a text box. This is not set up as a value list.

      The states are all in the format (VA, MD, CA, etc.)

      I need to create a new field that looks at VA and prints Virginia.

      What would be the best way to acheive this? (all my attemps have failed)


        • 1. Re: Calculating/printing a field based on another field

          Your state abbreviation field should be set up with a value list--that will cut down on data-entry errors. The best approach for what you want doesn't use a calculation at all. Instead you should have a table of state names and their abbreviations that you link to your current table with a relationship using your state abbreviation field.

          1. Use Manage | Database | Tables to define a new table, States
          2. Click the fields tab and define two fields for this table, Abbr & StateName
          3. Click the relationships tab and drag from the state abbreviation field in your original table to the Abbr field in your new States table. This should create a line linking the two tables (we call them table occurrences) on the graph with an = sign as the relationship's operator.
          4. Now click OK to dismiss Manage | Database and find the new layout for states, change the view to Table and enter all the abbreviations and state names you need in this table.
          5. Now, on any layout that is based on your original table and you need to see the name of the state, place the StateName field from this new table on the layout.

          Note: Once you set up this States table, you can use the Abbr field as a source of values for the value list of two letter state abbreviations for the field that started your question.