5 Replies Latest reply on Aug 13, 2015 11:17 AM by philmodjunk

    Lookup data randomly not autofilling

    marketg

      Title

      Lookup data randomly not autofilling

      Post

      We moved from FM 10 to FM 13 here back in January. Recently we just became aware of lookup fields randomly not filling in and can not figure out what the commonality is. Here is what we have in place.

      Invoice system file with a portal to an Invoice Items file. When starting the Invoice, it does a look up from a Customer file we have (and that works just fine). One you start filling in the Invoice Items in the portal, the Invoice Items file has various look up fields to auto fill in Phone Number, Company Name, Customer Name... and some others. Randomly for no reason we can figure out, some Invoice Items Records are missing all the related Look Up field data for that particular Invoice. I'd say it happens 1 in every 10 if I had to give a number of times it happens.

      Any thoughts as to why this is occurring?

      Thanks

        • 1. Re: Lookup data randomly not autofilling
          philmodjunk

          There's more than one way to look up that data. Which are you using? Looked up Value or Calculated Value or Script?

          If a looked up value setting, can you pull up some of these records on a a layout based on Invoice Items and do a relookup? Does that update the fields correctly?

          Such tests might be done most safely on a back up copy of your database in order to avoid mass changes to your records that you can't undo.

          • 2. Re: Lookup data randomly not autofilling
            marketg

            PhilModJunk,

            We are using the Looked Up Value feature. I did go check doing a "relookup" and that did fill in the missing data. Does that help possibly know a solution?  :)

            Thanks PhilModJunk

            • 3. Re: Lookup data randomly not autofilling
              philmodjunk

              It narrows the range of possibilities.

              Is it possible that this is what I call a "two stage" look up?

              In other words, does the table look up some data that then matches to a record in another table and then more data is looked up?

              I know of cases where this has failed due to FileMaker trying to look up data from the second table before the data has been looked up from the first table and thus fails to match to data from the second table. This is something that I've seen work in an older version and fail when the DB version was upgraded.

              • 4. Re: Lookup data randomly not autofilling
                marketg

                I don't believe this is a two stage look up as I understand it.

                Customer service creates an invoice record by entering a phone number. The phone number is the look up tie to the customer file records and that information is pulled through to the invoice. Then they finish filling out random data on the invoice before starting to fill in purchase line items. When ready they click in the Product Code field in the portal for Invoice Line Items to create a line item (when this happens it creates a record in the Invoice Items database with that Invoice Number which does the lookup and pulls the related data look up data through to the Invoice Line Items database).

                I hope that helps with seeing how we have things set up.

                I can run more test if you can point me in some testing directions.

                Thanks

                • 5. Re: Lookup data randomly not autofilling
                  philmodjunk

                  when this happens it creates a record in the Invoice Items database with that Invoice Number which does the lookup and pulls the related data look up data through to the Invoice Line Items database

                  Pulls the data through from what table? a Customer table? or are you referring to a look up that pulls data such as a unit price from a products table?

                  Phone numbers aren't ideal for looking up a customer as the phone number can change and I've even seen cases where the same phone number appeared twice in a contacts table--either due to a data entry error or because contact 1 gave up a phone number and then a phone company assigned this number to a different person or company.