Floating periodic calendar events without using recurring records

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • Answered
How can you create a floating calendar of events for the current month without using recurring records?  I'm trying to create a preventive maintenance calendar report that will show for the current month only.  Some maintenance event records are only quarterly or less frequently, and I'm struggling to identify a formula that will let me check if the Assigned Month matches the current month before calculating a Calculated Assigned Date (or none) for the calendar. 

I've tried creating an application variable to calculate Current Month of ToText(Month(Today())) but the formula only returns the formula text literally when I try to use Current Month as a variable in a subsequent field.  I tried using the conversion ToText, ToNumber  with the application variable in a formula for the report and this returned either the text or 0 instead of 2.

Table Fields I've set up so far are as follows:
Assigned Months is a multi-select text field with values like: All, Jan, Feb, etc.

Assigned Day of Month is a numeric value (e.g. 1, 7, 8, 15, 22, 30) corresponding to the day of month that's used in another formula to calculate the Calculated Assigned Date.

Calculated Assigned Date is FirstDayOfMonth(Today())+(Days([Assigned Day of Month])) //what is current month plus assigned day  but this doesn't allow me to calculate for calendar items that happen less frequently than monthly. 

Ultimately I want to create a Calculated Assigned Date if Assigned Months = All or the current month.
Photo of Amy

Amy

  • 52 Points

Posted 9 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
I think that you are very close.

Try this

var date MaintDateIfValid = FirstDayOfMonth(Today())+(Days([Assigned Day of Month]-1)); // subtract 1 so the 1st of the month is the first of the month

var text AssignedMonths = ToText([Assigned Months]); // convert multi select field to text to text

IF(
Contains($AssignedMonths, "All"), $MaintDateIfValid,

Contains($AssignedMonths, "Jan") and Month(Today())=1, $MaintDateIfValid,

Contains($AssignedMonths, "Feb") and Month(Today())=2, $MaintDateIfValid,


etc .....


Contains($AssignedMonths, "Dec") and Month(Today())=12, $MaintDateIfValid)
Photo of Amy

Amy

  • 52 Points
This is amazing and worked perfectly - thank you!  You rock!