KEY column...... First app. Didn’t understan...

[Tim_Meske]

KEY column… First app.

Didn’t understand the importance of a KEY column.

While developing my app I changed the KEY column, created new KEY columns, etc.

Now understanding the KEY column is not intended to change, how can I ensure that my many KEY column changes won’t cause problems down the road?

Can I establish a permanent KEY column after the previous changes?

Thanks!

[Steven_Coile]

The values in the KEY column must each be unique. So long as they are, you’re good. You’ll need to find a way to generate KEY values for future rows that is unlikely to ever produce a duplicate. AppSheet provides the uniqueid() function for this purpose (though you aren’t required to use it).

You may already have duplicate KEY values. AppSheet goes to some lengths to prevent that, but it’s possible to get around the safeguards, and the safeguards are ineffective if you’ve modified the data directly in the spreadsheet.

So you’ll need to audit your KEY column values to identify and eliminate duplicates. There are several ways to approach this. One way would be to sort the spreadsheet by the KEY column, then visually scan the column for repeated values. Fixing duplicates could be as trivial as just assigning new values, or could be very hairy and problematic.

[Tim_Meske]

+Steve Coile

+Steve Coile

Hi there.

Currently the key column name checked as KEY is listed in the editor as

_RowNumber.

It is grayed out and I didn’t create it, must have been created as a default.

The far left column in my excel spread sheet is a column that I created with the intent of it being the ID column but it is NOT checked as KEY in editor.

This excel ID column wasn’t added until long after I started creating the app.

Prior to me adding the intended spreadsheet ID column, the far left column was the date.

Not sure if the date was ever set as KEY.

Also not sure if I previously set other column(s)as ID.

Basically I don’t know what I had as ID column and KEY in the past and don’t know if it matters at this point if I know that currently there are no duplicates?

I’m concerned because there was a time that I copied and pasted dates into this column when it was the far left column.

I really don’t know when and what the column ID and key column was set.

If today there are no visual duplicates listed in the spreadsheet can I rest knowing there aren’t hidden problems?

BTW only 48 rows in the spreadsheet.

All created for development purposes.

Ultimately I would like to clear them all out before the app is deployed.

Maybe deleting the rows minimizes my ID and key concerns?

[Tim_Meske]

My excel spreadsheet has first 49 rows with KEY 2-50.

Rows after that are KEY coded with uniqueid().

All rows were created for development purposes only.

Now that the app formulas for KEY are set and will deploy soon, is it advisable to manually delete the rows from the spreadsheet?

Or some other way to clear out development rows.

Thanks

[Steven_Coile]

@Tim_Meske I would clear them from the spreadsheet, myself.

[Tim_Meske]

Just wanted to be sure that there isn’t any magic behind pre-existing rows that get deleted. So long as the key column doesn’t have duplicate numbers we are good; doesn’t matter if the initial 49 rows are out of sequence after deleting some rows?

[Steven_Coile]

@Tim_Meske Unless you’ve specifically told your app to use the [_rownumber] column, the order of the rows is immaterial. You could even have blank rows with no ill effects,

[Tim_Meske]

+Steve Coile thanks

[Steven_Coile]

@Tim_Meske Several things…

You keep noting “the far left column”. Column position has no particular significance; AppSheet identifies columns by name (the column header) and ascribes significance by name. That the column is “the far left” doesn’t mean anything in particular to AppSheet.

The [_RowNumber] column is defined and maintained automatically by AppSheet, and used as the KEY column if AppSheet cannot find a more suitable KEY column when the table is created. The values in the [_RowNumber] column will always be unique, but may change if you re-order or remove rows in the spreadsheet. For this reason, the [_RowNumber] column is considered “unstable”, and not an ideal choice for the KEY column.

You can change your KEY column at any time. But keep in mind that the KEY column values are how Ref values refer to the table’s rows. If a key value is stored in a Ref column, that stored value is not automatically updated if you change the KEY column. As such, that stored key value will likely not match a value in the new KEY column.

For instance, suppose the old key column used numbers (1, 2, 3, …) for key values, but the new key column uses letters (A, B, C, …). A Ref column that contains an old key column value (e.g. 2) won’t match any values in the new key column, breaking the reference.

