1 Reply Latest reply on Apr 20, 2017 7:31 AM by philmodjunk

    Data Lookup and Concatenate


      I am a new user of FMP.


      My database contains two tables:


      1. Trips
      2. Airports


      Trips contains the following field:


      Field name: Itinerary

      Field Contents: JFK - DEN - SEA


      Airports contains the following field:


      Field name: Code

      Field Contents: JFK, DEN, SEA, etc.


      Field name: City

      Field Contents: New York, Denver, Seattle, etc.


      How do I create a new field in Trips that translates JFK - DEN - SEA to New York - Denver - Seattle?


      I am using FMP 11.


      Thank you.

        • 1. Re: Data Lookup and Concatenate

          Don't put multiple stops in a single field. Replace itinerary with a related record of one record for each stop. You now can do a simple look up matching on a single code to get the name of the city. The list function can be combined with substitute function to produce a list of either codes or cities in your itinerary table if needed.