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. ​

    • Related Articles

    • Copy a Custom Field into a Core Field

      Some systems don't have certain core fields and clients often create a custom field that they use instead. The following script will let you copy values from a custom field named 'Opened' into __M_Matters.Final_Date_Opened . You can adjust this ...

    • Create a "Legacy Matter Number" Custom Field

      Some firms rely heavily on Matter Numbers to search for a specific matter. Sometimes when they migrate to a new system it can't replicate the automatic numbering system from their old system so they want to renumber all their matters to a new scheme ...

    • Convert a Custom Field into a Practice Area

      --The system we are coming from does not have practice areas --however, the client has created a picklist custom field that mimics this. --We are going to convert this custom field into a practice area. --First, populate the practice areas table with ...

    • Create an "Employer" Contact from a Custom Field.

      In some systems, contacts do not have an Employer relation and only have a text field that represents the employer. This script will help when moving firms into platforms that allow an actual contact to be specified for the employer. --Create Company ...

    • Custom Fields: Creating Custom Fields via Queries

      Sometimes you may need to create a new custom field to handle an unmapped column in a database. The queries below will help you do that. -- This is the ID we are giving our custom field definition. DECLARE @FieldId NVARCHAR(MAX) = 'ConNo --- CUSTOM' ...