Hi everyone,

 

I have a form that tracks participants in a treatment program, and depending on what phase they're in would like to filter a view that shows which participants are scheduled to report in for the week.  Phase 1 = every Thursday (every week), Phase 2 = Every 2nd and 4th Thursday (every two weeks), Phase III = Every 3rd Thursday (once a month)

 

I was hoping to see if there's a way that I can create a formula to calculate who will be on calendar for the week, something that would result in On_Calendar = 1 for a participant who is scheduled to report in, and = 0 when off calendar.  Here is the condition I was considering, I just don't have any experience with Deluge and don't know exactly how to script it:

 

When the field "Phase" is entered or updated -

 

Create new formula field called On_Calendar and set it to 0

 

1. If Phase = 1
2.      On_Calendar = 1
3. Else
4.      If Phase = 2
5.           If (zohocurrentday > 7 and zohocurrentday < 15) or (zohocurrentday >22 and zohocurrentday < 32)
6.                On_Calendar = 1
7.           Else
8.                If Phase = 3              
9.                     If (zohocurrentday > 14 and zohocurrentday < 21)
10.                          On_Calendar = 1
11.                     Else
12.                          On_Calendar = 0
13.                Else
14.                     On_Calendar = 0
15.      Else
16.           On_Calendar = 0

 

With this code, I'd be able create a view that filters all Participants who are scheduled to report for the week (On_Calendar = 1)

 

I did it this way, using day of the month, because it didn't look like Zoho would be able to provide week in the month.  I'd schedule the view to be emailed to the Admins every Sunday, hoping that the weeks will line up right.  If there's a better way, I'm completely open to any ideas.  Any help would be greatly appreciated.  Thanks in advance.

 

Haj