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.
Solved! Go to Solution.
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!!
If you are using the status to just visually alert the app users, you could instead explore using a virtual column for AMC Status.
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.
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!
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.
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!!
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
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.
Thank you!!
User | Count |
---|---|
56 | |
25 | |
13 | |
11 | |
6 |