Cascading calculations and sync

Kirk_Masden · 09-22-2020 08:02 PM

I have an action that occasionally writes incorrect data to the sheet when I test it as a single user. The following factors seem to be at play:

The action counts the number of virtual columns with a certain value, which in turn, depends on a LOOKUP() expression that looks up a value in another table.
The action can occur before a sync is complete

If the sync has not been completed before the action is invoked, an erroneous value is written. If, on the other hand, the sync has been completed, all is well.

One other possible factor is the lack of a parent-child between the tables. That was pointed out to me when I contacted support@appsheet.com and that led me to post the following:

I plan to contact support about this again but, in the meantime, I find it hard to believe that I’m the only creator for whom this is a potential issue. So, I thought I would ask about your experiences and opinions about this sort of matter.

My own feeling is that after a user changes a value, any value that is dependent on the changed value should not be written to the sheet in an action invoked by the same user until a recalculation has been done, with or without a parent-child relationship between tables. Would you agree? Please let me know if I’m missing something. Also, have any of you experienced similar issues with cascading calculations that occur before a sync?

Koichi_Tsuji

Hi @Kirk_Masden san,

I m not exactly sure what sort of arrangement you have been in place in terms of action, sync etc, but the issue sounds like the appsheet architect.

Action ; Initiated from client app, and then any value changes in back end will follow after that action event occurs in the app. so there is always delay in backend until they recognize the change and refresh the backend data.

Workflow & Sync : to the contrary to the action, they are intiated from backend side. For instance, the data change action is triggerd through the worklflow, the data change occurs first in the backend data base, once the operation is completed, then the change is pushted to the client app, so there is delay then change is made visible to the app users , as it happens at backend at first.

To ensure the action is always taking into account the most latest data, please try to trigger you action “through the workflow”.

Meaning, if you have normal appsheet action to change values etc, then dont fire the action at client first. Hide this action, and then push the mechanisum, i.e. to set another action to fire the target action through the workflow. For instance, you made number type physical column and make action to incrementally add number. Then you set workflow to listen to this change then fire the target action to change the value in that filed or other. By firing action the data change will be mde at backend side first, then it will return any VC calculated value at then, although there is a bit of time lag until the app user see and view the changes in the app.

I might be wrong, it will not help your current unique settings with your app, but worth trying ?

Kirk_Masden

Very insightful! Using a workflow action for this purpose hadn’t occurred to me.

I’ll investigate a bit more as to what has caused my problem. It seems to have something to do with a delay in the recalculation of virtual columns. If I learn anything new about this issue I’ll write in this threat again.

In the meantime, if anyone else has experienced problems with calculations involving virtual columns producing incorrect data when the sync is not complete, please share your experience.

Koichi_Tsuji

Sensei!

Let me raise my hand first.

I actually observed the issue. I suppose the issue you are mentioning here in this post is exactly like this?

I do have vc column in parent table to count the number of the child row.
In this demo, it start from 0.
Add child row.
VC return swiftly and immediately the number as 1.
Then go to child table, and remove (delete) record.
Back to parent detail view, it is still indicating the child row count as 1, instead 0, until sync is finished.

I dont think it happened before, recently start to happen!?

seems the virtual column reeveruation and calculation logic has been changed by Appsheet team, i.e. it is not starting from client, but starting from backend ? Obviously looks odd to me…

@Aleksi @Steve @TDhers

Any thought ?

@Kirk_Masden it is possible this is causing the mis-calculation you refer is happening because of this…

Aleksi

In my generic sample app the behavior is not the same. When I delete the child record, the count in both places are recalculated right away and sync is not needed. Let me test this little more if I could reproduce the same…

Aleksi

What ever I try, I’m not able to reproduce the same behavior. I made a new test app but it’s working just fine. I haven’t heard such a change either. We should probably investigate your app why it’s working like this.

Koichi_Tsuji

Obviously something is wron then.

I will email to support@appsheet.com , address to you, with my sample app details for investigation.

Aleksi

Thanks!

Koichi_Tsuji

Email is on the way.

Kirk_Masden

Thank you very much!

I will need to try to write to support@appsheet about this tomorrow. For now, I’d better go to bed . . .

Koichi_Tsuji

I was not early bird today, attending to mid-night Appsheet super interesting webiner last night!
Nigh, nigh.

To call for investigation to the deeper extend, please provide Aleksi with screenshot or short movie to share how your app behave now.

Kirk_Masden

Thanks!

Aleksi

it seems that the reason is coming from the REFROWS() when you are using slice as a source. In my test app, I was using original table as a source. We are working with a fix so it would work with slices as well without the need to sync.

Kirk_Masden

Yes, @Aleski. That sounds like my situation. My problem is on a table that is a slice. For your reference, I’ll send a message to support@appsheet.com in the next day or so describing my problem. My examples might serve as additional test cases.

Aleksi

When you do that, please assign it to me, thanks.