I'm a new Airtable user and I'm trying to replicate some functionality I had in my previous spreadsheets integrated with Looker. I have two tables: Categories (with fields ID, Histórico, Categoria, subcategoria, and Centro de Custo) and Transactions (with fields ID, Data, and Histórico).
I'm trying to automate the creation of a new field in the Transactions table called "Categoria", which should work as follows: when the value in the Histórico field of a transaction matches a value in the Histórico field of the Categories table, the corresponding Categoria value should be returned.
I tried creating a formula field in the Transactions table with the following formula: =ARRAYJOIN(IF(FIND(Histórico, Histórico(from table Categories), Categorias(from table Categories))
However, it didn't work as expected.
Can anyone suggest a solution or a different approach to achieve this? I'm struggling with what seems to be a basic task and I'm close to giving up and going back to my confusing spreadsheets in Spreadsheets.
Thanks in advance for your help!