Hello friends ! I have recently started getting rid of all the virtual columns i could in my apps, sparing only the ones having ref_row expressions, and replacing the rest with workflows that would recalculate the columns i need to recalculate only when it is needed, that worked great at first, until…
It became slow
Or at least slow enough to take more time than a sync does, so for example if an user made a change to a child row, a workflow would trigger an action that updates the value of a column in the parent row, but if he syncs manually after the change, the value is not updated.
That gives the impression to the user that the app is broken, since he synced a change and he’s still seeing an old value in the parent table, he won’t see the new value until he syncs again, which is problematic.
so here are the questions:
BONUS QUESTION: Would a webhook do better in this use case?
@Rafael_ANEIC-PY you’ve uncovered the major difference between workflows and actions
All your other questions are valid and solid, the answer is pretty much the same for all of them:
I imagine in those VCs you’ve got SELECT() formulas of some kind? In a well designed data schema, you’ll have very little need to make a hard-pull using SELECT() or LOOKUP() because the data you want, or the subset of records, already exists and it’s just a matter of calling what’s already there.
Awhile ago I put together a document outlining the key concepts I believe one needs in order to master the AppSheet platform; you can read it here.
You can read more about it here:
Side question don’t wanna hijack the post, but I see you as a quintessential Community approved expert for Appsheet. When you make these guides and outlines, I assume you use a gsheet based app as the example of your guides? Most of the skills are essential in any datasource but some do depend on what datasource is used for what is optimal. For example I use a MySQL database so I try to make any action I can into triggers since my database is exponentially faster than using actions or workflows when I need lots of changes.
I do; though I have clients that are utilizing SQL servers as their data sources, the majority of people using AppSheet would fit into some classification of Citizen Developer.
So I generally come from things from a Sheets stand point; but the best practices from the SQL-world still apply to the sheets world - they actually make a world of difference.
Good advice! Some items you need to update rapidly: sometimes I’m forced to create a “heavy” formula (some SELECT()
formula) in order to make a formatting rule apply faster, instead of waiting for the sync to happen.
I assume Appsheet apps work via webhooks to their own API so I would guess the answer is no.(Don’t quote me on this answer)
I could’ve sworn that Appsheet would self delay if you had a workflow running when a user syncs. We had a massive database trigger that was so poorly made it took 10 seconds to run and it was just added to the sync time of our app and wouldn’t finish syncing until the trigger had finished.
You might have to choose a blended approach and figure out a balance that will always show the most up to date information but be a bit slower than the fastest method. Some things you can also do to avoid this is making smart VC formula that return nothing essentially unless you are in a place where they are needed.
I should note: there is a place and use for both workflow and action-based data updates.
Another probably even more important factor is how expensive is the SELECT() and what constitutes a murdered sync time? I put some thought into what formulas I use for things but I generally don’t put any thought into SELECT() or LOOKUP()s. I don’t know if I just don’t the same level of data or if we just have enough of an expectation of it being slow to not worry. For our large more system level apps we are fine with 10-30 seconds of sync depending on how much manual syncing our users will need to do. Smaller lighter apps we try for 5-10 seconds cause they’re less of a I use this for 8hrs a day 40hrs a week and more I use this once a day.
Might sound counter intuitive that we want small apps to be faster at syncing but its the time spent in the app vs sync time that really seems to matter for our users.
Thanks for all the interesting answers guys, i’ll answer everything one by one
Actually no, i’ve managed to dump all my SELECT() formulas into my workflow template, the exact expression im trying to optimize is:
+SUM([Related items certificados][MontoCert])
+SUM([Related Anticipos][Anticipo])
-SUM([Related Anticipos][Devolucion])
-(SUM([Related Descuentos][Total gravado])/1.1)
-SUM([Related Descuentos][Total exento])
-SUM([Related Retenciones][Retencion])
+SUM([Related Retenciones][Devolucion])
there aren’t that many child records involved, but every parent row has several children tables, that’s what makes me so paranoid about just using it in a VC instead.
Got any links where i can learn more about those smart VC formulas? hehe
Yes, and that’s where my mistake is right now, i tried action based data changes but since i didn’t like having double updates i tried the workflow based data change approach thinking i could get almost the same result, my bad.
Well, at this point i feel like a sync maniac, my app has a pretty smol database as i’m still testing it yet, but i’m already thinking about how it would hurt to have a couple of virtual columns once i start hitting a few thousand rows.
pd:
This is an efficient way to do it.
No bad here…
This has been the same I’ve found as well, which has really caused me to overlook a lot of possibilities that can be had with workflows. But my users need their updates NOW, they don’t have time to wait for the app to sync - they just want to be able to use it - so I’ve focused on build methodologies centered around in-app updates.
(All this technical stuff… it’s like static noise to them, all they hear is KSHSHSHSHHSHSHSH)
Well then, to conclude our debate about virtual columns…
I can see myself going that path as well haha.
OH ya I refuse to use a SUM or COUNT formula at any possible step in appsheet if I can because they suck SOOOOO bad for sync time.
Is there an alternative?
Database views or calculating them when they change by assigning them to a real column.
My database knowledge level is potato right now
Hmmm, calculating them as they change…
How would you do that? as these are several different lists of child rows
Would basically be a workflow that triggers on add delete or update for any of the child rows and it would do an action on the parent to recalculate.
There might be a way to do it in gsheets formulas but we have never used gsheets.
This would be the action-based data-changes I was referring to.
Basically… you need to consider every scenario that would update that value and program some way to account for it - using parent-child ref updates and such - and make the appropriate updates when appropriate. lol
This helps
User | Count |
---|---|
40 | |
36 | |
32 | |
23 | |
16 |