Put multiple values in single cells to reduce writing time

Recently, I’ve been putting multiple values into single cells in order to reduce the number of “writing events” (I’m not sure what the proper term is) to my spreadsheet. For example, I can used an expression like

concatenate([Value1],", “,[Value2],”, ",[Value3])

to put three values in one cell. Then, in the app, I can access them using combinations of split() and index() expressions. For example, if [Value2] is a number and the name of the column that contains [Value2] is [Example], the following would give me the required value:

number(index(split([Example],", "),2))

By the way, the number() expression is needed because concatenate expression changed the number to text. Number() changes it back to a numeric value.

This makes my job as an app developer a little more complicated but it definitely reduces the number of “writing events” (the number of syncs that the app displays) and I’m also pretty sure that it reduces the actual time that it takes to accomplish syncs. Once the expressions to parse these cells are in place, they don’t seem to slow down the app, at least in my experience.

I’m offering this as a “tip” but if I’m sorely mistaken in my approach, I hope someone will set me straight. :slight_smile:

Postscript: Here’s part of a follow-up post I wrote to provide an example of the kind of situation in which putting multiple values in a single cell speeds up the writing time:

I made a sample app to demonstrate how one can speed up the writing process by using concatenate to put several different data points in one cell and thereby reduce the number of writing actions in a grouped action. It’s called “Time test” and it’s on my portfolio:

https://www.appsheet.com/portfolio/230844

1 Like

I’m always using this kind of approach with app variables. I normally give the option for the customer to have one variable table where they can change almost everything… like drodpwn options, texts in Workflow rule etc. For example when you need to read options for the Valid If, you can do that with the SPLIT(LOOKUP(“VariableName”,Variables,Name,Variable),",").

2 Likes

Thanks @Aleksi! If you use the same approach a lot, know I know for sure that I’m on the right track. :slight_smile:

Who never knows… :smiley: :smiley: :smiley:

1 Like

Interesting dilemma. I have been trying hard to follow the logic of “normalizing” my databases to try to avoid DBMS anomalies. Doesn’t this suggestion violate the ideas of First Normal Form (1NF)? Wondering which tradeoff is worth it. I can imagine this is not much of a problem for tables that are not the main database for the app (eg: variables), but what about storing multiple addresses in the same cell - bad practice? I guess in some cases the sync gain is worth the risk? Just thinking out loud! :thinking:

2 Likes

Thanks @Mike!

I’ll be interested to see how the discussion develops in regard to the points you raised.

Clearly, throwing multiple values (particularly, values of different types with different functions) into one cell or column adds complexity to a database and has the potential to cause trouble. So, I wouldn’t want to do it if I didn’t feel a strong need. I have found, however, that some of my students tend to build up big “sync debts” (rather long queues of syncs that are pending). My interest in ameliorating that problem has led me to take on some extra complexity in order to cut down on the number of syncs that my app requires.

One more advantage to using individual cells to save lists as opposed to single values is that it allows me to accumulate a rich history and app usage. For example, on my app, when a student feels that he or she was able to answer a question correctly, they tap “Got it” and this records the “session number” in a column. The presence of the current session number in that column then causes that question (or record) to be removed from the slice. In the past, the latest session number would replace whatever came before it but now I have the column set up to accumulate a text list (separated by commas) of all of the session numbers in which that card (record) has been completed. Thus, the cell now fulfills two functions: a) removing completed records from the current slice and b) building an archive of past activity. As this doesn’t seem to impede the function or efficiency of the app, I’m happy to have each cell be a little list that I can parse later as I see fit.

One more reason that I have values written to my underlying spreadsheet is to force a calculation. I do this when, for some reason, the value that the app displays tends to be incorrect until the sync has completed. I’ve written another post about about how to write to the sheet in order to force the app to calculate something immediately:

If I have several calculations that I want the app to do immediately so that the figures displayed are correct, it doesn’t really matter where I write them so I can just throw all of them in to one record.

These are the three main reasons for which I have begun to experiment with saving lists instead of single values. I’m happy with what I’m doing so far but I’ll be eagar to learn more about downsides that I may have overlooked. I looked at the web page you introduced, @Mike, and, as I am a novice when it comes to databases, I’m not sure that I was able to fully digest it. On a quick reading, however, I didn’t see anything that gave me pause about my current approach.

Thanks again!

1 Like

Indeed, a small table of variables is one important location for my “list cells.” However, the “archive” of past activity I mentioned in my longer reply, is on my main table.

1 Like

@Kirk_Masden - makes sense for small lists as the DBMS risks are not as high. I guess best answer is to use it where it makes sense and does not create record integrity risks or high maintenance. Thanks for sharing as always.

1 Like

Interesting topic.

I agree with Mike in that I generally try and constrain myself to relational database normalization… However, as Kirk points out, AppSheet doesn’t always work best with a completely normalized database.

One example where database normalization isn’t ideal is using security filters on user-specific data. Take for example, the following table structure:

image

In a completely normalized database, the [assignment] table would have the [student] foreign key, and the [grade] table would have the [assignment] foreign key.

