7 Replies Latest reply on Jul 4, 2014 8:49 AM by philmodjunk

    Table  Lookup



      Table  Lookup


           I have a class registration table with 12000 records. When someone registers for a class, the class cost is pulled into the associated registration record by a lookup to the class table.


           Been working fine until I re-imported the complete registration table. The lookup no longer picks up the class cost until I do a minimal edit on an individual Class Id field on the reg table. The lookup then works fine for that one record.

           I don't want to do a record by record edit (like reenter one digit in the Class Id field) on 12000 records.

           I can also access the class cost field  from another field in a different table  linked by the Class Id field. But that won't work as a replacement solution, for other reasons.

           I have tried exporting the registration table, cutting and pasting the class Id column and reimporting.

           Interestingly, the individual records I had tweeked prior to exporting the table still worked when I reimported it. But nothing else does.

        • 1. Re: Table  Lookup

               Are you using an auto-enter calculation field option to look up this data?

               If so, here's a way to force all the fields to update in one batch operation: Updating values in auto-enter calc fields without using Replace Field Contents

          • 2. Re: Table  Lookup

                 Thanks for quick response.

                 I went through the procedure you referenced to update in one batch operation. Sadly. It didn't work.


                 I tried a couple of times, reimporting the data each time. Cross checking every step.

                  I think I understand the concept although I must be missing something



            • 3. Re: Table  Lookup

                   I did not respond to your initial question "auto-enter calculation field"

                   No, i was using a  number field. with indexed, lookup.



              • 4. Re: Table  Lookup

                     The method I suggested is only if you are using the auto-enter calculation. With a looked up value field option, there's a simpler method.

                     Show all records

                     Put the cursor in the field that serves as the match field of the relationship on which the looked up value is based

                     Select Relookup from the Records menu

                • 5. Re: Table  Lookup

                       And the need to do this kind of mass update suggests that maybe a look up method that physically copies data from one table to another is not the best design choice to make. There are ways to dynamically link to this data such that changes in the related table automatically update the values shown from the context of table that looks up this data.

                  • 6. Re: Table  Lookup

                         I refer you to my first ever Post about Total fields in multi record portals.

                         I solved that problem using the option we have just been discussing.


                         Bye the way. Your latest suggestion worked. For which many thanks.


                         It is clear I am woefully uneducated about how FM processes data. I am, used to static data storag  but then my last serious programming was in the 60's. How do you suggest I educate myself!!

                    • 7. Re: Table  Lookup

                           I refer you to my first ever Post about Total fields in multi record portals

                           If that is your reason for looking up this data and producing the resultant redundant copies of it, then this is not sufficient reason for this approach unless there are other details involved.

                           There are quite a few training resources available. Which is best depends on the needs and abilities of the person seeking training so you'll need to examine the info on each and decide, some are free such as this one from FileMaker: https://itunes.apple.com/us/book/filemaker-training-series/id787527886?mt=11

                           And a short bit of info germain to this topic is here: Auto Fill

                           It describes linking to related data in two ways: by copying the data over like you have done here and by dynamically linking to the data via the same relationship that you need to copy over the data.