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.
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