Run calculation every hour

Ami
Participant V

Hello
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?
Thanks

1 22 1,786
22 REPLIES 22

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.

hi

I have thought about GS but as i may someday move to a SQL database, i rather find a solution within the app

MultiTech
Participant V

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:


Also see:


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.

Hi

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

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.

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 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:

  1. An action to actually make the data change
  2. 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

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.

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.

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.

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.

Hi Phil,
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.

Hi Koichi,

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.

Ferret
Participant V

Since Automation has replaced Workflows, does this mean we need to create 1 process, 24 events AND 24 bots?

I believe that would be correct.

I now have a scheduling app that uses a bot to run every hour. Could be great to have an option to just have one bot and select multiple time with that.

Also, i use a chrome add in that refreshes the page every few seconds, it triggers a sync even sometimes.

MultiTech
Participant V

@praveen talked about opening up further granularity to allow for hourly cycles.

I thought I remembered that, but was not sure. Such a feature would be put to good use on this end.

Thanks for the idea. That is to provide a browser client with much more frequently-updated data in the app they are using, correct?

Yes. When i have users in the office i want to provide them real time data without them needing to manualy sync every few minutes

Hi @Ferret You can use this add on:

So two separate things being dicussed here, I think โ€ฆ

  1. a way to make the app โ€œclientโ€ refresh more often in the background โ€” weโ€™ve discussed this a lot internally. Tough to do without making sync more efficient (less onerous on the backend store). We want to avoid having Drive quota exceptions and other such fun stuff happen to you
  2. a way to run scheduled automation bots on a finer granularity. Adding @prithpal and @Dan_Bahir to hear this requirement and determine if they can support this

This would be useful to run the BOT not on fixed date/time, but can run dynamically on schedule. Yes, it is potentially increase the load on AppSheet server, but this could be constrained by Automation Limit by putting limit for quota etc to reduce the risk?

Top Labels in this Space