I need to run a calculation that compares two lists.
One list is fixed and the other list is being generated by the user. I need to compare these lists every hour for 24 hours a day. Once compared i set an action to copy the row to another table.
Can a virtual column run in the back and calculate all the time? Or any other way?
Hi @1111! I suspect that AppSheet doesn’t do this (but maybe we’ll be lucky and someone will prove me wrong). My experience is that actions in AppSheet need to be triggered by something that a user does.
Are you using AppSheet with Google Sheets? If so, you might be able to do what you want to do with Google Apps Script.
Hey Ami. Yes, you can create a column that will compare two lists - what are you trying to do?
Intersect() might be a good starting point:
Run a calculation every 24 hrs
For the part of triggering the calculation every 24 hrs, you’ll want to look into Reports - they’re workflows that run on a schedule.
So here is the thing.
I have this formula in a virtual column:
SELECT(PATROLS CONSTRUCT[CP ID],[PATROL ID]=[_THISROW].[PATROL ID])-SELECT(PATROLS[CP ID],[PATROL ID]=[_THISROW].[PATROL ID])
this produces a list of IDs.
however, i want my user to see the name of the ID (The label). when i try lookup, it only gives me one value even if i have a few IDs.
Moreover, once i have this figured out, i do need this to run every hour every day automatically so my user can see the results and doesn’t have to look for them as i would have a lot of data to sort through.
Reports now run once a day at the max, so i would need 24 reports?
I have thought about GS but as i may someday move to a SQL database, i rather find a solution within the app
If you’ve got that formula in a virtual column, it will be calculated every time the user makes a sync, opens the app, or edits certain elements (things connected to the formula).
If you would like this formula to NOT run constantly: you’ll need to make a physical column within your table to hold the variable, then create an action to perform the calculation, and finally set that action in a report so it runs on a schedule.
To show the label instead of the ID, pull the column that’s marked as the label instead of pulling the ID column.
Yes, to run the calculation every hour you can simply create 24 daily reports that are identical except for their scheduled time. I do this myself.
The video was very helpful to me. I didn’t realize that I could change data with a report. I had looked at it earlier but because I didn’t change the “Create report” setting to “ForEachRowinTable”, I never saw the “Change Data” option. Thanks a lot!
By the way, could you tell me how you embedded the video here? I haven’t been able to upload mp4 to this forum as I have done on the previous forum so I’m wondering what the trick is.
sorry for asking, my question is Appsheet is a way to “detect” the value change in VC EVENT on schedule report? My understanding was Appsheet is not able to detect the change in VC to be used as trigger for workflow and report.
Thanks, so i managed to get my name column to work.
As for the video you have posted, can you add screen shots? It was a bit too fast for me and i think this is exactly what i need
I make use of a SaaS I learned about from Santiago a few years ago www.CloudApp.com
Holy sh!t, if you need to be able to communicate what’s on your screen with easy, totally worth a look.
With their desktop app, I can take a screen recording, gif, screenshot, super easy - things are automatically uploaded to my cloudapp account, and the link auto-magically on my clipboard so all I have to do is paste.
Everything’s a link on my end, the super-crafty devs at AppSheet built the community to work with such things.
I only have a free account with CloudApp - so screen recordings are limited to 1 minute. Sometimes I have to record things 5 or 6 times to get it under 60 seconds.
But I like that; it makes me be concise in what I’m saying - efficient with the delivery (I’m a bit long winded at times and can ramble).
There’s a couple of different elements you’ll need:
- An action to actually make the data change
- A report to run that action on a schedule
As an example, I’ll show you how I built a system to do a detailed computational analysis of time-logs (something that’s really intensive to run). This setup differs a little from what I’ve listed above, because this app (like the one in my video example) has a 3rd element - a stack of actions to run instead of just a single one.
The first element: action to change data
In my case, a second action to group others together into one sub-routine:
The second element: report to run the schedule
Thanks! For some reason, your link didn’t work for me but I found this:
Learning of your cloud-base solution reminded me that I had asked @Fabian a similar question earlier.
It’s a different service but the same basic idea, I think.
The Report will simply be triggered every hour. When the Report is triggered you are responsible for detecting which records need to be processed and specifying the actions you wish to take.
For example, if you want to process only records that have been updated in the past hour, you might include a DateTime field in each record that reflects when that record was last updated. You could then write a Select expression to locate all recently updated records and process them.
The system is simply launching the Report on the schedule you specify. You are entirely responsible for everything that occurs thereafter. That includes identifying the appropriate records, if any, and deciding what actions to perform.