Skip to main content
Question

Floating periodic calendar events without using recurring records

  • February 10, 2018
  • 0 replies
  • 48 views
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.
    This topic has been closed for replies.

    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)
    Terms & ConditionsAccessibility statement