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.
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
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?
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.
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.
Got it -Thanks for the simple walk through!