14 Replies Latest reply on Aug 1, 2017 8:01 AM by philmodjunk

    Lookup not updating



      I have a table of rates, starting miles and ending miles and a lookup field that looks up the rate from the table.  I've recently updated the rates and mileage on the table, the field doesn't seem to be updating or only retrieves the updated rates for the first 3 rows.  What am I doing wrong? I just updated the rates.


      Thanks in advance

        • 1. Re: Lookup not updating

          Hard to say from here without more detail.


          But lookups physically copy data from the related lookup table into the table doing the lookup. Updating data in the look up table will not automatically modify data already copied into the other table. Only a record that is created after the change would automatically show the new value.This is generally why you would use a look up or auto-enter calculation to copy over data from such a table in the first place. Other methods would be used if you want this data to automatically update each time a corresponding change is made in the look up table.

          • 2. Re: Lookup not updating

            Lookups triggered by setting the key(s), also called match field(s), in relationship on which the lookup is based. 


            In your scenario, it's not clear if you have something like a TRIPS table that has start and end miles that does a lookup from the RATES table (or something similar). 



            If you can, just put one of the fields from the primary table that's in the relationship to the lookup table on the layout, then select that field and go to Records menu and select "Relookup Field Contents."  Then look for "Relookup Field Contents" in the Script Workspace. 


            Let us know if this works.

            1 of 1 people found this helpful
            • 3. Re: Lookup not updating

              What about using a caluclated field instead of a lookup. If you make the calculation unstored it will do this 'lookup' for you when the record is opened.


              Since my reading indicates that is what you want to do, use the calculation.

              • 4. Re: Lookup not updating

                You have to select  the match field and then select relookup from the records menu to get it lookup new values. or change data in the match field. otherwise the data not changed even though the data in the lookup table is changed.

                i.e. you sale tax rate changes part way through  the year the new rate on applies only to new sales, not old ones. and lookups are the way to go for this. but, if the data need to change in old record as the table is updated then lookups are not the best way. and what &gofmp15 said is better.

                1 of 1 people found this helpful
                • 5. Re: Lookup not updating

                  You could also make as simple script to do the relookup.

                  goto layout (that needs the new info)

                  show all records

                  goto match field



                  and use on layout exit script trigger to update your records.

                  • 6. Re: Lookup not updating

                    There are several problems with using lookup fields for more than a one time lookup.


                    Should existing data be changed, a particularly sensitive question with invoices when prices or taxes change.


                    What happens when the lookup file is changed but the fields in your parent file haven't been relooked up? If the data is supposed to be real time, this can be a problem. If so, then an unstored calculated field might be a better idea.


                    Then of course is the question of whether or not you need to lookup the data in the other file. If you have a relationship then any changes would be shown 'instantly' if you drag a related field onto your layout.

                    • 7. Re: Lookup not updating

                      Thanks for all the help here, let me explain the issue I'm encountering.


                      This is the table I have.  I updated the "RatePeMile" column and everything still calculates according to the the Start and End miles.  When I update the Start and End miles, only the first two rows calculate according to the rate of $12.34.  Any rows after the 2nd row will continue to calculate at $12.34,  For example: 301 miles will calculate at $12.34 rather than $9.51.



                      • 8. Re: Lookup not updating

                        You'll need to show us those calculations and tell us whether they are in calculation fields or number fields with an auto-enter calculation.

                        • 9. Re: Lookup not updating



                          The "cost" calculation field is (miles * milesamount)

                          The "miles" is a number field

                          The "milesamount" is a lookup field which looks up from the table mentioned.

                          • 10. Re: Lookup not updating

                            I see no "miles amount" value to look up from the table in your earlier reply.

                            • 11. Re: Lookup not updating

                              It looks up the "RatePerMile" from the table.


                              If I was to change all the Start and End values and leave the RatePerMile cost the same.  They calculate, but immediately when I change the RatePerMile cost, it would only calculate the first two rows correctly.


                              I have a few other tables in our database and noticed the same thing occurring.

                              • 12. Re: Lookup not updating

                                What I am looking for is the precise values and calculations used. The calculation you describe in reply #9 does not refer to the rate you say that it looks up. So what is the actual calculation that uses this looked up rate?


                                I prefer not to guess.

                                • 13. Re: Lookup not updating

                                  Hi philmodjunk, I apologize, I'm jumping in to this database trying to figure out how to update the table and calculate based on the miles.


                                  I'm not quite sure what the precise values and calculations are being used. It's a big mind boggling to not understand why it would only grab the correct ratepermile for the first two rows based on the start and end miles.


                                  I did find a script that updates the tables, but running the script did not update the rates either.

                                  • 14. Re: Lookup not updating

                                    Sorry that I did not get back to you sooner, this one got "pushed down" in my in box and I never seemed to quite get back to it--Mostly due to vacation and DEVCON.


                                    If you are still struggling with this, I think that someone will need to examine a copy of the actual file. That may mean getting a consultant to work with you on this or by posting a clone (empty copy) of your data here in the forum. You can use the advanced editor to make a reply to this discussion that includes and attached copy of your file.