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

Ask for Help
1

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?

2

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.

3

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.

4

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

5

would you mind opening up access on that file?

6

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

1 Like
7

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?

8

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.

9

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

10

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