Linearize Notes

Linearize Notes

Some systems support note comments(notes-on-notes). On systems like this, you might see notes organized as follows:
  1. |-Matter ABC.001
  2. +-Note 1 (2021-01-01)
  3. +-Note 2 (2021-01-02)
  4. +-Note 2A (2021-01-04)
  5. +-Note 2B (2021-01-05)
  6. +-Note 2C (2021-01-06)
  7. +-Note 3 (2021-01-03)

The following script will detect notes on notes and move them directly under a contact or matter root.
This is necessary when restoring notes into systems that do not support note comments, however, you should advise your client that the hierarchical context will not be preserved so notes may render in a suboptimal order such as:
  1. |-Matter ABC.001
  2. +-Note 1 (2021-01-01)
  3. +-Note 2 (2021-01-02)
  4. +-Note 3 (2021-01-03)
  5. +-Note 2A (2021-01-04)
  6. +-Note 2B (2021-01-05)
  7. +-Note 2C (2021-01-06)

The Script
  1. --Convert Notes-On-Notes to be a linear list of notes
  2. DECLARE @RESULTS INT = -1WHILE(@RESULTS != 0) BEGIN UPDATE Child
  3. SET
  4. --Change the Child's parent to the Parent's Parent

  6. Child.Final_Parent_Id = Parent.Final_Parent_Id, Child.Final_Parent_Type = Parent.Final_Parent_Type
  7. FROM
  8. __M_Common_Notes Parent,
  9. __M_Common_Notes Child WHERE 1=1 --Only do this for Notes-on-Notes AND Child.Final_Parent_Id = Parent.Id AND Child.Final_Parent_Type = '__M_Common_Notes' --That have a "Root" note attached to one of the following type AND Parent.Final_Parent_Type IN ('__M_Contacts', '__M_Matters')
  10. SET @RESULTS = @@ROWCOUNT
  11. END

  13. GO



    • Related Articles

    • Split Big Notes

      Some destinations have a max character count for notes. The script below will split notes that exceed that length into multiple notes. The subject of the note will be {original subject} (Part X of Y) and the description will be the first X number of ...

    • Create Notes from Matter Descriptions

      Some platforms do not support matter descriptions. The following will transform a matter description into a matter note. --Transform Matter Descriptions to Matter Notes INSERT INTO __M_Common_Notes (Id, Final_Parent_Id, Final_Parent_Type, ...

    • Notes: Add Creator Information to the Body

      The script below will append the text 'Created By: {firstName lastName}', 'Created on behalf of: {firstName lastName}', and 'Updated by: {firstName lastName}' to the end of the note description in cases where the user's Email address was changed on ...

    • Audit Scripts: Row Counts and Progress Monitoring

      This article shows you a script that can be used to monitor the progress of both a Backup and a Restore. It can also be used to just review row counts on all the Universal Database tables. The first code block is used to populate a SQL Server Temp ...