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

    Table  Lookup

    DavidTory

      Title

      Table  Lookup

      Post

           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
          philmodjunk

               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
            DavidTory

                 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
              DavidTory

                   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
                philmodjunk

                     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
                  philmodjunk

                       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
                    DavidTory

                         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
                      philmodjunk

                           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.