Workaround for turning a virtual columns into a normal one?

AppSheet AppSheet Q&A
, ,
1

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:

  1. Contracts, it has a Deposit amount paid by the Customer during the life of a Contract.
  2. Movements, marks the start and end of a particular Contract.
  3. Transactions, records payments and reimbursements of Deposits.

They are related as follows:

  • A Transaction column in “Movements” table has a reference to “Transactions” table to record a Deposit paid by the Customer at the start of a “Contract” (which as another column with reference to Contracts table).
  • A new Movement form creates a record at the end of the same Contract, retrieves the first Deposit Transaction and shows it to the app user, for him to know the amount of Deposit the customer had paid, then ask the user whether to retain the Deposit or create a new Transaction to reimburse it.

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:

  1. You cannot have List Type with a normal column. It has to be virtual.
  2. Turning the column type to Ref and adapting the formula accordingly, made me lose the inline view in the form, and the user is now presented only with a greyed number showing the amount of of the first transaction.

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!

2

KJS:

You cannot have List Type with a normal column. It has to be virtual.

No, but you can have EnumList, and it will work in many of the same cases.

KJS:

showed a nice, clickable inline view of the first Deposit transaction

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.

KJS:

(I’ll have to do it around 30 times)

30 times?! Wow, really? Why?

3 Likes
3

Always a pleasure to read your posts Marc

Marc_Dillon:

No, but you can have EnumList, and it will work in many of the same cases.

Thank you, I will try that and tell you the outcome.

Marc_Dillon:

Personally, I would never use it.

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

Marc_Dillon:

But perhaps you can just build a concatenated string to show the pertinent values from the initial record.

Thanks! that would be a nice alternative, if it breaks

Marc_Dillon:

30 times?! Wow, really? Why?

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.

4 Likes
4

Hello dear @Marc_Dillon

Marc_Dillon:

No, but you can have EnumList, and it will work in many of the same cases.

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!

3 Likes
5

Also, hopefully this would bring benefit to other new users like myself:

First

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

Second

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

1 Like