Im downloading csv transactions from my bank. the only way to identify which account is to parse a ref field.
so im using this function to return the account: $Transaction_ID[9:15]
I have another table called Accounts that has the same text/number that im trying to use as a reference. but it doesnt link, it just shows as a red cell.
Let’s say the column with $Transaction_ID[9:15] is called calculated_ref: in the properties of calculated_ref columns, define its type as “Reference”, then manually select “Accounts” table, then “Show column” on Account’s column you’d like to be shown in the main table.
OK, sorry: I just forgot that the Reference column must be seen as an integer referring to the id of referred table. So here is your CalcAccount column:
Accounts.lookupOne(Account_Number=$Ref[9:15])
Sample here (with a supplementary Account Name column in Transactions to show that the reference is working).
awesome thanks, so i guess the part i didnt understand… actually i still dont understand. I get the lookup part but why could i not use the parsed text as a lookup?
Perhaps you’ll see it better if you try replacing Accounts.lookupOne(Account_Number=$Ref[9:15]) by Accounts.lookupOne(Account_Number=$Ref[9:15]).id: you’ll see that the result is exactly the same. Another way to understand it would be to download the grist file, and to open it with sqlite3 or any sqlite3 viewer: you’ll see that, in the Transactions table, the CalcAccount column has the value of corresponding record’s id in the Accounts table.
I understand that a reference field returns the entire record (row) for the matching item. I guess it seems like grist should allow me to link via the formula $ref[9:15] directly vs having to do a lookup. Okay its working and i think i can remember to do that again the next time i need. it… Thanks
I’d distinguish between internal representation and Grist’s magic: internally, the reference field is what, in database world, is called a “foreign key”, referring to the “primary key” (id) of referred table. Having $Reference_cell return the entire record is Grist’s magic, and one of the many points that make it so awesome.