Help wanted: Automatically populate a calendar with Jobs

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"

)

Hi Kamille,

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

IF(

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

   "active"

)

Nevermind, I fixed it, thanks for your help

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

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

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

All available timezones are listed here:

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?

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?

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.

IF(

   AND(

      FIND(DATETIME_FORMAT(SET_TIMEZONE(TODAY(), 'name of your timezone'), 'dddd'), {Active Days}), 

      {Done Today?} = "false"

   ), 

   "active"

)

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.

Kamille,

Now I want to create another form to request jobs. I tried making a form view almost identical to the one you have helped me with, but when as soon as I add the conditional rollup, it throws back a circular reference error. If you know any tips and tricks to get around the reference error, please let me know. Thanks for your help so far.

There isn’t enough information here to determine how or why you have a circular reference.

I should just ask, because I’m struggling. I can send a video explaining what I have, but it might just be easier to ask how’d you go about it. How would you request jobs to be cleaned?