Copy Calendar Entry Dates into Custom Field Values

Copy Calendar Entry Dates into Custom Field Values

Some systems let you create custom fields that reference a Calendar Entry.
When moving custom fields into a system that does not support this, an easy workaround would be to turn these custom fields into DateTime custom fields by copying in the start date of the calendar entry.

The script below makes that happen.

  1. --The system we are going into does not support Calendar Entry Custom Fields.
  2. --As a workaround, we are going to copy the date from the calendar entry into the custom field value.

  4. --Copy the date into the custom field values.
  5. UPDATE
  6. __M_CustomField_Values
  7. SET
  8. Final_Value = V3.Final_Date_From
  9. FROM
  10. __M_CustomField_Values V1,
  11. __M_CustomField_Definitions V2,
  12. __M_CalendarEntries V3
  13. WHERE 1=1
  14. AND V1.Original_CustomFieldDefinition_Id = V2.Id
  15. AND V2.Original_Kind = '__M_CalendarEntries'
  16. AND V1.Original_Value = V3.Id

  18. --Update the definitions to be DateTimes instead of Calendar Entries
  19. UPDATE
  20. __M_CustomField_Definitions
  21. SET
  22. Final_Kind = 'DateTime'
  23. WHERE 1=1
  24. AND Final_Kind = '__M_CalendarEntries'
  25. ​