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

    Data Lookup and Concatenate

    fmpuser0

      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
          philmodjunk

          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.