Relationships and keys coming from a traditional DB perspective?

[Martin_E]

I am learning Grist by attempting to implement an Excel-based application with a substantial amount of VBA code, forms and about half a dozen tables. The tables are stored one per sheet. The VBA code implements a subset of SQL-like expressions to be able to select, filter, etc. For example, I can:

“SELECT field1, field2, field n FROM table_name WHERE some_field = some_value ORDERBY something”

The tables have primary keys used as foreign keys to setup the relational model.

All user interaction is through VBA forms. In other words, no direct editing or viewing of tables at all.

I should add that I was going to re-implement the application using Python/Django due to a need to be able to have multiple users, permissions, etc. The application has outlived what is reasonable using Excel. That’s when I learned about Grist. It seems like a potentially good path. Just trying to understand it at this point.

I imported all tables into Grist. The first discovery is that my “ID” PK columns don’t seem to have a way to be set to auto-increment and remain unique. I did find a couple of threads on the subject, this being one of them:

Auto-increment column

Answering my own question: I just converted a prototype database with 20 tables using UUID() as keys. I did the following. add a new temporary column, called “temp” convert primary key column to “data colum” if not already done add trigger formula UUID and tick the field “Apply on record changes” and choose field “temp” goto column temp and enter “=1” into the first row - this triggers a record change for all rows and hence an automative update of UUID delete column temp - nice feature in Gris…

A UUID can serve well as a PK, however, I already have a PK column in every table and FK references to these PK’s in various tables. Other than a tedious manual task, not sure how I would create a UUID based PK field on every table and then edit/modify/add the corresponding FK’s on the tables that need to reference them. While I understand UUID/GUID’s, there’s a simplicity to small sequential numbers when it comes to having to manually deal with references.

Do I need to export the table with UUID PK’s in to Excel, use tools there to mend the FK references and then re-import the data into Grist?

In the various examples I have seen, I don’t recall seeing traditional PK/FK auto-increment fields used at all. Is this the Grist philosophy? How should I think of this?

I am going through tutorials and have watched a few videos. I haven’t found a detailed treatment of how to think about Grist in traditional DBMS terms. I’m sure it’s out there.

Thanks.

[Rogerio_Penna]

All Grist tables have an automatic autoincrementing ID column, that is hidden.

You can create a column with a formula to show the ID. (“$id”)

here, excluded id 5 and reordered the rows alphabetically by the “text” column. The ids were kept

[Martin_E]

Thanks!

That is so much easier than the answers I’ve come across that use UUID(), Python code to add 1 to the largest number in a column or use an auxiliary table to keep track of min and max ID’s per table.

Now I need to come up with a way to deal with missing ID’s in the source data from the existing database. This happens due to row deletions in the original data.

Unless Grist has a way to do this, I think I can pre-process the data with Pandas to add throw-away records corresponding to the missing records, sort the tables by ID and then import into Grist.

I would have to then add the $id column to each table and then delete the throw-away records. I could probably make this simpler by adding a “keep” column to every table during preprocessing with True/False for keeping or deleting that row. My guess is that I could handle the automatic deletion of those rows within Grist with Python before creating relationships between tables using the $id’s as primary keys.

Is there a more appropriate way to do this in Grist?

It would be fantastic if a feature was added to treat a column as the $id column while, at the same time, skipping missing rows (rather than having $id number the rows sequentially, which would destroy the relations from table-to-table).

Thanks again.

[Rogerio_Penna]

I already imported several tables related to each other into Grist. Grist usually discovers the relations by itself.