Flatten Custom Object fields into Contact/Matter Fields

Flatten Custom Object fields into Contact/Matter Fields

Warning
WARNING:
Many systems that support custom objects allow multiple rows in custom objects.
Many systems that allow custom fields only allow one single value for each field.
When you use this script, if a matter has two values set for the same custom field, only one will be used.

  1. --Update the custom field definitions to link to contacts/matters.
  3. UPDATE
  4. __M_CustomField_Definitions
  5. SET
  6. Final_Parent_Type = V4.Final_RelatedTo_Type
  7. FROM
  8. __M_CustomField_Definitions V1,
  9. __M_CustomObject_Definitions V2,
  10. __M_CustomObject_Instances V3,
  11. __M_CustomObject_Instances_Related_Entities V4
  12. WHERE 1=1
  13. AND V1.Original_Parent_Type = V2.Id
  14. AND V2.Id = V3.Original_CustomObjectDefinition_Id
  15. AND V3.Id = V4.Original_CustomObjectInstance_Id

  17. --Update the values to have new parent types.
  18. UPDATE
  19. __M_CustomField_Values
  20. SET
  21. Final_Parent_Type = V3.Final_RelatedTo_Type,
  22. Final_Parent_Id = V3.Final_RelatedTo_Id
  23. FROM
  24. __M_CustomField_Values V1,
  25. __M_CustomObject_Instances V2,
  26. __M_CustomObject_Instances_Related_Entities V3
  27. WHERE 1=1
  28. AND V1.Original_Parent_Id = V2.Id
  29. AND V1.Original_Parent_Type = V2.Original_CustomObjectDefinition_Id
  30. AND V2.Id = V3.Original_CustomObjectInstance_Id
  31. a