With AppSheet I have found it beneficial carry the [student] foreign key to the [grade] table, to facilitate efficiency of the security filter (otherwise, you are loading all grades or performing the dereference lookup across all rows at load time).

So, the tradeoff is database denormalization or slower load times. I’d be really curious to know AppSheets thoughts on these topics, in terms of best-practice for this platform.

Sorry that was a tangent.

1 Like

@Jonathon - thanks for chiming in. Not a tangent at all. Trying to figure out how to strike the “right” balance of normalization. Turns out that Appsheet ease-of-use can be in conflict with full database normalization. Good to hear how others are addressing this…

AppSheet is quite good at drawing in users with its ease-of-use… However, for users that stick with the platform, issues like this will crop up. There certainly is a brick-wall with the platform, and you can knock it down a little with tricks like database denormalization or convoluted application definition tricks.

In my ~2.5 years with the platform, AppSheet has done a great job of releasing updates which have made the plaform way more versatile… However, I am somewhat concerned with AppSheets ability to grow with its users as they become more comfortable with relational database concepts and application design.

I hope the no-code tenet isn’t a hill AS will die on. The platform is in serious need of looping functions, as one example. The automatic generation of views and assumptions which are made also need to be user-definable, as the workarounds to them are clunky and cumbersome.

When it comes time, will AppSheet users be able to take the training wheels off the bike, or will we have to move to a new bike completely?

2 Likes

I certainly have no plans to move to another platform. AppSheet does what I want it to do. However, what you wrote about hitting a sort of wall makes sense to me. I might not use “brick wall” as my metaphor as I’ve been able to find ways to get through difficulties I have encountered thus far. I would agree, however, that there is a sense in which AppSheet can become more difficult to master as one gets more advanced. Still, I’m confident that the platform will continue to grow and improve. I just hope that, in this dog eats dog world, the good people at AppSheet will be able to keep it growing and improving quickly enough that it can become a more-or-less permanent fixture on the internet. :slight_smile:

Great discussion! Thanks! Will share thoughts in three parts: (a) multiple values in a single cell to improve sync time, (b) normalization and security filters, and © no-code hill to die on

1 Like

Each logical update made by the user is treated by AppSheet as a separate saved change to sync. The reason is that you might have workflow rules that need to run on each of them. This is somewhat in your control … if you open a form and change four columns and save, then it is a single logical update. But if you use Quick Edit, each change is a separate update. @Kirk_Masden I’m guessing you use quick edit and that’s why you have this issue.

Now we could make this faster (collapse and send many changes together) if we are prepared to alter the semantics of the workflow rules. We’re talking about providing that option (Adam and Phil are getting started on this design discussion this coming week). But as you can imagine, that may not be the default.

Anyway, personally, I hate the idea of having a text column which is actually meaningless as a standalone property but is just being used as away to carry information. Prefer to have semantically meaningful properties rather than physical storage properties.

Normalization and sync … take a look at this article we just published. With the use of IN() expressions in security filters, you should now be able to maintain normalized data models but still get the benefits of security filtering. https://help.appsheet.com/articles/3086424-advanced-techniques-horizontal-scaling

2 Likes

We will definitely stay no-code. That’s been the principle all along and we’re pushing the boundaries to see how far it will go. It’s still early days but what you are observing is similar to the problems early relational databases had when their query processors were not great. We still have to do more work to make our expressions faster and to provide guidance when the expressions are inefficient as written.

Relational databases are no-code and declarative and (now) nobody thinks they died on that hill. They climbed the hill while CODASYL database dies out. Just saying … it’s possible :]

Interesting discussion indeed :slight_smile: How I’m using these variables and where is the limit… I’m using them for initial or dropdown values where those values are normally quite fixed but still the Admin can change them without the need to open the editor and change them. If you need dependent dropdown, that approach would not work. I’m trying to say… I have set the limit where I’m using this approach: they will be “active” only when you open the record. Another place is a workflow rule… like Subject, Body etc.

1 Like

@praveen thanks for the article on the security filters. I currently use USERSETTINGS() and security filters as mentioned in the article, however I have not experimented with using IN() to maintain data normalization on child tables, at least since I’ve migrated away from Google sheets.

I wasn’t aware that, when working with Google sheets, appsheet had to load the entire spreadsheet despite the security filters. This explains some of my struggles in reducing load times on some applications a year or so ago. Partitions in this case would be quite powerful, particularly if appsheet is capable of generating the sheet partitions itself based on some logic.

I will mess around with normalizing some of my tables tomorrow and report back. Thanks for the help!

Especially now that you’ve moved away from Google Sheets (I’m presuming to a relational database), the IN() security filters will help you a lot. Because the change we made last week pushes an IN() filter into the database query and it will be pretty efficient.

@Aleksi, yes your approach is necessary because we don’t currently have “App Settings” — just like user settings, these would be a single data row where constants could be changed without going into the editor.
Ideally, we should have these as a part of the app definition and they would just be accessed normally (eg: APPSETTINGS(“MinimumValue”)) etc. One more for the todo list.

4 Likes