Display a specific field value based on a dropdown list field

Question

we are working here with filemaker and i'm getting arround to make it work for my need.

Here are the field i have Field name "For this year" is a drop down list with a list of 10 year manually updated when needed Every year have a field name "Price 2020", "Price 2021", Price 2022" etc.

I want to moved the value of the field "Price 2020" when i select the option "2020" in the drop down menu of the field "For this year" and the "Price 2021" when i select the option "2021", and etc. This value need to be copied in another field named "Price for the selected year"

If not too much, i also want to copy the field of the "Price (year - 1)" in a field named "Price for the year before"

I tried looking arround the web, but i can't find what to ask on google to find some case that look like what i want to do.

Answer 1

Your description is difficult to follow, but this part caught my eye:

Every year have a field name "Price 2020", "Price 2021", Price 2022" etc.

That's not a good structure. You should have a table of Prices, where each price is an individual record with fields for (at minimum) Year and Price. Then, when you select a year in your current table (not sure of what), you can lookup the price for the selected year using a relationship matching on year.