Bringing in data from another table automatically

Hello! As seen in the pic, as a basic example, I have a clients table and a tasks table. The name column in the tasks table is referenced to the name column of the clients table. When selecting the name of the client in the tasks table, I would like the email field in the tasks table to be automatically populated with the email address of the client is this possible?

Hi @Gainzy, yes that is possible! Change the Email column to be of type Text (it doesn’t need to be a reference - only one reference column needed). Then type into a cell in that column =$Name.Email (when asked to convert to a formula column, do so).

The column you are calling Name in Tasks has a reference to a Client. You are choosing to show their name, but you can access all information about the Client via this column. It might be clearer if you call the column Client, and then update the Email formula to =$Client.Email.

There’s also an option in the right side panel to include other columns from a reference automatically - see the “Add Reference Columns” section (Reference columns - Grist Help Center).

3 Likes

Thank you! Worked perfectly :slight_smile:

I spoke a little too soon. It works fine with using a new sheet, but when editing my existing Client document, I get the following problem - The email addresses that are being pulled in have the square quotes around them and also the occasional “u”. The source email addresses in the Client table do not have any square brackets. Has this issue been seen before? grist-email

Do you know what type the original email column is set to? Maybe it is not a simple Text column? If it isn’t something deliberate, you could try making it a Text column. Or, if deliberate, you could try setting the new email column to the same type as the column it is reading from. The column type affects how it is displayed. To my eyes what you are seeing looks like a list type, maybe a Choice List.

Here’s a quick summary of column types if it helps: Columns & types - Grist Help Center

If nothing works, try setting up an example document that shows your problem but doesn’t have any sensitive data in it, then share it publicly and we can take a look: Sharing a document - Grist Help Center

1 Like

Thank you Paul. I found the issue was related to the column I was referencing. The solution was to copy the column data into Sublime text, remove the [u’ ] using find/replace, remove the affected column, create a replacement column and pasting the data back into the new column.

It looks like your original column Client is a Reference List. The formula $Client.Email is ‘correct’ but Grist doesn’t understand that it’s supposed to format that column to look like a Reference List as well. It works for Reference columns because they don’t need special formatting. This is a bug with Reference Lists that we should fix, especially with the “Add Reference Columns” UI.

Change the formula of your email column to just $Client, then change the type of the column to Reference List, and choose ‘Email’ from the ‘Show Column’ dropdown. Then it should look right.

1 Like