Put multiple values in single cells to reduce writing time

[Kirk_Masden]

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.

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

[Aleksi]

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),",").

[Kirk_Masden]

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

[Mike_A]

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!

beginnersbook.com – 2 May 15

Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database

Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly. Let’s

[Kirk_Masden]

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:

Force immediate calculation Tips & Tricks 💡

Thanks for your interest. For quite a long time now, I’ve been working on a flashcard app for language learners. With this app, students following a review schedule that is divided into “sessions.” The “cards” (records) that a student is to study in each session are selected through a slice, according to the session number that each individual card was begun in comparison with the current session number. It its the “current session number,” stored in a separate small table, that completely c…

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!

[Kirk_Masden]

Mike:

I can imagine this is not much of a problem for tables that are not the main database for the app (eg: variables)

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.

[Mike_A]

@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.

[Jonathon]

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:

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.

[Mike_A]

@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…

[Jonathon]

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?

[Kirk_Masden]

Jonathon:

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

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.

[Glenn_Thorne]

Hi Aleksi

Trying to use this for setting up variables. Are you using one row with different columns for each variable OR different rows for each variable that are found by searching through the rows for the appropriate variable name?

And I assume the Split() function referenced is for the different component parts of the validif() function, is this correct?

TIA
Glenn

[Aleksi]

I’m using one row for one variable. Then you can easily find that row. When it’s a string, yes you can read it as a list for the Valid If with the SPLIT expression.

[Glenn_Thorne]

Hi Aleksi,

To confirm - when using one row per variable then you would make the column a text field and then allow the user to enter a text value. But would this not complicate things when we want the user to enter long text for one variable, a date for another variable, yes/no for another etc? Doesn’t it make it difficult to enforce data validity rules on the data entered by the user since the column takes the same data type or all variables?

[Kirk_Masden]

I may not need to add this but, just to be clear, on my app the user is not typing the information in. I’m using actions to store variables that are needed for the operation of the app (slices depend on them, etc.) but are not fully understood by the ordinary user.

For information that the user types in, under normal circumstances (e.g. adding a new row of data) I don’t think there’s any need to use this technique because the full row of new data will be written all at once.

Oh, and on my app I don’t this on a simple table that basic has just one row.

[Mike_A]

@Aleksi - based on @Glenn_Thorne response, maybe a picture of the variable table you are talking about might help.

Aleksi:

one row for one variable

Is this a table with one column only (and a different variable in each row), or a table with one row, and each column represents a different variable, or something else. Good to learn what works from experience.

[Kirk_Masden]

Hi @Glenn_Thorne!

In my case, I have a situation where, if I was not concerned about time, I would put three different values in three different cells on the same row of a small table for settings. Putting the three values in one cell, separated by commas (or, you can use some other character as your separator), saves writing time (see the sample app I’ve made public:

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

The values in the list could be referenced in the validif() function but other uses are possible.

[Aleksi]

Who never knows…

[pravse]

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

[pravse]

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.

[Kirk_Masden]

praveen:

I’m guessing you use quick edit and that’s why you have this issue.

Quick edit may lead to similar writing times but I noticed the problem with grouped actions. Even if I’m writing the same amount of data, the time it takes seems to depend on the number of cells that are written to by individual actions in the group – not on the amount of data. So, using one action to consolidate the data in one cell is much faster than using individual actions to separate the data by cell.

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

If you copy it and use the plus mark (+) to make a new row with empty cells and then use the pen action to write data to those cells, you can see how slowly or quickly the data is written to the Google spreadsheet. Combining several writing actions into one and then writing all of that data to one cell saves a lot of time in this example.

Addendum: In my flashcard app, grouped actions are used to record how users are interacting with the app, not data that they are intentionally typing into it. When they add data for a new row using AppSheet’s add (+) function, multiple cells are filled simultaneously at sync so combining data in cells is not needed at all. So, in my case, combining data in single cells is only useful for improving the efficiency of writing tasks that are done in the background via grouped actions.

By the way, I tried to use ChangeTime stamps to record the start and end of the grouped actions but I couldn’t get a distinction between the time of the first writing event and the last one in the grouped action. Is that a characteristic of grouped actions or am I doing something wrong?

[pravse]

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

[Jonathon]

@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!

[pravse]

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 :]

[Aleksi]

Interesting discussion indeed 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.

[pravse]

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.

[pravse]

@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.

[Aleksi]

Absolutely correct!

[LeventK]

Dear @praveen
Being a middle-aged man om his 50’s, over the little bit more than 3 decades of my professional life, I have met and worked with many CEO’s whom I should admit and respect that have strong vision and foresights about the future predictions of their companies. I’m an AppSheet Partner and Developer since 2015 and I should really admit that you are one of that visionary and foresighted CEO’s that I have met so far. Making our “Alien” @Aleksi a part of the AppSheet team was a strong proof of that. Though I never met but “literally” know the names of other “Aliens” inside the team like @brian, @Phil, @morgan, @tony, @Adam, @Harry and many possible others, they are the crucial staff who will take us to the next generation in no-code mobile application development world.

