11 Replies Latest reply on Nov 28, 2016 2:42 PM by carojo

    Auto Enter Lookup issue with FMS

    bwoods

      I am using FM to develop an application for project costing.   The test environment is using FMS 15 running on IMAC with users having FMP 15 & FM go.  I have a problem where one of the auto enter lookup fields does not update when a client changes the key field, or creates a new record with a script that populates the key field.   This only occurs in one of the tables, there is another similarly configured table and this works fine.  The only difference I can see between the two tables is the one that works uses a primary table for the relationship and looked up field, and the one that does not work uses a copy of a primary table for the relationship and looked up field.   What is perplexing is that if I use the client (FMP Adv 14) on the IMAC server both tables update correctly, no problems.   If I download the application from the server and run it on a FMP client as standalone, it works fine. It is only when I use the client over the network that the problem occurs.   If I go to table view on the remote client and manually change the key field in the problem table the lookup field does not update, I also  checked the key field has the correct data in it for the relationship to work correctly.  If I do the same test which the client on the server machine the lookup field updates as it should, same result if do the test on a client with the downloaded application.

       

      Any suggestions, or hair transplant offers gratefully accepted.

        • 1. Re: Auto Enter Lookup issue with FMS
          carojo

          I'm experiencing a similar issue since upgrading to FileMaker 15. I have a script that creates child records from a master (Purchase Order) record and I'm finding descriptor fields defined as Lookup fields in the records created are not populating. The ID field is populating OK, but not the descriptor field that uses this ID to lookup from related table.

           

          The database is hosted on FM15 server and the issue has occurred randomly - twice (for different ID / Lookup fields) out of 22 occasions when the process has been applied since migrating to FM15 (from version 12) and so far not in testing on local FM15 client.

          • 2. Re: Auto Enter Lookup issue with FMS
            philmodjunk

            Have you checked your table of look up values for duplicated ID's? If there is more than one matching record, it might be that the look up attempted to look up a value from a different record than you expected.

             

            This is just one of a number of issues that you will need to pursue and rule out in order to figure out why the data is not looking up. Another thing to look at is what kind of data forms your key for this lookup? If it's text, very small invisible (or nearly so) variations in the text might keep the field from looking up a value.

             

            Sharing the portion of your relationship graph that controls this look up may also be helpful.

            • 3. Re: Auto Enter Lookup issue with FMS
              carojo

              Thanks for the prompt response Phil. Obviously I can add script steps to set the descriptor fields explicitly (or change field definition to auto-enter calc) but would like to get to the bottom of the issue as the same conditions apply in other contexts in my database.

               

              Have attached screen grabs - numbers refer to images

               

              Note that the foreign key fields in records created by the script are populating correctly (as evidenced by fixing the data using “Relookup Field Contents” on the foreign key fields) - but for some reason the lookup is not happening within the script.

               

              (1) All Primary key fields - including for the related tables (Accounts and Strain) are Text and set to auto-enter calc Get ( UUID ) with Field definition  set to Prohibit modification during data entry, validation Not Empty and Unique and user cannot override validation - so no duplicate values for primary key.

               

              (2) Relationship for lookup tables are straightforward.

               

              (3) Lookup fields on table (Mouse) based on these relationships seem OK.

               

              (4) With the foreign keys that link to the Account and Strain reference tables from the Mouse record these are also set to be lookup fields - from parent / litter records where mice are bred inside the facility. However in the cases of the mice records created from a Purchase Order there are no related parent / litter records to lookup from (i.e. the _kf is null for “MouseMother” and “Litter” where mice are created from Purchase Order Import script) so I don’t think this should be an issue.

               

              (5) Script creates new Mouse record in the context of Main “utility” table

               

              (6) Script then populates the foreign key fields successfully in new mouse record and changes are committed - any errors in these steps are captured to notify the user (also rollback if error by reverting record changes i.e. Mouse record not created if an error in any step).

               

              Cheers,

              Caroline

              • 4. Re: Auto Enter Lookup issue with FMS
                philmodjunk

                I see no screen grabs.

                • 5. Re: Auto Enter Lookup issue with FMS
                  wimdecorte

                  carojo wrote:

                   

                  Thanks for the prompt response Phil. Obviously I can add script steps to set the descriptor fields explicitly (or change field definition to auto-enter calc) but would like to get to the bottom of the issue as the same conditions apply in other contexts in my database.

                   

                  It's probably a matter of where you want to spend your time.  I prefer to not have my business logic set at the field definition level and set fields explicitly as part of the scripted workflow.  You already have a scripted workflow so that would make it easy.

                   

                  Troubleshooting the auto-enter issue could be a lengthy process since:

                  - it sounds to be intermittent

                  - you have no error trapping or handling mechanism in the auto-enter setup

                   

                  Given that, I would bite the bullet and get rid of the auto-enter calc.

                  1 of 1 people found this helpful
                  • 6. Re: Auto Enter Lookup issue with FMS
                    philmodjunk

                    The first question is why you need to copy over Names that are best kept in the related table. Should you ever need to change a name (people do change their names and sometimes mistakes are not found during input and have to be corrected after the fact), it's a major operation to update the redundant data in every table and record where it's found. This is all avoided if you don't copy over the data in the first place.

                     

                    Two things come to mind:

                    1. You can try re-indexing the the fields in your file(s) to see if that makes a difference. This may not correct the issue, but it's easy to do using advanced recover options.
                    2. I've seen cases where look ups fail when the key value is an auto-entered calculation. This seems due to the system trying to look up data in the look up before the key value has been assigned the value--but I am purely speculating from observation. The fix mentioned by David-to stop trying to auto-enter these names and let the script that assigns the key also assign the name would avoid the issue.
                    1 of 1 people found this helpful
                    • 7. Re: Auto Enter Lookup issue with FMS
                      carojo

                      Thanks Wim and Phil - looks like I'll add a few more script steps.

                       

                      Phil I understand the concept of denormalising data - and have mechanisms in place to update reference values for "active" records. The requirement to copy descriptor fields is down to setting values for archived records.

                      • 8. Re: Auto Enter Lookup issue with FMS
                        jthulin

                        So I also have a similar problem since upgrading to FMS/FMP/FMGo 15. I've been following all the responses and  understand the shortcomings of autoenter lookups, but for a variety of reasons I won't be able to get away from them completely. However, it seems to me the issue is with the 15 client apps (both FMP and FMGo). The lookups work just fine with version 14 client apps, as they have for years. They work inconsistently with version 15 client apps. We have contacted FileMaker support, and they don't seem to want to spend the time to figure this out, although they will make an attempt if we want to go with the paid support option that is not included in our licensing. Does anyone know enough about 15 that would give a clue as to why it doesn't process the lookups as in 14 and previous versions at least back to 12?

                        • 9. Re: Auto Enter Lookup issue with FMS
                          philmodjunk

                          I suggest that you click "home" here in the community and then open a product issue report to report what you are dealing with. This puts you in touch with FileMaker Tech Support, but without having to pay for the privilege.

                          • 10. Re: Auto Enter Lookup issue with FMS
                            jthulin

                            Thanks for the advice! I'll do that.

                            • 11. Re: Auto Enter Lookup issue with FMS
                              carojo

                              I too have found that the problem with lookups not working has only occurred since upgrading to FM15 client / server - the issue was never reported on the FM12 platform where it had been running for 2 years - it's a bit concerning!