Can you parse a text field for an account number and then create a reference on that forumla?

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.

Any way to do this?

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.

thats exactly what ive tried. here is a sample doc:
test doc

CalcAccount is my formula that parses out from Ref. I want column to be a Reference to the Account.Account Number column.

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

would you mind opening up access on that file?

Sorry… I’ve just opened it (with r/w access).

1 Like

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.

1 Like