LinkedIn
Twitter
This post is a follow-up to "Using INPUT() to update another table using a value from this row":
https://www.googlecloudcommunity.com/gc/Tips-Tricks/Using-INPUT-to-update-another-table-using-a-valu...
In that tip, I provide a sample app that shows, as the title indicates how to take a value from a row and update a cell on another table. What I did not do was try to explain why that can be very useful. In this post I'll attempt to do that.
Here are some screenshots from an app I'm working on:
โ
The first image is the bottom part of a "card" in my flashcard app. The marked action is a grouped action. The first action writes a long string via "With these inputs" for access on the other table via the input action.
The following illustration shows what I am doing:
The QuickChart calculations and the calculations of dates required the use of SELECT() expressions because anticipated dates depend on a count of other records that are ahead of the current one and on the calculation of a couple of types of rates of progress. But those details aren't important here. The key point is that INPUT() function allows us to access the results of complex SELECT() and other expressions without weighing our apps down. It's akin to "just in time" manufacturing; instead of constantly calculating and recalculating information so that it will be available if the user wants it, it is only calculated when it is required. Moreover, even if many virtual columns are used in the second table for additional calculations and display, these won't cause problems because the table is tiny.
Finally, in regard to how you prepare your data to be split and displayed in the targeted view, I recommend using two strings, one for the splitting and and one to insure that your INDEX() numbers won't change, even if you have an empty value. Let's say that the string used for the splitting will be "A1B2C3" and that the second string will be "X1Y2Z3". When you concatenate your string it'll look something like this:
CONCATENATE([first value],"X1Y2Z3A1B2C3",[second value],"X1Y2Z3A1B2C3",[third value],"X1Y2Z3A1B2C3", . . . etc.
Let's say that the [second value] is an empty string in this case. After you SPLIT() with "A1B2C3", you can use INDEX to correctly locate the second spot. Then after you use SUBSTITUTE() to remove the "X1Y2Z3" string, you will be left with an empty string right in the second spot where it is supposed to be. Without the "X1Y2Z3", the INDEX() numbers following an empty spot would all be off.
Based on my own experience, I have to admit that using INPUT() in this way can be somewhat difficult because everything must be carefully packed and then unpacked, then prepared for display. But, in terms of app performance, I've found it to be more than worth the effort.
Thanks Marc! Your response and the response from @ใคใซใซใฎใใฃใกใใ have made it clear to me that the initial lack of response was due to my failure to explain myself properly.
Since we are in agreement about avoiding the placement of expensive expressions in virtual columns, I'll skip that point.
And, I'd like to emphasize that the usage I'm trying to explain here is the same as that of the sample app I made a couple of years ago ( https://www.googlecloudcommunity.com/gc/Tips-Tricks/Using-INPUT-to-update-another-table-using-a-valu...) in that any one of a long list of records on one table can send something to be displayed on the tiny table.
So, as you rightly point out, the new part is the idea that we can use a single input expression to send all of the information that is needed to build a complete view.
Assumption (which may not be correct): It's a good idea to avoid large numbers of "to-be-synced" data in the upper right corner of user's apps:
Even a very large complicated string still only results in one little sync added to the line and this usually resolves quickly. It might be almost as quick to use multiple inputs to send the data individually but I haven't tested it.
But, I can say that I am not alone in doing something like this. Here's an old post of mine I titled "Put multiple values in single cells to reduce writing time":
My first response was from @Aleksi, who wrote "Iโm always using this kind of approach with app variables." I was very happy to read this because it told me that, if Aleksi was doing it, it was a reasonable approach. There's some interesting (at least I was very interested) discussion in regard to this post, including from Praveen (that great leader we all miss).
Another possible advantage of my approach of throwing everything in one cell: When one imagines additional types of data for display or to be used for other purposes in the target view, one does not need to add a new column to the target table. The single cell takes whatever you want to throw at it -- few, many, small, large -- as the case may be. It's true that virtual columns need to be added but if the table is tiny they don't hurt performance and are easy to add and remove.
