App formula sync problem? I have a column in ...

(Stefanie Dietz) #1

App formula sync problem? I have a column in sheet B that was previously populated by a vlookup directly in the sheet, looking up a value in sheet A. Wanted to move this formula into the app. So, tweaked that column in the app table B to contain an app formula referencing the corresponding column in table A. Removed the formula in the sheet. When testing the formula, it shows the correct results. BUT whatever I seem to do, it does NOT update the column in the sheet! What am I doing wrong?

(Grant Stead) #2

App formulas only calculate when the row is actually updated. So a virtual column on the other hand that at formula will recalculate on the fly as needed.

If it’s important that the information in the application is correct you need a virtual column. However if you also need the information to be hard-coded and in the back end then you most likely will also need a a sheet formula.

(Stefanie Dietz) #3

Hmh using two columns (one virtual one not) is actually a good idea. Thanks. But just to understand this more: You say “App formulas only calculate when the row is actually updated”. I was under the impression that if a VALUE that is used in the app formula (even if it does not live in the formula’s row) changes, the formula is then recalculated. This is not true then, correct? The formula only re-calculates if anything changes in the actual row the formula lives in?

(Grant Stead) #4

@Stefanie_Dietz exactly. That’s how Excel/Gsheets works, but appsheet app formulas do not follow the path… It has to do with speed and bloat that a mobile app can’t handle… Yes, the app formula in a physical column only recalcs if the row is updated… And with our new reference actions you can force a recalcs through hosting every row… But if you can get it done with a virtual, that’s what I would do. Show the virtual for app users, and hide the sheet column… But then you at least have the data in the back end the way you want…

(John Gardner) #5

@Grant_Stead Can you explain “new reference actions you can force a recalcs through hosting every row”? I don’t think I’m familiar with this.

(Grant Stead) #6

So, you can create a physical helper column type number, initiate it on 1… And then it’s as simple as an action to change that [HelperColumn]+1 Then you make a ref action that has no real filter so that it executes the recalc +1 action on all rows… You can tack that action on the end of sequence actions, or right before navigation, etc… Doing so makes sure all of the app data is live and recalculated…

(John Gardner) #7

Every time I’ve done this on ~20 rows or more, Chrome gives a warning that the page is unresponsive and I have to specifically tell Chrome to “Wait”, sometimes multiple times.

Any ideas for workarounds on this?

(Grant Stead) #8

@John_Gardner really, I haven’t seen that…

(Stefanie Dietz) #9

@Grant_Stead Follow-up question to my original question, even if the conversation has moved elsewhere :wink: I have a “real” column for user input (afaik this is not possible with virtual columns, correct?). On this user input, a couple of calculations (all in virtual columns) is based, which then influences a filtered slice. In theory, should not everything “after” the user input be computed in the app and therefore be updated without the need for a sync? So far, the slice is only updated once the app has done it’s syncinc thing.

(Stefanie Dietz) #10

@Grant_Stead Just a thought… would it help if the slice consisted purely of virtual columns, not just filter for a value in a virtual column?

(Grant Stead) #11

@Stefanie_Dietz you know it’s funny, we’ve actually asked for entire virtual tables… Example uploading and storing images. I don’t care how appsheet organizes it’s business, and I don’t need the directory in my backend, so why can’t appsheet just handle it all without me needing physical columns lol…

Anyway, I still don’t think I’ve got my mind wrapped fully and how, why, and when appsheet syncs, and exactly how things are affected… Maybe @Aleksi_Alkio can help us out…