Is there a quick way to get the value of a cell in a Reference column, instead of its id?
Currently, I need to add a function each time to do a lookup in the reference table by id to get the value.
Would be good if it would return the value instead by default, or simply by adding .value (or similar) to it.
Even better would be to be able to return the value from another cell for that record with the same approach.
So for example, if my reference table Country (with list of countries) includes:
id
name
code
and my Contact table has a reference to it,
contact.country would return the id (current/default) contact.country.name would return country name (display name) contact.country.code would return country code
The value in a Reference column actually is a row ID. What you typically see when looking at the column is actually a special hidden column called something like gristHelper_Display with a simple formula like $country.name. We’ve considered an option to retrieve columns like that in the /records API but that doesn’t feel like a great solution for your problem.
What you can do is just make a regular column with the formula $country.name (and/or $country.code) and retrieve that column in the API. You can hide the column in all your widgets so it doesn’t clutter them and it will still be available in the API.
I didn’t see CSV endpoint at first, because I thought these options would be under “records” endpoint docs.
Then I have implemented JSON endpoint without checking if Reference List returns values. Because I checked by downloading table as CSV from browser and there were values, so I think same conversion holds for JSON payload as well.
Now I have problem of a Reference List returning row IDs as array. I will add another column just to make the Reference List converted to a joined string of values, so I can parse with current system, or I have to ditch JSON implementation and implement the thing from scratch with CSV endpoint. Not a big deal, but it could be easier.
Some feedback; This should be an option in JSON payload in records endpoint as well, in my opinion. If this won’t land, I think you should mention and communicate clearly all data format options (json, csv, xlsl, etc.) in “records” or similar endpoint documentation. Because it is the way to get records, and I go for it as it’s straightforward. I think me and other people as well won’t look around to see other formats available.
Nonetheless, thanks for a great tool, and keep up the good work!
I agree that would be a useful option. Adding a helper column is one workaround. Another is to use the hidden option of the /records endpoint (i.e. /records?hidden=1) which returns hidden helper fields, which already include a helper column (such as gristHelper_Display) that has the values which the UI displays. They will be in the format ["L", "value1", "value2"], where "L" is a code to say that it’s a list value (there are other codes for dates, references, etc.). Basically, by skipping the "L" element, you’ll get the list you want. The tricky part is that there is nothing to say which helper column has which values, in case you have multiple reference columns
