6 Replies Latest reply on Jul 28, 2016 3:00 PM by user19752

    Help with the lookup option

    pbartow

      We have a very old version of FMP.  Here goes.  It's version 6.  We are in the process of having it redesigned, but this question may relate to all versions.  I am not an FMP expert and did not create this database, but am the only person familiar with how it works.  There is an order number field and a purchase order field in a db called orders.  Both are repeating with 18 rows.  I know that there is a much better way but that will all be addressed in the future.Standard record or order would be:

      Order #Po #Part #Qty
      100016123A100
      100016123B200

      or

      Order #Po #Part #Qty
      100116124A1000
      100116124B2000

      Often 2 orders will be combined:

      Order #Po #
      Part #Qty
      100016123A100
      100116124A1000
      100016123B200
      100116124B2000

      So  I have another db call numbers which has 2 fields called order number and po number.  Every order number gets paired up with a po number.  Based on a lookup, if I enter 1000 in the order # field it will copy 16123 into the po number.  This works fine.  However I want to be able to enter say 16124 in the po number field and it will then copy the order number into the order # field.  I have tried over and over in a couple of different ways but am getting error messages.  Is there a way to do this?  Thank you.

        • 1. Re: Help with the lookup option
          beverly

          There is an article on the FM knowledge base (aka KB):

          So if you search for 7463 under "Find an answer", you get the article. Or you can search (as I did) for 'filemaker convert repeating to related'. And that may yield more resources from your favorite search engine.

           

          Study the how and why and get back to use if you need more assistance, pbarlow!

          beverly

          • 2. Re: Help with the lookup option
            philmodjunk

            I'm not sure that I followed your description fully so apologies if I am way off the mark, but one severe limitation to using repeating fields is that you can't enter a key value into one repetition and look up a corresponding value from a related table, then enter a different key value into the second repetition and look up that different corresponding value.

             

            Thus converting the repeating field to a related table so that you can use a portal and set up the needed look ups is a good idea. I'm sure that the KB article has all the info needed to do this, so I'll just comment that it's pretty easy to do because Import Records has a built in option for producing that new tale where each repetition has been split into a separate record.

            • 3. Re: Help with the lookup option
              user19752

              There is a way to do it in newer versions, but on FM6, there is no Lookup() function, no "replace existing value" option, etc.

               

              You can make another po#2 field for entering po# to lookup order#, put it befind po# field and make po# field un-enterable.

              • 5. Re: Help with the lookup option
                philmodjunk

                user19752 wrote:

                 

                There is a way to do it in newer versions, but on FM6, there is no Lookup() function, no "replace existing value" option, etc.

                 

                You can make another po#2 field for entering po# to lookup order#, put it befind po# field and make po# field un-enterable.

                The Looked up value feature has been around and in use since FileMaker 2.5. But trying to use a repeating field as a match field in a relationship keeps any reference to data in a related table (also a file in FMP 6) from matching values the way you'd need for look ups to work. (A repeating field works like a return separated list of values--if you have 3 different values in your repeating field, the relationship matches to any value in the related table that matches value 1, value 2 OR value 3...)

                • 6. Re: Help with the lookup option
                  user19752

                  Lookup on repeated field works well with both key and value fields, as OP wrote. His problem is recurring reference on two fields, this is not only on repeated field.

                   

                  Of course, changing repetition to related records is preferred way, but it is described "I know that there is a much better way but that will all be addressed in the future".

                  I'm not sure which is better that make change on FM6 and up version, or 1st up version, and make the change. The change need making another file on FM6, since it can have only one table in a file.