Solved: How to Auto Run a App Formula for all rows in a ta... - Page 2

TotalSolutions · 07-15-2021 03:50 AM

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.

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

View solution in original post

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