Add multiple "reserved" columns to your table befo...

Marc_Dillon · 12-02-2020 02:17 PM

Here is a screenshot of a new table that I’m about to add to one of the apps I’m working on.

I have a general idea about what all columns that I’ll need in my app, but I’m certain that I’ll need to add more in at a later time once I get further down the development process. So I’m going ahead and just adding in 9 “reserved” columns from the start, which I will keep hidden for now.

Why do I do this?

Because with Appsheet, you can change a column’s name in the app very freely, with just a Save. You don’t even have to change the header in the back-end spreadsheet. Although I HIGHLY recommend that you do at the same time. (if you go to regenerate at a later time without changing the names to match then many things will get messed up)

What you can’t do as easily is regenerate the structure. Just on a blank table it takes a little bit more time. Try to regenerate on a large table full of data, it’s going to take a bit of time. Try to regenerate your structure on an app that is actively in use… you run the risk of people not being able to sync their data and forcing you to perform data recovery (that’s not fun, trust me).

With one app in particular that I often do a lot of work on, and is also very actively used on a daily basis, I have to schedule specific times for column additions. I do them late at night, and make sure that my client sends out notifications to his app users to make sure that they do manual syncs before and after. That became a pain in the butt after doing it a couple times, so the last time I did it, I went ahead and added a half dozen extra columns to EVERY table in the app. It was extremely beneficial.

MultiTech

A particularly good use case for this is if you’ve got Offline users.

Changing the version number or the column structure causes Offline apps to “break” - meaning that any pending uploads offline users have will refuse to go through (and recovery mode must be taken advantage of to get that data).

If you include extra columns - I call them Temporary columns: Temp1, Temp2, Temp3, etc. - into your app, like @Marc_Dillon is suggesting here, then when you need to make an update that includes adding a new column you’ve already got plenty to choose from.

And since you’re not actually changing the STRUCTURE of the app, offline apps don’t complain.

In fact, I had an interaction with a client that was so funny I had to put it on a T-shirt

Marc_Dillon

Hmm. I might have to buy that shirt…

WillowMobileSys

@Marc_Dillon @MultiTech_Visions

I am a little confused and I certainly do NOT have the experience with deployed apps as you two do.

Are you using a database or sheets?

I have experienced a situation where I added a column in the middle of the table in a sheet. Some users didn’t Sync before using and while they were still able to to submit changes, the data was written into the wrong columns. I had to fix by shifting data over by one column. But that was only for the first entry each user made. Because a Sync was done in the background they were good for subsequent submissions. The user had no clue that anything was wrong. No errors were reported.

I concluded that after deployment of an app using sheets, if I simply add columns at the end then I can avoid this problem - even if a user fails to Sync after the new columns are added. But I think this is possible because a G Sheet doesn’t perform any type checking when data is added, as far as I know.

A database however will type check the incoming data and throw an error which prevents the row/data changes from being written. So deployment of changes needs to be handled more sensitively.

With this perspective, I can certainly see where having temp columns can be a huge benefit when a database is being utilized. They can help with sheets but maybe not as critical?

Thoughts, corrections, explicatives??

Marc_Dillon

GSheets.

I’ve seen that before as well, but it was a good while ago. Now I’m wondering if Appsheet changed something at some point to make it more strict about syncing with a column numbering mismatch.

Yes, I’ve had many “expletives” about this

WillowMobileSys

The issue I had happened back in June 2020. I assume you mean by “more strict” is that now it’s not just by column order?

LOL! I wasn’t sure about the spelling but was too lazy to look it up!

Marc_Dillon

I meant that maybe in the past, Appsheet allowed a sync to go through even if it noticed that the column count was mismatched. Which in the case of adding a column in the middle, this put data into the wrong columns.
But now it seems like it is more strict in disallowing the syncs to go through if it detects a mismatch, probably as a fix for the case of data going into the wrong columns.
Totally guessing here of course.

Haha. I wasn’t correcting your spelling, just making a joke. I thought you really meant this word:

MultiTech

@WillowMobileSystems you’re right about a true database throwing errors; AppSheet will throw errors if the structure changes - but not so much if you just add new columns to the end of the column list. (Sometimes that will sneak through without throwing an error.)

But generally speaking, AppSheet is pretty tight with it’s column structure having to match what the devices have (when uploading data).

Ami

If i add those columns, can a user of the app modify them by himself? Change their view name, show/hide them?

1minManager

Normally you’d add them and hide them until you needed them

MultiTech

As @1minManager mentioned, if you hide them then the users can’t interact with them.

But that is only talking about “USERS” of the app - not anyone accessing the AppSheet editor.

Anyone that has access to the editor will have the ability to modify those columns - just like they have the ability to modify anything on the app.

Ami

I built an app where there are a bunch of columns set up and then the user creates forms as he pleases when he selects with true/false if to show them. However, if i could allow my users to set the view names for those columns they would keep their functionality but at the same time allow flexability for the user

MultiTech

You can do this, you just need to include a field for the user to specify what the label should say - then you can base the display name or description off that column’s data.

So you’ll have two columns for each single column the users can add to their form:

the toggle to include or not; and
a text field for the label

Ami

That means the view name in the column should be an expression?

MultiTech

This is a “reserved” phrase in AppSheet - as there are specific elements (called “Views”) that control how things are presented in your app; these views have a name space, so when you say “View name” people might think you’re talking about that.

You will need an expression inside the “Display Name” or “Description” for the column that you want to change the column name of. Your formula would simply be the [Column] where the user entered the name of what the field should say.

Lucinda_Mason

That’s a great idea. I definitely need to do this, even in deployed apps.

Marc_Dillon

I would say that this tip is specifically for Deployed apps.

Lucinda_Mason

Of course. I wasn’t thinking clearly.

Add multiple "reserved" columns to your table before loading into your apps

A particularly good use case for this is if you’ve got Offline users.