Hi there, I am using Google Cloud SQL for m...

Hi there,

I am using Google Cloud SQL for my app (actually transfer everything from google sheet to Google Cloud SQL) and reorganizing all the data. I have some compulsory things that have been asked for this database: I must use primary keys auto_increment for all my tables.

I have seen in the documentation some explanation about the different ways : https://help.appsheet.com/data/data-integrations/using-data-from-mysql

Everything is well explained but I can not use the RANDBETWEEN() function solution. Indeed, the most of my tables must have increasing IDs over time. Therefore, I imagined another solution:

Put this formula in the initial value: =MAX(table[id_column])+1 Make my id_column KEY HIDDEN REQUIRED in appsheet

This solution seams to be working, but I am asking myself if I missed something. Does anybody already tried this or has an idea about the troubles I might have using this solution?

Thanks a lot

0 4 1,598
4 REPLIES 4

Harry2
Participant V

@Benoit_Gramond Hi Benoit, it is actually not recommended to generate new key values based on the values of existing keys (for example, increment the current maximum key to compute the next key). If your app can work offline, this approach can lead to duplicate keys. For example, 2 different users using the app simultaneously while offline might have the same maximum key value. As a result, when they insert new rows into the app, these rows will have the same key values, which will lead to errors when these rows are synced to the database.

If you absolutely have to have an identity column, or an auto-increment key column in your database, you can work around this by using 2 different key columns. One column will be the actual identity column in the database. The other key column will be the key column in AppSheet. This way, you can mark the identity as not required by the app so that it can be left blank. Each time new rows are inserted into the database, the database will automatically generate new key values for the identity column. As for the AppSheet key column, you can use the UNIQUEID() expression to generate new unique key values for this column. To the database, key values in this column wonโ€™t count since itโ€™s not an identity column.

Hello,

I have the same use case in MYSQL.

When you say:
โ€œโ€ฆyou can work around this by using 2 different key columnsโ€,

Do you mean using two different key columns (.e. ID fields) in the MYSQL database?

Specifically:

  • One key column the original database Primary Key
  • And the other key column, a database field specifically for Appsheet

For example:
Car_ID (existing Primary Key)

Car_ID_Appsheet (newly created field for Appsheet UniqueID)

Using this approach:

  1. Will the Appsheet โ€˜Data: Set the value of columns in this rowโ€™ Action
    insert a new row to the database table, if the database Primary Key (i.e. Car_ID) is set to
    auto increment?

  2. Will the UNIQUEID() expression create a random value, for the Appsheet ID (i.e. Car_ID_Appsheet), that will be populated in both the database and in Appsheet?

  3. Related to the UNIQUEID() expression, does the

need to be added to the MYSQL database table, in the Car_ID_Appsheet column, prior to writing new rows to the MYSQL table through Appsheet?

Thanks,

Alex

@Harry this is why I love this app and this community indeed this is the best solution, it also solve my problem of users adding rows at the same time in my inventory app.

by the way, if anybody needs to add a column with uniqueid in a table that already had data, here is the SQL code to update this column after creating:

UPDATE table

SET column

= (

SELECT GROUP_CONCAT(SUBSTRING(โ€˜abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZโ€™ , 1+ FLOOR(RAND()*LENGTH(โ€˜abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZโ€™)) ,1) SEPARATOR โ€˜โ€™)

FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT ๐Ÿ˜Ž as dummy_tbl )

Top Labels in this Space