Searching for text in field name within formula ie. 2019, 2018, 2017 in field name

There is no equivalent function but you can also do something like this,


Case(Year([My Date Field]),

2018, [Rate 2018],

2019, [Rate 2018],

2030, [Rate 2018])


But that is not really a Quick Base way of doing things. There are better ways with having a table and then a look up field. Perhaps if you explain what you were trying to accomplish we can suggest a table structure that would do that more elegantly.

Maintaining history of years prior while allowing the fluctuation of what field is used in the calculation.  

In this circumstance it is important to keep historical data but the calculation should always be on the current year.  I have other circumstances that are similar where historical data is also important.

So, a typical Quick base methodology would be to have a table of Years where the key field is Year, like 2019.  

Then make a relationship down to the details table based on say a formula field like

Year ([invoice Date])

and lookup an fields from the Years Table that that you need, down to the detail records.

So what your suggesting is a separate table for this information that ties to each year?

How could i separate that by each record would it be more like...

"Record Name", "Process Information", "Year it ties to" as 3 separate fields?  then wouldn't the unique ID end up becoming just as cumbersome?

I created a nested if statement to include the variances which ended up quite astronomical just to include 3-4 years of data.  Was hoping for a way to keep it searching so that anyone in the future could add a new field for the new year and it would just automatically populate the data correctly.


Would it work to have a case within the brackets...for instance "["&Year(today())&"]"

This would involve something similar to the "indirect" formula within excel

Either you would need to describe in more detail what you are trying to accomplish or else we would need to book a one on one session to get this working.

ie please describe the business issue that you are trying to solve for without going into how you would do this in Excel.  Like say in works what you want to have happen automatically.