Help wanted: Automatically populate a calendar with Jobs

[Josh_MCA]

Hello Everyone,

I am looking for a script that solves a basic problem. I am running a cleaning service, and certain jobs(like glass cleaning, vacuuming, bathroom care) need to be done regularly on certain days of the week (for instance the fridge needs a once a week Fri clean). I filter out jobs based on the room and attach QR codes on the walls that will only show the active jobs in the room that day of the week. This way, I give my workers a set jobs and no one gets overwhelmed.

I’ve made my workspace simple (at least to me). I have three tables (jobs, people, building/room) and I have fields with in the jobs that specifies the day of week I want it cleaned. I want the day of week (multiple select) to turn job status to active on the appropriate day. On the not appropriate day, I want the jobs go to inactive.

There’s more work to do on the project than this, but this is a good benchmark. If any of you think you can help me let me know.

[Kamille_Parks]

If you only need a {Status} field to display “active” on the days it should be, you don’t need a script at all. Assuming you have a multiselect field that outputs something like “Monday, Tuesday, Wednesday”, you could add a formula field like:

IF(
   FIND(DATETIME_FORMAT(TODAY(), 'dddd'), {Active Days}), 
   "active"
)

[Josh_MCA]

Hi Kamille,

I tried loading a formula field with your line of code, and no luck yet. What is ‘dddd’?

[Josh_MCA]

Nevermind, I fixed it, thanks for your help

[Josh_MCA]

This is Great, except everything is in GMT, how can I switch to eastern time zone?

[Kamille_Parks]

Replace DATETIME_FORMAT(TODAY(), 'dddd') with

DATETIME_FORMAT(SET_TIMEZONE(TODAY(), 'name of your timezone'), 'dddd')

All available timezones are listed here:

Airtable Support

Supported timezones for SET_TIMEZONE

The SET_TIMEZONE function will allow you to set the timezone for the data from a date type field. This is written in the form "SET_TIMEZONE(datetime, 'timezone identifier')," in which the...

[Karlstens]

If a job is missed on Monday, would you need it to remain active and shuffle to Tuesday, and perhaps read as “Overdue”?

I’ve written a script recently that triggers each day (or week in my case) that counts down the days until a job is required to be executed. Upon that countdown, when the count reaches zero then the job status reports “Time to do job X”, and once the countdown passes zero into negative numbers then the job status reads “Job Overdue”. Upon the job completing, the status is set to “Job Done” and the counter then resets to the time until the job is again due next and begins the countdown all over.

Is this what you have in mind?

[Josh_MCA]

Yes, This is the what I’m eventually trying to get to. Here’s just a concern of mine,

I want to figure out a way for my cleaners to switch jobs from active to done from the readme phone view, but have no access to change any other fields. That way, your Overdue concept works really well. Do you know how I can do this?

[Kamille_Parks]

You could have a [Log] table that has a form view that cleaners fill out when they complete a job. The select a job from a link to another record field pointing at your table of [Jobs].

Then your table of jobs could have a rollup field called {Done Today?} that looks at all the linked [Log] table submissions to see if anyone has logged that job as done:

• Apply the conditional filter to the rollup where the {Date} field is today
• Use IF(values, "true", "false") as the aggregation formula

Then finally adjust your {Status} field’s formula to be:

IF(
   AND(
      FIND(DATETIME_FORMAT(SET_TIMEZONE(TODAY(), 'name of your timezone'), 'dddd'), {Active Days}), 
      {Done Today?} = "false"
   ), 
   "active"
)

This way your read-only users can technically mark the job as no longer active.

[Josh_MCA]

This is great Kamille, and it works well for now, but next question.

How reset the rollup values at the end of the day so next time the job is scheduled, it forgets old form submissions? Right now the jobs that are done will not show up the next day because it is registering still true for the rollup value.