On behalf of you, I want to forward my strongest and kindest regards and gratitutes to all the development team from the bottom to the top! I have the faith that we will be stronger than ever and continue to lead the market! THANK YOU and GOD BLESS YOU ALL!

Last big thank is to the friends/partners in community with whom I have shared information helping us together to raise our level as a developer - but not limited to - Bellave Jayaram (my dearest partner), Kirk Masden, Grant Stead, Jonathon Sinclair, Steve Coile, Gil Litman, Stefan Quartemont, Simon Robinson, Reza Roofi and many others. THANK YOU and GOD BLESS YOU ALL!

This is specifically for @Kirk_Masden
神はあなたを祝福するかもしれません

[Kirk_Masden]

You are too kind.

感謝の気持ちで胸がいっぱいです。

[Aleksi]

The column type that I have used is a LongText. If I need to use a number, I convert that text as a number like… NUMBER(LOOKUP(“Reminder in days”,Variables,ID,Name)). If I need to read values for the Enum field, then like SPLIT(LOOKUP(“Trailer Numbers”,Variables,ID,Name),",") with Suggested values.

[Mike_A]

Interesting approach. This is what I assumed - but I had not imagined how detailed you get with this. Thanks for clarifying!

[Mike_A]

@Aleksi - One last question. By putting each variable in its own row, your approach clearly allows for a huge number of variables but required lookups to pull the data. I would tend to just create a table with the variable names across the top (header row) and row 2 for values. That way i could directly call the variable without needing a lookup each. This would still allow 255 variables - which is a lot. Am I missing a disadvantage to this if you have less than 255 variables?

Love to learn how different people approach problems

[Aleksi]

While I’m using this method for initial values or like lists, it doesn’t matter because the LOOKUP will be triggered only when you open that record… or like when you trigger the Workflow rule.

I used your approach earlier, but I found out that the user experience wasn’t good enough. If you have 255 variables and you want to change the last one, you need to scroll a lot with a form view. It’s easier to do that with a table view or you can use search. And if you add the 3rd column, you could group your variables as well.

[Mike_A]

I knew you had a reason behind! I was not thinking User entered data like you are doing (as @Kirk_Masden mention). So for user entered variables use rows. For system variables, can still use columns…

[Kirk_Masden]

I recently posted something that is related to the premise of this thread.

Counting lists in cells when some cells are empty Questions

I see. Thanks. I’ll look into changing the column type later.

In response to this latest post of mine, @MultiTech_Visions and @Steve helped me see that I should have been using an EnumList column type instead of a Text column type to “put multiple values in single cells” as I described above. I don’t know how much difference the column type makes in implementation and how relevant it is to this discussion but I thought I should mention the issue in this thread too.

[Kirk_Masden]

One more point: The need to reduce writing time that I mentioned in the title of this post has been greatly alleviated by the new “add a row to another table” action:

Action: Add a Row to a Table Tips & Tricks 💡

Actions are manual ways of interacting with your data, allowing app users to visit a URL, start a phone call, see a location on a map. Actions also allow for quick data changes, or creating a new row of data in the data table for which the Action is associated. Until Praveen’s recent feature announcement, it was not possible to use an Action to add a row of data to a different table. Now, by creating a new Action and specifying which separate table data should be added to, and defining each col…

In the past, because this sort of action was not available, I used multiple actions, each writing to a separate cell, as a work around. Even though the amount of data was the same, I found that the division of the task into multiple actions greatly slowed down the writing process. This post was in part a response to that issue.

Even so, I think there are still situations were the time saved by writing multiple data points inside a single cell with a single action can be worth the added design complexity involved.

[Grant_Stead]

Wanted to throw something out there… I’ve found that meta data is difficult to identify and capture… As humans we feel the need to categorize, so often a “thing” can have a million descriptors, meta data. So, often i’ll concatenate all of the important things into a “meta” column… usually with a delimiter of sorts… Then, expressions can find it IN(), and it’s a dumping ground for codes, etc…

[Kirk_Masden]

I also find IN() extremely valuable. It seems to work quite efficiently in AppSheet too.

[Ami]

Hi

Thinking outloud here, i have a table with tasks, as i update those tasks status i want to keep a log to track changes ( useremail, timedate, status). My thought was to open a form and have the data in one cell. Eventually i need one cell to contain a list of lists, similar to a library with python. However, i do wish my users to be able to view the data. So i’m thinking of a table of virtual columns in which there is a split mechanism that deconstructs the list of lists. Does that make sense?