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

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?

0 15 2,358
15 REPLIES 15

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.

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?

@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
Participant III

@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.

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
Participant III

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?

@John_Gardner really, I havenโ€™t seen thatโ€ฆ

@Grant_Stead Follow-up question to my original question, even if the conversation has moved elsewhere 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.

@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?

@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โ€ฆ

aville
Participant I

i have the same problem, any progress during 2 years?
do i need to edit some columns to update app formula, why it doesnt updating automatically?

That is the design of AppSheet with respect to App Formulas. The are meant to only change values when an Edit has been made by a user in the app itself.

If you need a value to change automatically when OTHER changes are made, you cannot use App Formulas. Instead you would create Actions to apply those changes. That Action can be called from the UI such as on a Form Saved event or by using a Workflow.

Hello,
How can i find โ€œForm savedโ€ event?

First, you would create an action (or set of actions) that performs your desired function.

Then you select it as the Form saved Event/action. To see this location you will need to expand the Behavior section inside of the Form view. See image.

Reactivating this thread as I have found a (less than desirable) solution.

I have just created an action that does a data change on a dummy column within every single row in the table(s).

Itโ€™s a horrible work around, but a workaround nonetheless.

Top Labels in this Space