How to obtain the id of referenced records inside a custom widget?

[Emanuele_Gissi]

I am developing a custom widget with a button that should duplicate a record. The record contains some reference fields and some reference list fields.

The record returned by grist.onRecord(record) {} is something like:

{
  "id": 14,
  "Name": "Andrea",
  "City": "Mi",
  "CalcId": 14,
  "Books": [
    "Pride and prejudice",
    "Animal farm"
  ],
  "Books2": [
    "La vita nuova"
  ],
  "Ref": "Convivio",
  "A": 14,
  "Addresses_City": [
    "Milano",
    "Roma"
  ]
}

Note that Books, Books2, Ref, and Address_City are all references to other records. The value is the displayed value, not the referred record id.

To duplicate the record I need to supply the record ids, not their displayed values.

How to obtain the referred record ids? eg. [1, 5, 9] for a reference list or 9 for a reference?

PS
IMHO, this question has been asked several times, but never fully answered for the Javascript case:

How to Get the Raw Row ID of a Reference Column in Custom Widget?

I’m creating a custom widget. I have a table ICP with a reference column Industry that points to another table Industry. The Industry column is configured to display the human-readable Industry_Name instead of the raw rowID. However, I need to access the raw rowID of the related Industry record in my custom widget. Here’s the schema for reference: @grist.UserTable class ICP: ICP_name = grist.Text() Industry = grist.Reference('Industry') # Configured to show Industry_Name for readability …

How to Copy Reference List using Action Button

Hi, I am attempting to use the Action Button to copy a record. Currently I can copy the following field types successfully, Standard fields using $column_name Reference fields using $column_name.id However, when attempting to copy a Reference List type field to a new record, i receive a the following errors, “Experiments”: $Experiments, # {‘rowIds’: [1], ‘tableId’: ‘Experiment’} , gives this error “Experiments”: $Experiments.id, # KeyError, gives this error $Experiments field …

[jarek]

Hi @Emanuele_Gissi

There are only 3 options here.

First is finding the element by name, as in How to Get the Raw Row ID of a Reference Column in Custom Widget? post. It assumes that the display column used for this Ref field is unique, and locating it by text makes sense.

Second option is to add a dedicated formula column that will show you the actual id of the Reference column (just a new formula column that gets the id from the first column).

Third option is to add a formula column with a following formula:

RECORD(rec)

This will expand all Ref columns and instead of the text you will receive an object that has rowId in it. Here is help page for the RECORD function Function reference - Grist Help Center

[Emanuele_Gissi]

Thank you @jarek

I am going to explore the 2nd and 3rd solutions.

I exclude the first one because the label is not necessarily unique.

IMHO, as this seems a frequent use case, maybe we should think to a direct access to the raw record?

Thank you again for your help and great work.

[Emanuele_Gissi]

Do you think it might be worth opening an issue on GitHub?

[paul-grist]

I think so. The onRecord handler already accepts some options, and it would be reasonable for it to be configurable to be more helpful here.

[Rogerio_Penna]

Really happy Emanuele opened this thread, because I was really needing it.

I was able to read from child tables, only the records connected to the current record of the table I was connected to.

HOWEVER…

I noticed that changing values in the child table doesn´t reflect on the Custom Widget UNTIL I reload or change Grist views and return.

I tried listening to changes in the child table, but failed. The JS code only listens to changes in the main table it’s connected to. Any idea on how to solve this?

[Emanuele_Gissi]

@paul-grist

onRecord handler

Okay, it seems like a low hanging fruit, just adding some options to FetchSelectedOptions would seemingly be enough (disclaimer: as much as I understand it). I created a placeholder issue on GitHub:

grist-plugin-api should allow more control over records and tables

Feel free to add/modify/discard it.