I have an SQL Server table with a primary key and identity column called ‘ID’. This column has to remain the primary and identity as its used by my main application. When I add this table to Glide it doesnt detect this column. Therefore, I creating another column called ‘GlideID’ which Glide can use for its own unique row ID. However, because Glide is using ‘GlideID’ and my other application is using ‘ID’ you cannot edit a row which had been created on the other device (e.g GlideID would have unique value, ID would be blank, and vice versa). Any ideas?
I apologize if this is a basic question, but I haven’t used SQL Server as a data source in Glide before. When you say “Glide doesn’t detect this column,” do you mean that the column isn’t being imported at all, or that it’s being imported but not recognized as the ID column?
If it’s the latter, Glide doesn’t specifically use primary keys or unique keys; any valid column can be used in a relation or query. If it’s the former, it appears that Glide currently doesn’t automatically recognize SQL Server’s auto-incrementing identity columns as primary keys. This has been discussed by in the Glide community already without any solution. See SQL Server Identity Column.
The former, it’s not being imported at all.
In my example,
ID is my identity column.
GLIDEID is the unique column for the Glide app.
You can create new rows from within Glide app (the identity column and the GLIDEID update correctly). However, the problem is the identity column is not shown in the table and you cannot edit rows which were created outside the Glide app. When Glide resyncs the table, it shows the new row and cell values, but the GLIDEID value is shown as Null which doesn’t match the GLIDEID value in the app.
The other post had the same issue, but no fix. Another post mentioned, creating a sequencing column and updating it outside Glide, but this is not feasible. Currently, this makes SQL Server as a datasource unworkable in the app, as really it can only work as a readonly app.
Would it help if you created your own ID column? When rows are added from Glide use the Unique Identifier and when added from outside glide a different unique Id generator.
My guess is if you had a column with unique ids you could figure the rest out.
The ID column is used as the unique identifier outside the app and cannot be changed to another column. The GLIDEID is used as the unique identifier just in Glide.
Ideally, you want the GLIDEID from Glide to sync its value to the SQL table if its null. Not sure if this is possible?
I am wondering if the issue related to GLIDEID column being NULL when synched back is related to datatype of the column in SQL server. What is the column’s datatype on the server and how are you populating it on the server? If it is a UNIQUEIDENTIFIER column, can you try to use a character column instead populate with a GUID string?
The datatype is varchar(4000). The GLIDEID is generated by Glide and syncs fine with the server when a new row is created in Glide, so I do not think its a datatype issue. The problem is when a new row is created outside Glide. Glide needs to detect the new row, create a new GLIDEID value and sync this back to the server.
Ah I see. In that case, perhaps you can populate the glide id column using a guid on the server when new rows are added there.
My application is an inventory management system and the ID column is used as part of the asset numbering, so the numerical ID indentity auto increment is critical. I have over 20 tables (with 1000s of rows) and all use a numerical autoincremented indentity, so moving to a GUID wouldnt work.
@raajkumars is not saying you need to touch the ID column. He’s just saying to add something to your external process to populate the GlideID column from Glide when adding rows within Glide, and populate it with your external process when a row is added outside of Glide.
In the same way you can add rows internally and externally, just fill that GlideID column both internally and externally. As long as it’s a unique value, such as a GUID, it shouldn’t matter where it’s gets filled from.
2 Likes
I appreciate that, if the application had a few tables and a few new row events this would be possible. But the external application is well established (code wise), so trying to find every new row event for every table to populate another column with a GUID wouldn’t be practical (cost and time).
1 Like