9 Replies Latest reply on Dec 10, 2009 3:08 PM by philmodjunk

    Uncooperative Lookup Field in Subtotal Part



      Uncooperative Lookup Field in Subtotal Part


      I am using FileMaker Pro Advanced 9.0v3 on a MacPro using Mac OS X 10.5.8.


      This is a 22-year-old FMP database (600 records) which draws information via Lookup relationships from 7 other databases (600 records each for 6 of them, and 115 for the one I can no longer copy information from).


      My problem emerged when I recently converted this set of 8 files from FMP 6 to FMP 9 . . . all layouts continue to work reliably EXCEPT one where the Lookup relationship has stopped functioning (the other 6 Lookups work fine copying numerous field of data into individual records).


      This layout is intended to copy an amount (the number of beds needed by county) from a simple 4-field Lookup file based on a "County" field match into a Subtotal of records sorted by County (beds needed) which is then subtracted from a subtotal amount (beds existing) to produce a county total (beds are needed if positive, or there is a surplus if negative). Until the conversion, it worked fine, but now it will not copy the amount from the other database.


      When viewing the relationships, I have the main table plus 7 lookup tables, and the relationships all appear to be properly matched. I have tried deleting the non-functional Lookup table, resetting the relationship, and even changing it from a Lookup to a relational function, all without success. I have spent days of trying things, none of which have worked.


      I was the original creator of these databases, but my level of FileMaker Pro knowledge effectively stopped improving at FMP 6 because someone else was doing the database input for the past 15 years. Now, it has been transferred back to me, and I thought I was doing a good thing when I updated these files to FMP 9, which is true EXCEPT for this one important Lookup failure.


      This is NOT a shared or published database, and I am the exclusive user. I would be happy to attach a Zip-compressed version of these files to anyone who can assist me. Thank you.



        • 1. Re: Uncooperative Lookup Field in Subtotal Part

          Try turning indexing off and on for the two key fields (one in each table/file) that define the relationship between them--you may have a damaged index.


          If all else fails, you can recover both files and test the recovered copies (even if recover reports "no problems found") and see if the recovered copies work. If they do, test each file separately by opening it with the other file being the original unrecovered copy and see which file is actually damaged. Replace the damaged file with a back up copy if you can.

          • 2. Re: Uncooperative Lookup Field in Subtotal Part

            I turned "Indexing" off and back on for the key fields, but it did not good.


            I recovered both the master file and the lookup file, but to no avail.


            Data still can't be imported to the field in the subtotal part . . . any other ideas?



            • 3. Re: Uncooperative Lookup Field in Subtotal Part

              "Data still can't be imported to the field in the subtotal part . . . "

              That statement is a bit confusing. You can't import data into a layout part. You can import it into a table and then the layout can be used to display the data. Have you tried creating a table view layout for the table that is supposed to be looking up the data and checked there for the data? Perhaps there's a problem with your layout, found set and/or sort order that is preventing the layout from displaying the data.

              • 4. Re: Uncooperative Lookup Field in Subtotal Part

                I'm apparently missing a step, and don't adequately understand the table relationship . . . so, I will brush up on these relationships in an effort to accomplish what you described.


                Any interest in seeing the set of databases that I've compressed into a Zip file.



                • 5. Re: Uncooperative Lookup Field in Subtotal Part

                  Have you kept your converted files as the separate files you had to have in Filemaker 6 and just converted them?


                  If so, on the file where you are not seeing the data you want to have be looked up:


                  Enter layout mode and choose "New Layout".

                  Set it up as table view layout and add the field where you aren't getting the look up to happen.

                  Switch to browse mode, examine your records and see if the field is empty or holds a value. If it's empty, you have a problem with the look up. If not, it's probably a layout issue.


                  It might help to use Manage | Database to examine the relationship between your two tables (Files) and to check the field definition of the match fields used in the relationship as well as the defiinition of the problem field itself.


                  Another test you can do is to try adding a portal to the related file on your layout. If you specify the same table (table occurrence) you do for your lookup, you can use it to see if the relationship is working. If you can't see any records in your portal, you have a problem with the relationship. If you can, the trouble lies elsewhere.


                  You've used the term "lookup" so I've assumed that you've defined a "looked up value" option on the field you are describing. This option physically copies the data from a field in one table into a field in the other. If you are using a different method, please describe that method.

                  • 6. Re: Uncooperative Lookup Field in Subtotal Part

                    The converted files have been kept separate.


                    I've checked and rechecked the relationships, which all seem proper . . . likewise with the field definitions.


                    All of these things worked in FMP 6, but not after the conversion to FMP 9.


                    Any chance someone can look at it if I send you a compressed set of files?

                    • 7. Re: Uncooperative Lookup Field in Subtotal Part

                      In my previous post, I described a method for checking to see if the problem was that the data is not being looked up or if the report is failing to display the value.


                      Have you tried that check to see if the data is being looked up?

                      • 8. Re: Uncooperative Lookup Field in Subtotal Part

                        Yes, I did that additional layout with a table and regular, but to no avail.


                        I even modified the looked-up value specifications, and added the "If no exact match, then: use ______" feature (using the word "NO" instead of the underlined blank), and it returned NO in the field where I expected a lookup value to appear.


                        Have been working on this for two weeks and don't feel any closer . . . most frustrating.

                        • 9. Re: Uncooperative Lookup Field in Subtotal Part

                          OK, check your private messages for an email address you can send the files to. I'll take a quick look, but if it requires complex debugging, I'll take my "volunteer" hat off and offer my services as a consultant. (I doubt the issue will require that, but you never know.)