If you delete all the rows in your spreadsheet, you will eliminate any concerns about duplicate or broken keys.

I’d like you to spend some time tinkering with keys and references to better understand them. They are extremely important,and understanding them will benefit you greatly.

[Tim_Meske]

+Steve Coile

Thanks for the pointed response!

I am interested now that I recognize the importance.

So you suggested deleting all rows to eliminate all concerns.

So simply delete all rows directly from the excel spreadsheet then regen?

Nothing else?

Then add/establish the real KEY column; in this order?

[Steven_Coile]

@Tim_Meske You may delete the rows directly from the spreadsheet. After doing so, you only need to Sync the app. Regenerate will coincidentally sync the app, but is overkill if all you’re doing is removing rows from the spreadsheet.

You may add/delete/rename the KEY column either before or after deleting the rows. If you add/delete/rename a row, you must Regenerate the columns so that AppSheet sees the changes.

If all you do is change the KEY column, you only need to SAVE (though Regenerate will also work).

Sync makes the app aware of data changes. Regenerate makes AppSheet aware is data structure changes, and performs a sync.

[Tim_Meske]

+Steve Coile Since all current rows are for development only I will delete them all and key in new rows to finish development then deploy.

Also, if I want to keep the row data would copying the rows before deleting them then delete the rows then save the app and then finally paste them back in fix my concerns or do the possible mixed up keys I’m concerned about follow the copied/paste rows?

[Steven_Coile]

@Tim_Meske There’s nothing magical about the data in the rows. If you copy-delete-paste, you’ll have achieved nothing.

Let’s try a different angle. You’re currently using the row number as key. What do you want to use as key instead?

[Tim_Meske]

+Steve Coile

Was planning to use the function that Appsheet offers; uniqueid() Seems like the simple fix

[Steven_Coile]

@Tim_Meske Sounds good, using uniqueid().

The easy and safest way to convert to a new KEY column is to:

1. Create the new column in the spreadsheet. Set the cell format for the column to plain text. Your app will error when it notices the extra column, so proceed quickly to the next step.

2. Regenerate the table’s columns to make AppSheet aware of the new column. The new column will not be visible to the app until the app syncs, which isn’t needed yet.

3. Configure the new column in AppSheet as desired except do not yet make it the KEY column. Do configure the Initial value with =uniqueid().

Consider marking the new column as HIDDEN, as there’s likely little reason for the app user to ever see it. I also like to set the column’s Editable_If field to =false to ensure the app user won’t ever be able to modify a KEY value should they ever run across it somehow.

4. If you have any slices that use this table, update every such slice to include the new column.

5. SAVE the changes.

6. In the spreadsheet, copy the values from the existing KEY column to the new column.

Since the existing “column” is just the row numbers, copy the formula, =row() into each cell in the column, then select the entire column, cut, and paste as plain text. Or put 2 in the first row’s cell and auto-fill the rest so the numbers match the row numbers.

There is no strict formatting for text-based keys like uniqueid() generates except that they be at least 1 character long and be unique within the column. The row numbers meet that criteria, so you can re-use them as keys for the existing rows! New rows will get new keys generated by uniqueid().

The newly-added values won’t be visible in/to the app until the app syncs, which still isn’t yet needed.

7. Remove [_RowNumber] as the KEY column and make the new column KEY.

8. Update any AppSheet formulas you have that refer to [_RowNumber] as a means of identifying a row to instead refer to the new column. You may have used such references in formulas in the configurations for: tables, columns, slices, views, formats, actions, workflows, and elsewhere.

9. SAVE the changes.

You now have a new KEY column that preserves any existing Ref references.

10. Sync the app. All users should do this before attempting any changes in the app. Failing to do so could prevent further in-app changes from becoming permanent.

Done!

[Tim_Meske]

+Steve Coile

The 10 steps worked perfectly.

Thanks for sticking w me on this Steve!!!

I’m learning a lot from my first app.

A friend that turned me on to Appsheet will gain a lot from this too, will pass along. Take care!