If you’re anything like me, you probably have a stack of SQL views just to simplify report writing. Since text entries are so pervasive to TMS’s operation, I find that I am constantly making use of them in various Crystal Reports. Here’s a time-saver: a SQL view that combines the four tables used for controlling text entries and handling notes.

CREATE VIEW [dbo].[CMP_BI_TextEntry] AS

SELECT Te.TextEntryID
    ,Te.TableID
    ,Te.ID
    ,Te.TextTypeID
    ,Tt.TextType
    ,Te.TextStatusID
    ,Ts.TextStatus
    ,Te.Purpose
    ,Te.TextEntry
    ,Te.TextDate
    ,C.ConstituentID
    ,C.DisplayName
    ,C.AlphaSort
    ,Te.LoginID
    ,Te.EnteredDate
FROM TextEntries Te
INNER JOIN TextStatuses Ts ON Te.TextStatusID = Ts.TextStatusID
INNER JOIN TextTypes Tt ON Te.TextTypeID = Tt.TextTypeID
LEFT JOIN Constituents C ON Te.AuthorConID = C.ConstituentID

In my opinion, this is one of the lost views that TMS should ship with.

Update for TMS 2010 and above:

Since TMS 2010, there have been some changes to text entries. Therefore, I thought it was about time to post an update.

CREATE VIEW [dbo].[CMP_BI_TextEntry] AS

SELECT Te.TextEntryID
    ,Te.TableID
    ,Te.ID
    ,Te.TextTypeID
    ,Tt.TextType
    ,Te.TextStatusID
    ,Ts.TextStatus
    ,Te.Purpose
    ,Te.TextEntry
    ,Te.TextEntryHTML
    ,Te.TextDate
    ,C.ConstituentID
    ,C.DisplayName
    ,C.AlphaSort
    ,Te.LoginID
    ,Te.EnteredDate
    ,Te.LanguageID
    ,L.Label
    ,L.Mnemonic
    ,L.ISO369v3Code 
FROM TextEntries Te
INNER JOIN TextStatuses Ts ON Te.TextStatusID = Ts.TextStatusID
INNER JOIN TextTypes Tt ON Te.TextTypeID = Tt.TextTypeID
LEFT JOIN Constituents C ON Te.AuthorConID = C.ConstituentID
LEFT JOIN DDLanguages L ON Te.LanguageID = L.LanguageID

Thanks for taking the time to read my post – I hope you found it helpful.
Leave feedback in the comments below.

If this post save you time, then consider sharing it with your friends and colleagues. If it saved you some money, then please consider buying me a coffee. If you want to know when I publish new content, then subscribe to my feed. Your support encourages me to write more. Thanks!