Best way to have virtual column recalculate

Rifad · 07-27-2023 03:49 AM

Hello Community !

I've successfully configured an app with a database setup like this. However, I haven't come across many detailed resources regarding this particular issue within the community.

In this scenario, the VC (Virtual Column) fails to recalculate the output column, leading to erroneous data being displayed in the output table whenever changes are made to 'My user setting' through the 'All Data | Slice' filters. This is highly unacceptable and raises concerns about the data's integrity. It makes me question the purpose of using virtual columns if they are not recalculated as expected, and they only seem to update after a sync/force sync. I've thoroughly experimented with these features but unfortunately haven't found a resolution yet. Any guidance or assistance in resolving this issue would be greatly appreciated.

Marc_Dillon

A VC on the "My User Settings" table would update immediately.

Rifad

No I tried and It did not work

Rifad

Wow, I never actually noticed this behavior happening in real-time! I totally expected the virtual column to recalculate on its own. Now, it's got me rethinking how this virtual column really works. I mean, why should an expression from a slice calculation need sync to recalculate the output? It's making me wonder what difference the slice is supposed to make with security filters in this scenario.

Dyako_Amin

Please can we ge this looked into. VCs should be recalculating in real time, im having to force refresh my browser 3 times and after 30 seconds to see the updated results.

Rifad

I’m facing same issue and not sure if its a bug or intended behaviour.

Rifad

It’s really effecting data show to user. User keep updating and app must wait till all those multiple sync is updated and data is fetched back.

AleksiAlkio

This is because virtual columns are calculated on server side and if you don't have any relation between these two tables. When there is a relation, it should calculate VCs in the other table as well.

Rifad

I did try connecting User settings table directly to output table like this and still didnt help.

Rifad

@AleksiAlkio wrote:

When there is a relation, it should calculate VCs in the other table as well.

I observe real-time updating of slices, which filters out rows based on date input. However, when the same slice is used in a virtual column of the output table, it takes time to recalculate. This creates a situation where users see the change in the number of rows in the slice and assume that the COUNT data in the virtual column is correct, relying on that value. In reality, the values have not yet been updated, leading to user confusion and questioning the integrity of the data shown by the AppSheet application.

Rifad

Here is the issue

Rifad

I did change the expression avoiding slice to this directly in the VC and still waiting for sync.

COUNT(
      			SELECT(All Data[ID],
             AND(  
                  [Date Acquired] >= ANY(My User Settings[Start Date]),
                  [Date Acquired] <= ANY(My User Settings[End Date])
 ) 
                  )
            )

Joseph_Seddik

I'd try this:

Have a Ref VC in the All Data table with an app formula like:

IF( AND([Start Date] = ANY(My User Settings[Start Date], [End Date] = ANY(My User Settings[End Date])),
"myUserSettingsRowKey", ""
)

Then in My User Settings a VC with: COUNT([Related All Datas])

Rifad

@Joseph_Seddik wrote:

Then in My User Settings a VC with: COUNT([Related All Datas])

This VC directly in the My User Settings table also waits till its synced.

Rifad

@Joseph_Seddik wrote:

Then in My User Settings a VC with: COUNT([Related All Datas])

@Joseph_Seddik the issue is shown below. The output is in 3rd table that is OUTPUT.

1. My User Settings

2. All Data | Slice

3. Output table

The count in 3rd is the count of 2nd table/slice that is changed by date in table 1. Its not directly related.

So the VC is in TABLE 3. Although the slice of TABLE 2 updates instantly filtering out other rows the VC in TABLE3 is waiting for sync.

@Rifad wrote:

Joseph_Seddik

@Rifad I see, thank you.

So further to this:

@Joseph_Seddik wrote:

Have a Ref VC in the All Data table with an app formula like:

IF( AND([Start Date] = ANY(My User Settings[Start Date], [End Date] = ANY(My User Settings[End Date])),
"myUserSettingsRowKey", ""
)

I'd then add another Ref in the Output table pointing towards My User Settings table, then:

COUNT([refColumn].[Related All Datas])

I understand the issue and clearly see this strange behaviour. I can't explain it. I'm just suggesting an alternative that in my opinion would work.

Rifad

@Joseph_Seddik wrote:

COUNT([refColumn].[Related All Datas])

Unfortunately the Output table has a formula with conditions like this.

Here 'My All Data' table is slice with expression:

AND(
[Date Acquired] >= ANY(My User Settings[Start Date]),
[Date Acquired] <= ANY(My User Settings[End Date])
)

@Joseph_Seddik wrote:

clearly see this strange behaviour.

I wonder when the slice with above condition clearly filters out all rows in real-time and VC is still waiting for the sync. Puts users in total confusion like shown below. Not sure what to do now.

@Rifad wrote:

Here is the issue

Joseph_Seddik

I'm not facing this issue, but I remember long ago that I faced issues with VCs when I had Delta Sync enabled, so just in case check it.

Also, here are my settings, try to match them and test please. Otherwise we'll figure out a solution.

Rifad

The app is configured same way as above and the delta sync is off @Joseph_Seddik

ghost5

Turning off Delta sync helped my virtual columns to recalculate quickly when pulling data from other tables. Thanks

Rifad

Absolutely no help from support these days and 90% of time we get a copy pasted message like this. I just wanted to confirm if its a bug or intended behaviour and this is the message we get after 6 days.

Rifad

Just wanted to confirm appsheet team is most incompetent support team in this planet assuming google being one of the biggest company is nothing other than a marketing gimmick.

Last email was sent 3 months ago. Ticket was open almost an year ago.

Rifad

@devingu It will be helpful if someone can at-least check and acknowledge if its a bug or not. Its been 12 days my client is being kept on hold. Its showing wrong data until app is synced. I have shown all required details above in this post. Theres absolutely no resolution other than copy pasted messages from support. Here is the case id [1-3939000034861] .

Thanks

JMPeterson

Any updates or resolutions on this item? We have started noticing some strange behavior in virtual columns, some not updating at all.

Rifad

@JMPeterson

JMPeterson

Unfortunate. Strange, we haven't experienced the delay or lack or recalculating until recently. Or it was only just reported to use. Thanks for following up.

Rifad

Just wanted to give you a quick heads up on this. It's been 7 months and we still haven't received any updates from support. Can you believe the specialist has been working on it for that long? It's kind of crazy!

Pritesh2

Close Delta sync(Appsheet core) it work

Pritesh2

Close Delta sync (Appsheet core) it work for me

jaichith

If you have security filter. It may not work

blablabla

i'm having the exact same issue, thank you @Rifad for documenting it so well, and thank you @Pritesh2 for your solution, in the mean time, i guess this will work but as i have so many data, i would prefer the virtual column to update automatically with data available at the moment of the update.

i hope Appsheet will take some time to look into this,

Julie.