Dear nice people,
I used to have a lot of virtual columns in almost 20 tables in my app, but thankfully after reading your performance-related topics in this forums I went back to convert them to normal columns when possible. The dataset is still small so I didnโt notice any performance issues, but Iโm glad to have been able to avoid future problems thanks to your advice and patience especially @Steve in this regard.
I need to ask you please regarding one particular case that I would have to do repeatedly and would like to know if my approach is appropriate or there is a better way.
An example involving three tables:
They are related as follows:
I used to have a virtual column named โoldTransactionโ, type List, base Ref, with the following formula:
IF( "End" = [movementType],
SELECT( Movements[transactionID],
AND(
[contractID] = [_THISROW].[contractID],
"Start" = [movementType]
)
),
LIST()
)
This worked perfectly and the form for the End Movement showed a nice, clickable inline view of the first Deposit transaction, and the user could not only see the old amount but also open the old transaction from within the form and see itโs details, like date, hour, ID etc, then close it and come back to the form to complete the End Movement.
When I realized after, that this is bad practice because the value of the virtual column will never change but each app sync will recalculate the column for all Movements rows of all Contracts again and again. So I decided to turn this virtual column to a normal column.
I was faced with the following problems:
What I did was to create a new virtual column โvirtualOldTransactionโ, type List base Ref with the following formula:
LIST([oldTransaction])
and I have my inline view back. I thought this at least would let me avoid a SELECT in a virtual column.
What do you think please? Should I continue doing this (Iโll have to do it around 30 times)? is it worth it? Anything better?
Thanks much!
Solved! Go to Solution.
No, but you can have EnumList, and it will work in many of the same cases.
The pop-up view of read-only related records from within a parentโs form view is, in my opinion, a weird โartifactโ that doesnโt really conform with anything else present in Appsheet. Iโve seen multiple other users posting things about this weird view, wanting to use it in other places, wanting to use it in different ways etc. However, take this as you will, Iโve never seen any of the more frequent users of this forum post about using it at all. Personally, I would never use it. I do however see that it can be useful in some ways, like your situation, and your solution of pushing the SELECT into a real column, and only using a VC to mirror the data into a List, is definitely a more performant way to do it, if youโre wanting to keep the pop-up/inline view within the form.
But perhaps you can just build a concatenated string to show the pertinent values from the initial record.
30 times?! Wow, really? Why?
No, but you can have EnumList, and it will work in many of the same cases.
The pop-up view of read-only related records from within a parentโs form view is, in my opinion, a weird โartifactโ that doesnโt really conform with anything else present in Appsheet. Iโve seen multiple other users posting things about this weird view, wanting to use it in other places, wanting to use it in different ways etc. However, take this as you will, Iโve never seen any of the more frequent users of this forum post about using it at all. Personally, I would never use it. I do however see that it can be useful in some ways, like your situation, and your solution of pushing the SELECT into a real column, and only using a VC to mirror the data into a List, is definitely a more performant way to do it, if youโre wanting to keep the pop-up/inline view within the form.
But perhaps you can just build a concatenated string to show the pertinent values from the initial record.
30 times?! Wow, really? Why?
Always a pleasure to read your posts Marc
Thank you, I will try that and tell you the outcome.
I totally agree with your reasoning! Undocumented weird artificats should always be avoided no matter how nice they are. I just thought well, should it brake, it would be just a display issue for just one field, no big deal. Probably Iโm too tempted
Thanks! that would be a nice alternative, if it breaks
Nothing to be proud of really. Just rushed implementation with insufficient platform knowledge. I tried to hack it into completion.
So mainly, I was focused on the spread sheet, keeping it a fully normalized database, with minimal data, no repeated column pairs in more than one table, no redundant-calculated values etc. I have to say that I was tricked by AppSheetโs guide โThe Essential of Dataโ into believing that you have to focus on the database. It even provides you with external links to teach AppSheet users the norms for the creation of efficient databases etc. Moreover I have previous db knowledge.
Hence, I kept creating virtual columns for everything, calculations, references even temporary variable storage to use for collecting user input, decisions or even formatting, thinking it is a one-time calculation, which for anyone reading is NOT. I can also say here, to some extent, that I was oriented in this direction by some official guides and examples. Thankfully I was saved by the community
So I have 24 data tables, and have one or two or three virtual columns per table that I should convert to normal ones, many with SELECT-and-sisters expressions, so I expect to have some 30 columns to convert.
Another thing I remembered to get rid of the virtual columns, I used to have virtual columns to store decisions to launch Actions. Seemed better in terms of tracking and the-equivalent-of-code-readability to keep the calculations next to the data. Now, Iโm moving all decision-related calculations to inside the respective Actions themselves.
Hello dear @Marc_Dillon
Just to give an update as promised regarding this proposal. It worked exactly as if it were of type List, but I got a warning saying I had to allow users to add new values. I was worried about that but seems since the column has an app formula this option didnโt have any effect, and no edits were allowed which is the desired behaviour.
The only issue was that it didnโt help with the display of the weird pop-up view. Other than that I believe it is a perfect solution to turn a list virtual column into a normal one. So again, thank you!
Also, hopefully this would bring benefit to other new users like myself:
If you spend sometime thinking about the purpose of your expression you can find ways to simplify your expressions and replace them with simpler more efficient ones.
Take this expression for example:
ISBLANK( FILTER(
"Contracts",
AND(
"Open" = [contractState],
IN([contractID], [Related Contracts])
)
))
I managed to avoid the use of FILTER/SELECT and replace the whole expression with one line doing exactly the same thing:
NOT( IN("Open", [Related Contract][contractState]) )
In my first post, I mentioned that I adapted formula below, when I changed the column type from List to Ref:
This is the new formula. I just added ANY()
and changed LIST()
to ""
:
IF( "End" = [movementType],
ANY( SELECT( Movements[transactionID],
AND(
[contractID] = [_THISROW].[contractID],
"Start" = [movementType]
)
)),
""
)
User | Count |
---|---|
44 | |
29 | |
23 | |
20 | |
15 |