Our Airtable base collects information about our program. Two of the tables track people and the programs that people are registered for. For more unchanging information--like name, email, or date of birth--we have fields on the people table. However, for information that can change from year to year--like city, job title, or phone number-- we collect it on the program registration table. In the past, we set up our base so the most recent records for a given person in the program registration table would update lookup fields in the people table, so the people table would display the most recent city, job title, phone number, etc.
This system has worked well for us, but we are running into an issue where if the most recent record in the program registration table contains blank fields that correspond with people table lookups, then those people table lookup fields will also be blank. We would like to figure out a solution where the most recent pieces of information are still being transferred to the people table, but if a given field is left blank, it does not override what is already present in the people table lookup.
Is there a way to put conditionality on our most recent city formula field that appears on the program registration table, for example, so that it would go from this, IF({Most Recent?}, City), to having another condition excluding empty fields, and when that happens, it would look at the 2nd most recent record, 3rd most recent record, etc until it finds a field that is filled for that value to pull into the people table?