Solved: MYSQL Insert Primary Key with Add Data Action

Alex3 · 08-21-2020 07:46 PM

Hello,

I am trying to insert use an action to insert a row into a MYSQL database table. In my specific use case, I have to auto increment the existing MYSQL primary key with the action. I think the topic in the Appsheet community link below indicates that a possible workaround is to use 2 different key columns.

Do this 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 use in Appsheet as the Appsheet UniqueID)

Using this approach:

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?
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?

Thanks,

Alex

Steve

If you haven’t already seen this:

If it conflicts with my suggestions, go with the doc.

You’d need to attach an action to the form view to perform a navigate to another view in such a way as to trigger a sync.

View solution in original post

Steve

Why?

Alex3

Hello Steve,

I read your article on serial numbers as I was trying to implement the database update.

Our database is already fairly large and has existing foreign key relationships (and data) that reference the database table that we want to update through Appsheet.

Also this specific app is going to updated by only one user.

With respect to my original question,

Do we have to use 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 new database field added specifically for Appsheet

We think Appsheet is a great solution and we want to find a way to leverage its strengths with our established relational database.

Thanks,

Alex

Steve

If you want the database to supply and use its own primary keys and you want to be able to add rows from within the app, you will need two independent key columns: one for the database and one for the app.

If you want to be able to add rows from the AppSheet app, the column in the app table designated as the key column in the app must be set by the app; it cannot be set by the database.

If the database is going to add a value to a non-key column of a row of the app’s table, that non-key column in the app must have Editable? set to OFF.

The database-supplied value will not be available to the app until a sync has occurred after the row creation in the app, so app logic should not expect a new row to have that database-supplied value.

Alex3

Hi Steve,

Thanks for the quick reply. I really appreciate your patience.

So if I understand correctly, with the Car table analogy as an example:

MYSQL Car Table
Car

Car.ID (Primary Key)
Car.ID_App (field added specifically for AppSheet)

AppSheet Car Table
Car

Car.ID_App (Appshet Key)
Car.ID (not editable i.e. Editable? Set to OFF)

You mentioned the Sync
Can the ‘Save’ button (for the Add Record action) be set to trigger the sync?

Thanks,

Alex

Steve

If you haven’t already seen this:

If it conflicts with my suggestions, go with the doc.

You’d need to attach an action to the form view to perform a navigate to another view in such a way as to trigger a sync.

Alex3

Hi Steve,

This is great.

Thank you very much for the help and for sharing your expertise.

Alex