How to import data when there are related tables?

[RogerioPenna]

I have this data on a database, that I would like to have inside Grist.

There is a daily table for a Machines Diary. Each record has a RECORD ID with a random code, like 6397051734be4

Each machine diary has some info for that day PLUS 0 or more Ocurrences (in that day) and 0 or more Cargos on that day. Each of these related records has it’s own Record ID and also a Foreign Key relating to the Machine Diary table record.

So for example, on 8th December, the Diary of Machine BMS-01, had record id 6397051734be4. It had 3 cargoes, each of those cargoes refering to Foreign Key 6397051734be4… and 7 Ocurrences, each of those refering to Foreign Key 6397051734be4.

How to I import these tables, MAINTAINING that structure, to Grist?

Should I import the tables with all the data, including Record IDs and then tell Grist that X column is Date, Y column is Text, and Z column is a REFERENCE from the other table?

How does that work? Because Grist doesn´t let you set a column as KEY. When setting a reference you just say the table and tells what column you want it to SHOW, which probably means GRIST has an internal system of KEYS that is not shown…

[RogerioPenna]

Ok, as I thought GRIST has a unique ID for each row, that can even be shown with a FORMULA.

However, the row ID can be shown but not changed. Thus, when importing a table which already has row IDs, I am not sure how to relate the tables

I mean, yes, at principle, I import two tables. One has the IDs and the other the Foreign Keys relating to that ID column. And the moment I set the FK column as REFERENCE, GRIST finds it correctly and makes the relation.

But how does that relation continues afterward, if the real ID is another, automatic one?

Can I delete the original ID column in one table and the FK column in the second table, because Grist already matched the tables by the original, hidden Grist ID?

[Dmitry_Sagalovskiy]

I think you understand correctly. In Grist, each table has a hidden column id (can be used in a formula as $id), which identifies a record, and is stored in other Reference columns which point at this record.

For a situation like yours, you may also have other unique idenfiers, like RECORD ID in Machine Diary. And in your database, another table, let’s say Cargoes, has a foreign key into the Machine Diary, let’s say DIARY RECORD ID.

When setting up this data in Grist, you would convert the column DIARY RECORD ID to a “Reference” type, pointing to Machine Diary table. During the conversion, Grist will look up the RECORD ID, and replace the actual values with the numeric id of the corresponding rows. It’ll still show RECORD ID, but that’s no longer the real foreign key, only a display value. The Reference column (which I’d now rename from DIARY RECORD ID to DIARY RECORD) will store the id of rows in the Machine Diary table, and will show essentially the result of the formula $DIARY_RECORD.RECORD_ID.

As you say, at this point, if you delete the RECORD ID column, it won’t affect the relationships between records. That’s absolutely correct!

If you want to import more data into the same table though, you should keep the RECORD ID column, and keep it as the display value for the DIARY RECORD column. When importing into this table, you would first select the destination table:

Then you’ll be able to specify in the import dialog how to map input columns into Grist columns. In particular, you would map your input DIARY RECORD ID column to the Grist’s DIARY RECORD Reference column:

It will show up as pink (as if invalid) in the import preview (a known bug with preview)

but when the import is completed, Grist will use the DIARY RECORD ID values to find actual matching RECORD ID, translate them to the corresponding id's of rows, and import correctly.

[RogerioPenna]

Sorry Dmitry, I had already discovered all that in the meantime (and found it awesome how Grist does it automatically) between my ramblings and your answer.

BUT, since I didn´t say anything, it’s interesting you answered here because it’s info not easily found on the help (or at all) and will help people on this forum on the future.