Changing the Show Column value via API

[beddows]

In Grist-electron, I'm trying to change the Show Column value of a Reference column. Using PATCH columns, I'm attempting to update the visibleCol field to a new value.

In order to find out what the correct value might be, I'm using GET columns to retrieve the current visibleCol integer value.

The problem I'm having is that changing the visibleCol value only changes the Show Column dropdown value. The column itself remains unchanged. Even refreshing the page shows the correct value in the dropdown with no effect on the table.

How is the column value changed? And how is the visibleCol value derived for each reference column?

[dsagal]

There is a bit more complicated, and requires two actions. One to update visibleCol (like you tried), which affects what's shown in the dropdown, and one to update the display formula for the column, since the values are shown using a hidden helper formula column. You can in fact send both of those actions in a single API call, but it needs to use an undocumented /apply endpoint.

All of this is reaching into the internals a bit, so there is risk it would get changed over time, but it's been stable.

For both the reference column you are changing, and for the visible column, you'll need their integer ID (colRef in /column API docs) and their string names (id in /column API docs). So you'll need: DOC_ID, TABLE_ID (the string identifier of the table containing the reference column to update), COL_REF (integer), COL_ID (string), VISIBLE_COL_REF (integer), VISIBLE_COL_ID (string).

POST /api/docs/DOC_ID/apply
[
  ["UpdateRecord", "_grist_Tables_column", COL_REF, {"visibleCol": VISIBLE_COL_REF}],
  ["SetDisplayFormula", TABLE_ID, null, COL_REF, "$COL_ID.VISIBLE_COL_ID"]
]

For example, in the template in https://templates.getgrist.com/doc/afterschool-program, to change the Student column in the "Enrollments" table to show First_Name (instead of Full_Name), the body of the JSON POST would be this:

[
  ["UpdateRecord", "_grist_Tables_column", 39, {"visibleCol": 25}],
  ["SetDisplayFormula", "Enrollments", null, 39, "$Student.First_Name"]
]

[beddows]

Thanks @dsagal , I feel like I'm getting closer... Let me walk you through what I have.

1. When the tables are initially created, UserID is set to Show Column "Row ID". I can manually change it to Name or Email, no issues.

2. After running POST apply, Show Column is correctly set to "Name". But this is what UserID looks like below. The Name values don't appear until I double click on the cell. So the reference is correctly made. But even after selecting John or Jane, they don't "stick" as values, the cells remain empty.

3. In step 1, if I manually change Row ID to Email, then POST apply works fine and changes UserID and Show Column to Name. So the table works and POST apply works, they just don't work together programmatically...

To confirm:

COL_REF: the colRef of UserID (e.g.: 77)
VISIBLE_COL_REF: the colRef of Name (e.g.: 73)
TABLE_ID: "Users"
COL_ID: "UserID"
VISIBLE_COL_ID: "Name"

[dsagal]

Ah, try TABLE_ID of "Orders". It should be the table containing the reference column that needs to display differently. (I had a mistake on that in my example, correcting it now.)

[beddows]

Thanks, all working now! Love what you guys have created here, so much potential.