8 Replies Latest reply on Sep 8, 2014 1:02 PM by ramirezp6856

    Beginner Data Table Lookup Help


      I began using Filemaker last weekend and there seems to be a learning curve for me.


      I would like to convert a spreadsheet into a data table.


      With that data table I would like the user to enter a number e.g. year


      Then have the field automatically lookup a value then put that value in another field.


      For instance,


      Year [________] user would enter year


      APR [________] APR would automatically be generated upon user input.


      Thank you so much!

        • 1. Re: Beginner Data Table Lookup Help

          Welcome to FileMaker.


          There are a few ways to accomplish this task, depending on exactly what you're trying to achieve. In FileMaker terminology, a "lookup" refers specifically to the process of copying a value from a field in table X into a field in table Y based on a relationship between the two tables. In your example, this would mean when tableX::Year = tableY::Year, copy tableX::APR into tableY::APR.


          Now, that means you would need to have table X defined as a series of records with a Year field and an APR field. These would then copy over into your target table on user entry.


          Such a setup is not really the best from a database perspective, however, because you're keeping data in two places. If the APR value changes, you'll have to change it in all the records in table Y since they won't automatically update if you change the value in table X. This could be the correct model in cases where you want to keep a historical value, but often, it's not the right model. Instead, you would simply use the related field from table X on table Y's layout.


          Other possible methods for auto-entry include using an auto-enter calculation or simply a calculation field.


          If you would give us a little more information about exactly what you're trying to accomplish from a business rules perspective, we might be able to give more precise recommendations.



          1 of 1 people found this helpful
          • 2. Re: Beginner Data Table Lookup Help

            Yep, you were right. My project is historical dependent.


            Thank you for your prompt response!


            I do apologize, I meant CPI not APR.

            I'm generating a database consisting of historical objects.

            My client is curious of each object's adjusted CPI value.


            The learning curve does not seem so steep anymore!

            • 3. Re: Beginner Data Table Lookup Help

              I don’t know what “CPI" means, but that still doesn’t necessarily mean you need to copy the data from one table to another. If you need to know what the “CPI” value was in, say, 1965, that doesn’t mean you have to copy it into the current table in order to gain that benefit. It can be held in a table that says, “In 1965, the CPI was X” and then refer to the value by just telling the user that the year for the current object is 1965. This is basic relational database design, and is the proper way to design things.


              We need more information in order to make good recommendations. What is the “object” to which you are referring, and does this “CPI” value ever change from object to object?

              1 of 1 people found this helpful
              • 4. Re: Beginner Data Table Lookup Help

                Correct, CPI changes depending on the purchase year of the object.


                • Adjusted CPI Value = ( (CPI Purchase Year/ 2013 CPI) * Purchase Price )


                I went ahead and implented a basic relational database design as you suggested.


                TO: Object                                 TO: PurchaseYearCPI

                [ __pkPurchaseYear]                 [ _fkYear                    ]

                ...Attributes                                 _fkYear


                • 5. Re: Beginner Data Table Lookup Help

                  Does the CPI for a given year ever change?

                  • 6. Re: Beginner Data Table Lookup Help

                    No, not in my case. I am calculating CPI with respect to CPI of 2013.

                    • 7. Re: Beginner Data Table Lookup Help

                      In that case, you should have a table that contains the year and the adjusted CPI for that year. Then, use an auto-enter calculation to insert the adjusted price into the parent table record.

                      • 8. Re: Beginner Data Table Lookup Help

                        Alright great! I ended up taking that route, thank you for the suggestion.