6 Replies Latest reply on Dec 13, 2011 1:21 PM by ESing

    Auto Fill information from value table



      Auto Fill information from value table


      I believe this can be done somehow with a lookup but I can't quite figure it out?

      Tracking project investments made in the past and insert CPI for calculations later..., so the idea is you enter the year built and both the year and the related CPI (which are matched up in a value table) to show up in fields in the table.

      This is similar to being able to type in a zipcode and the state auto populating or something similar?

      Any help would be appreciated


        • 1. Re: Auto Fill information from value table

          you would need to have relationship set up for the lookup with year built as the key field.  Then define CPI as a lookup based on that relationship.

          • 2. Re: Auto Fill information from value table

            You'll need to decide whether your "lookup" should physically copy data from the lookup table into matching fields of the current table's record or just establish a link to the related record so that data in that record can be displayed on your layout.

            This thread spells out the details for both approaches: Auto Fill

            • 3. Re: Auto Fill information from value table

              OK, so I'm pretty new to FMP and thought I could follow along, but did not get the desired results.... So, a little more background - the Year and CPi info will never change so it doesn't matter if it's static or not. I think I may have too many things going on now, b/c I initially tried to use the lookup values, but that didn't work, now I am not sure how to refresh.


              I have tblInvestment with Investment::YearInvested and Investment::CPI and then I have tblCPI (value table) with Year and CPI as fields

              In relationships I connected the year fields, should I also connect the CPI fields?

              Could someone walk me through this?

              • 4. Re: Auto Fill information from value table

                With either method, the relationship is key.

                in tblCPI, does Year identify one unique record or are there multiple records with the same value for Year?

                I would seem you need this relationship if you want to enter a year and get the CPI value from the CPI table to appear:

                tblInvestments::YearInvested = tblCPI::Year

                Since these values would never change, I'd just add the tblCPI::CPI field directly to the tblInvestments based layout. When you select a year, the matching CPI value will automatically appear in this field.

                This assumes that there is only one record in tblCPI for each year you might choose to enter or select.

                • 5. Re: Auto Fill information from value table

                  If you have  Investment::YearInvested linked to tblCPI::Year, you should be able to just place tblCPI::CPI on your layout to view the CPI and you won't need Investment::CPI

                  If you would like it to populate, but be able to change it, you can define Investment::CPI as a lookup of tblCPI::CPI based on the above relationship.

                  • 6. Re: Auto Fill information from value table

                    Got it -Thanks for the simple walk through!