How to Auto Run a App Formula for all rows in a table automatically

[TotalSolutions]

We take Annual Maintenance Contract (AMC) for computers and that is valid for 365 days.

I have 4 Columns:

AMC Start Date of Type Date

AMC End Date of Type Date

AMC Days (Difference between Todays Date and AMC End Date) of Type Number with App Formula ( IF ( [BT] = AMC Bill, HOUR ( TODAY() - [AMCStartDate] ) / 24, “” ) ) which means if the Bill Type (BT) is AMC Bill then find the difference in days of today and AMC Start Date

AMC Status of Type Yes/No

I have put this formula in App Formula of AMC Status Column: IF ( ( [AMCDays] <= 365 ), [AMCStatus] = NO, [AMCStatus] = Yes )

NO and Yes are written as Expired & Active in the Yes/No display Values

If AMC Days value is Less than or equal to 365 the AMC Status shows: Active and if it is more than 365 it shows Expired.

Above is working but I have to manually go to every entry edit it so that it refreshes

How to I refresh the AMC Status without manually clicking on edit on every entry?

For example every day at 12:01pm it should automatically check all entries and update the Status to Active OR Expired automatically by running that App Formula for every Row.

[Suvrutt_Gurjar]

If you are using the status to just visually alert the app users, you could instead explore using a virtual column for AMC Status.

Total_Solutions:

For example every day at 12:01pm it should automatically check all entries and update the Status to Active OR Expired automatically by running that App Formula for every Row.

If for some reason, you wish to AMC status in a real column, I believe this is possible with a bot with a daily scheduled event that runs at 12:01 AM/PM. The schedule event in turn will run a step that has a “data change action” of "set row values} type. You may need to have suitable conditions for the event so that the step in the bot does not run on a very high number of rows.

The action could change the [AMC Status] values. I believe scheduled events are available in Core plan pricing.

[TotalSolutions]

Thank you for your kind response!!

My App in in Prototype Stage so will I be able to use a bot?

I tried what you said, attached the snapshots of the Bot I am trying to create.
At the Process Level in the Condition to Check I have put the formula: ( HOUR ( TODAY() - [ODate] ) / 24 ) < 365 but when I click Test it does not show the results all blank eventhough it should.
Also in the Step under Set these columns the AMCStatus column is not listed eventhough in the Order table it is there! its weired!

Please help!

[Suvrutt_Gurjar]

@Total_Solutions ,

Total_Solutions:

My App in in Prototype Stage so will I be able to use a bot?

I believe your app needs to be deployed for the scheduled events to run and you will need to have “Core Plan”

Till such time you could test with “Run” button.

i believe you do not need the following “branch on condition” process.

Instead, I believe you could just use the following process ( as shown in 2 pictures below)

Process - Picture 1

Process - Picture 2

You could define a proper filter condition in the event setup as below that selects a few rows on daily basis to change the status. The below is just a sample. Please select your own filter expression as per your configuration and table, column names, etc.

[TotalSolutions]

Thank you so much for your help!! You are Fab!! God Bless!!

Finally with your inputs I made the below 2 Bots!!

How to Create AMC Status Update Single Entry Bot:

Bot Name: AMC Status Update Single Entry Bot

Event Name: AMC Status Update Single Entry Event

Event Type: Data Change

Table: Order

Condition: [BT] = AMC Bill

Data change type: ALL_CHANGES

Bypass Security Filters?: Enable

Appearance: Select an Event Icon.

Process Name: AMC Status Update Single Entry Process

Step Name: AMC Status Update Single Entry Step

Run a data action

Action to run: AMC Status Update Single Entry Actions

Set row values:

AMCStatus: IF ( HOUR ( TODAY() - [ODate] ) / 24 > 364, Expired, Active)

AMCDays: HOUR ( TODAY() - [ODate] ) / 24

Options: Bot Icon: Select the Bot icon you like.

How to Create AMC Status Update All Entries Bot:

Bot Name: AMC Status Update All Entries Bot

Event Name: AMC Status Update All Entries Event

Event Type: Schedule

For Each Row in Table: Enable

Table: Order

Filter Condition: [BT] = AMC Bill

Schedule: Daily Time: 12:01am

Time Zone: Indian Standard Time

Bypass Security Filters?: Enable

Appearence: Select an Event Icon.

Process Name: AMC Status Update All Entries Process

Step Name: AMC Status Update All Entries Step

Run a data action

Action to run: AMC Status Update All Entries Actions

Set row values:

AMCStatus: IF ( HOUR ( TODAY() - [ODate] ) / 24 > 364, Expired, Active)

AMCDays: HOUR ( TODAY() - [ODate] ) / 24

Options: Bot Icon: Select the Bot icon you like.

God Bless!!

[TotalSolutions]

Since I am in Prototype Stage you suggested that I run the BOT manually. I can click on Run from the Automation Bots option but if I want to run the same from the front end app is that possible?

Please let me know. Thanx

[Suvrutt_Gurjar]

For scheduled events , I believe one needs to deploy the app. I may sugegst you to look in the community’s “AppSheet Automation” section for anwers , queries related to AppSheet Automation. You are likely to find amswers there. AppSheet team members looking after the AppSheet automation also respond to queries in that section.

[Automation - Daily schedule](https://community.appsheet.com/t/automation-daily-schedule/43787) AppSheet Automation

Hi AppSheet team, I am trying to use the Automation feature to create reminder records in my app. The daily schedule does not seem to be triggering and the monitoring is not showing any activity. [appsheet.bot.event.daily.reminder] Steps to reproduce: create action test through user interface verify that action creates reminder records as intended create task, process and event to call the action once per day Observed a. no records created b. automation monitoring does not show any acti…

[TotalSolutions]

Thank you!!