7 Replies Latest reply on Apr 12, 2012 8:08 PM by mark_scott

    How to define a lookup with multiple fields?

    sai.htet

      How do I define the relationship in "manage database" and write the correct scipt for lookup function if I have two tables like.

       

      Table1

      RAW DATA
      futbal
      fotbol

      rugby

       

      Table2

       

      Raw Data1Raw Data2Correct Data
      futbalfotbalfootball
      rugbyruggbyrugby
      frisbefrizbefrisbee

       

      I want the script to read the table1, field and lookup the "Correct Data", then replace the rawdata in table1 with the correct data. I don't have a problem with the look up function if there is only 1 raw data field in table2, but when I add more raw data fields, i cant get look up function to work correctly.

        • 1. Re: How to define a lookup with multiple fields?
          MarcDolley

          I think you may not understand how lookups work. They look up data from one field based on matching data in another. In your example, you would remove the field Raw Data2 altogether so you just had Raw Data1 and Correct Data. You would then have multiple records such as:

           

          Raw Data1     Correct Data

          futbal               Football

          fotbal               Football

           

          and so on.

           

          Your lookup would then work correctly. I'm sure others will offer more complex scripted solutions, but I wanted to give you an example of how a lookup would work.

           

          Regards

          Marc

          • 2. Re: How to define a lookup with multiple fields?
            sai.htet

            The problem with only using 2 fields is there will be repetitious data in the "Correct Data" field, like in your example. Can you suggest another way other than using "lookup"?

            • 3. Re: How to define a lookup with multiple fields?
              Stephen Huston

              You can auto-enter data via calculation, and write the caculation as a CASE statement so it tests the field contents (based on test you will need to calrify) to determin which field to use as the source.

              • 4. Re: How to define a lookup with multiple fields?
                dapple

                Tell me why repetitious data in the Correct Data field is a problem, please.  Maybe I am using Lookups to restrictively. However, I am intrigued by your response, Stephen, and will check on that right now. I was fm7 adept, took some time off and coming back to it there are so many changes! You seem to be an EXPERT!  Can I friend you?

                • 5. Re: How to define a lookup with multiple fields?
                  Stephen Huston

                  I'm not clear what you mean by "repetitious data in the Correct Data field is a problem", and it wan't mentioned in the original post.

                   

                  I've been using FileMaker since the un-numbered version 1 before Claris bought it. Any expertise I have is just from daily use for a long time, and it's my full-time job as an in-house FM Developer for a graphics company.

                   

                  If you think 7 to 11 is a big change, we are all trying to get up-to-speed on 12 suddenly.

                   

                  I believe this forum has some kind of "follow" function which allows you to be notified of postings by people you have flagged. I just watch changing posts to see what's being discussed and jump in if I feel my thoughts may contribute. Been doing that a lot more since the release of 12 just to stay current on issues being reported.

                   

                  Welcome back!

                  • 6. Re: How to define a lookup with multiple fields?
                    sai.htet

                    "Repetitious data in the correct Data Field" is a problem, because i think it makes updating the table harder.

                    Raw Data1       Correct Data

                    futbal                 Football

                    fotbal                 Football

                    With this example, imagine if there was 1000 different "raw data" that needs to be correctly translated to "Football" you will have 1000 different records, and what if later I want to change it from football to something else.

                    • 7. Re: How to define a lookup with multiple fields?
                      mark_scott

                      Raw Data1       Correct Data

                      futbal                 Football

                      fotbal                 Football

                       

                      This is a correct, well normalized, relation.  Raw Data1 is your candidate key; only unique values in that field.  The only dependency is between the two columns (obviously).  I'd go with this.  As soon as you start finding yourself defining fields as "SuchAndSuch1," "SuchAndSuch2," etc., as in the example in the original post with Raw Data1 and Raw Data2, odds are you've got a non-normalized design (a 1st normal form violation in this case) and problems down the road.

                       

                      Mark