More Adventures with the Library

5 minute read time.
I have been investigating the list of documents that appears under the Company tab group. This is actually part of a set of investigations into the library entity that I am working on. So there will be a few more blogs about documents and the library in the near future.

I wanted to find out how to ensure that when the CRM user is in the context of a company the system limits the listing of documents to only library entries linked to that company and not to a person. I also want to know whether I can change the system to allow documents to be uploaded that are only linked to a company and not a person.

The Meta Data used.

In theory the Meta Data definition of a list is made up from entries in the custom_screenobjects and the custom_lists tables.

If we consider the Global Document List that appears under the My CRM area, then this has a definition in the custom_screenobjects table that shows that the list is based on the Library entity and uses for its columns the Library table rather than a view.



select cobj_name,cobj_entityname, cobj_targettable from custom_screenobjects where cobj_type = 'List' and cobj_entityname = 'Library'



cobj_name, cobj_entityname, cobj_targettable

LibraryGrid, Library, Library
GlobalLibraryList, Library, vLibrary
ssLibraryList, Library, vLibrary

The interesting thing here is that the list that is used in the company and person contexts is called LibraryList and is visible in the Administration Area but it doesn't have an entry in the custom_screenobjects table.



The LibraryList does have entries in the custom_list table and when we look at the SQL log we can see very clearly that it thinks it is using Meta Data from the custom_screenobjects table and is associated with the vLibrary view.



select count(*) as fcount from vLibrary WHERE Libr_CompanyId=28 AND COALESCE(Libr_Global, N'') <> N'Y' AND ((COALESCE(Libr_Private, N'') = N'') OR ((COALESCE(Libr_Private, N'') <> N'') AND (Libr_UserId = 1)))
select TOP 11 * from vLibrary WHERE Libr_CompanyId=28 AND COALESCE(Libr_Global, N'') <> N'Y' AND ((COALESCE(Libr_Private, N'') = N'') OR ((COALESCE(Libr_Private, N'') <> N'') AND (Libr_UserId = 1))) ORDER BY Libr_UpdatedDate DESC , Libr_LibraryId DESC
select cobj_customcontent from custom_screenobjects where cobj_name = N'LibraryList'



By default there is only one view created for the Library table when CRM is installed. This is the vLibrary view and this has the following SQL.



CREATE VIEW vLibrary AS SELECT Library.*, libr_userid as cmli_comm_userid, communication.Comm_SecTerr, communication.Comm_ChannelId, communication.Comm_CreatedBy FROM Library LEFT OUTER JOIN communication on comm_communicationID = libr_communicationID WHERE Libr_Deleted IS NULL



The same list (LibraryList) is used for the listing of library items in the context of companies and in the context of persons. The same view (vLibrary) underlies that list. If we were to alter the view we would alter it for each usage.

Context is used to change the where clause that limits the rows returned. So when we are in the context of the company the SQL generated by the LibraryList looks like:



select TOP 11 * from vLibrary WHERE Libr_CompanyId=28 AND COALESCE(Libr_Global, N'') <> N'Y' AND ((COALESCE(Libr_Private, N'') = N'') OR ((COALESCE(Libr_Private, N'') <> N'') AND (Libr_UserId = 1))) ORDER BY Libr_UpdatedDate DESC , Libr_LibraryId DESC



and when we are in the context of the person, it looks like



select TOP 11 * from vLibrary WHERE Libr_PersonId=30 AND COALESCE(Libr_Global, N'') <> N'Y' AND ((COALESCE(Libr_Private, N'') = N'') OR ((COALESCE(Libr_Private, N'') <> N'') AND (Libr_UserId = 1))) ORDER BY Libr_UpdatedDate DESC , Libr_LibraryId DESC


The only thing that is different between the 2 statements is that in the person context the SQL has a clause that pulls up the records belonging to the person context and when in the company context the SQL pulls up records linked to the company.

Creating the New Library Item



We can see that when a document is created in the context of a company using the default screens the webpicker is showing the document to be uploaded will automatically be linked to the Company and that company's default contact.

The webpicker screen is actually set to be readonly. This has been done in the dll and not in Meta Data.

The evidence so far...

What we have seen so far is that, by default, the upload of documents into the Libary assumes that the document to be created will automatically be linked to the default person.

We have also seen that when listing out the library items in the context of the company the system is concerned to list out any record linked to the company and no check is made to see if the record is only linked to the company.

We can see that if we altered the view to list only those records associated with the company then we would break the listing of library items under the person context because the same view is used.

Our business requirement is:

To ensure that when the CRM user is in the context of a company the system limits the listing of documents to only library entries linked to that company and not to a person. I also want to know whether I can change the system to allow documents to be uploaded that are only linked to a company and not a person.
If our requirement is to allow documents to be uploaded only linked to a company and not a person then we would have to override the default behaviour that is to link the document to the company default person.

This would basically require setting the libr_personid field to be null during the upload of the document.

We could possibly do this using clientside code in the custom_content of the webpicker screen, or we could do this in a tablelevel script as the data is inserted.

But if do this then we have to also code in such a way as would not break the behaviour of the upload of documents in the person context.

This leads me to conclude that I would solve this problem by creating an application extension that was designed to control the upload of documents into the company area and their listing according to the exact business requirement.

In Sage CRM becomes quite an easy option to provide an alternative upload action using the ButtonGroups feature as we can see from the screen shot below.


This is an example of where it is easier to add an application extension rather than change the existing functionality.