Automatic classification transactions seems a nightmare

[rperoni]

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!

[TheTimeSavingCo]

You should be able to do this with linked fields

Try:
1. Change your primary field in "Categories" to be "Histórico" instead of "ID"
2. In "Transactions", create a linked field to "Categories"
3. In "Transactions", create a lookup field from the linked field from step 2, to display the "Categoria" value from "Categories"

Now, in "Transactions" when you paste a value into the linked field you created in step 2 above, it'll automatically get linked to the appropriate record in "Historico", and this will cause the lookup field from step 3 to populate