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