This time I'll blog about DMX 3. A complaint that reappears in the inbox is the inability to show custom attributes on the file list. It used to be possible, but the drawback was a performance penalty. Now it has vanished and some people have noticed this. So how can you go about fixing this? The solution is to (1) make it come back to the web server when it asks for a collection's contents, and (2) to make the web server display this. So lets start with 1. We assume we want to add a column called 'Cost' that points to an attribute called mycost and holds a numeric value.
The custom attribute data structure
A portal's custom DMX attributes are stored in a table called DMX_Attributes. In this table you'll find the ID and the name of the attribute among other things. The DMX content is stored in DMX_Entries. You'll find all collections, documents, and hyperlinks here. As you might have guessed, the values of an entry's custom attribute list are stored in a separate table aptly called DMX_EntryAttributes. This will be where the target data is we want to get to. So how does DMX get a collection's contents? Well, through the DMX_GetCollectionContents stored procedure:
ALTER PROCEDURE [dbo].[DMX_GetCollectionContents]
@AllVersions Bit, @ParentId Int, @PortalId Int, @UserId Int, @CanApprove Bit
AS
IF (@AllVersions=1 OR @CanApprove=1)
SELECT
DMX_Entries.*,
LTrim(RTrim(Users.FirstName+' '+Users.LastName)) AS FullName,
DMX_Extensions.ResourceFile,
DMX_Extensions.Icon16,
UserSubs.id,
UserSubs.LastAccess,
(SELECT COUNT(*) FROM DMX_Entries AS c WHERE CollectionId=DMX_Entries.EntryId AND c.EntryId=c.LastVersionId) AS Children,
(SELECT COUNT(*) FROM DMX_Entries AS c WHERE CollectionId=DMX_Entries.EntryId AND c.EntryId=c.LastVersionId AND c.EntryType LIKE '%Collection%') AS Subcollections
FROM ((Users RIGHT JOIN DMX_Entries ON Users.UserID = DMX_Entries.UserId) LEFT JOIN DMX_Extensions ON DMX_Entries.EntryType = DMX_Extensions.ExtensionKey) LEFT JOIN (SELECT * FROM DMX_Subscriptions WHERE DMX_Subscriptions.UserId=@UserId) AS UserSubs ON DMX_Entries.EntryId = UserSubs.EntryId
WHERE ((((DMX_Entries.EntryId)=([DMX_Entries].[LastVersionId])) OR (@AllVersions=1))
AND ((DMX_Entries.CollectionId)=@ParentId)
AND ((DMX_Extensions.PortalId)=@PortalId)
AND ((DMX_Entries.PortalId)=@PortalId))
ELSE
SELECT
DMX_Entries.*,
LTrim(RTrim(Users.FirstName+' '+Users.LastName)) AS FullName,
DMX_Extensions.ResourceFile,
DMX_Extensions.Icon16,
UserSubs.id,
UserSubs.LastAccess,
(SELECT COUNT(*) FROM DMX_Entries AS c WHERE CollectionId=DMX_Entries.EntryId AND c.EntryId=c.LastVersionId) AS Children,
(SELECT COUNT(*) FROM DMX_Entries AS c WHERE CollectionId=DMX_Entries.EntryId AND c.EntryId=c.LastVersionId AND c.EntryType LIKE '%Collection%') AS Subcollections
FROM ((Users RIGHT JOIN
(DMX_Entries INNER JOIN
(SELECT
MAX(Version) AS LastVersion,
LastVersionId
FROM
DMX_Entries
GROUP BY
LastVersionId,
PortalId,
CollectionId,
IsApproved
HAVING
(((DMX_Entries.CollectionId)=@ParentId)
AND (DMX_Entries.IsApproved=1)
AND ((DMX_Entries.PortalId)=@PortalId)))
AS EntrySelect ON (EntrySelect.LastVersion=DMX_Entries.Version AND EntrySelect.LastVersionId=DMX_Entries.LastVersionId))
ON Users.UserID = DMX_Entries.UserId) LEFT JOIN DMX_Extensions ON DMX_Entries.EntryType = DMX_Extensions.ExtensionKey) LEFT JOIN (SELECT * FROM DMX_Subscriptions WHERE DMX_Subscriptions.UserId=@UserId) AS UserSubs ON DMX_Entries.EntryId = UserSubs.EntryId
WHERE (DMX_Extensions.PortalId=@PortalId)
As you can see there are a number of fields that have not been used (yet) in the interface like 'Children' and 'Subcollections'. These can still be used in the display. Now for returning a custom attribute value we need to add this into this procedure. Basically the following would do: add a column like
CAST((SELECT MAX(ea.Value) AS Value FROM DMX_EntryAttributes ea INNER JOIN DMX_Attributes a ON ea.Attribute=a.id WHERE ea.EntryId=DMX_Entries.EntryId AND a.Name='mycost') AS REAL) AS mycost,
This will add a column mycost and look up the value of mycost for the entries returned. You should add this column in both 'SELECT's above of course.
Adding a column to the file list
The columns are defined in the resource file (so they can be localized and personalized on portal level) in a long string found under the key 'ColumnList.Text'. The columns are separated by semicolons and the various column properties are separated by pipe ('|') symbols. Note that over time the implementation of this has become quite complex and fault tolerance is not what it ought to be. This is why it can break quite quickly and I've not made much publicity for this 'feature'. It does allow me to tailor solutions to a wide range of customers. Now what we need to do is to dissect this resource entry and add the column. The default ColumnList.Text for the collection view is found in the file DesktopModules/Bring2mind/DMX/App_LocalResources/ViewCollection.asx.resx and reads as follows:
FileCheckBox|Select|24|||||||;TemplateIcon||24|||||~/DesktopModules/Bring2mind/DMX/images/View.gif|Details||EntryId||EntryId=[PARAMETERS]&Command=Core_ViewDetails|False||~/|Icon16||||;Description|Description||||True|Description||||||Default|;StatusLocked||24||||||Locked|File;StatusPrivate||24||||||Private|;StatusDeleted||24||||||Deleted|;StatusApproved||24||||||Not Approved|;StatusAttention||24||||||Attention|;ActionDownload||24||||||Download|File;ActionJump||24||||||Jump|Hyperlink;ActionJumpNew||24||||||Jump in new window|Hyperlink;LastModified|Last Modified|100||{0:d}||LastModified|||;Author|Author|150||||Author|||File;DateSubmitted|Created|70||{0:d}||DateSubmitted|||;FileSize|Size|70||||FileSize|||File
This would break down to the following columns:
FileCheckBox|Select|24|||||||;
TemplateIcon||24|||||~/DesktopModules/Bring2mind/DMX/images/View.gif|Details||EntryId||EntryId=[PARAMETERS]&Command=Core_ViewDetails|False||~/|Icon16||||;
Description|Description||||True|Description||||||Default|;
StatusLocked||24||||||Locked|File;
StatusPrivate||24||||||Private|;
StatusDeleted||24||||||Deleted|;
StatusApproved||24||||||Not Approved|;
StatusAttention||24||||||Attention|;
ActionDownload||24||||||Download|File;
ActionJump||24||||||Jump|Hyperlink;
ActionJumpNew||24||||||Jump in new window|Hyperlink;
LastModified|Last Modified|100||{0:d}||LastModified|||;
Author|Author|150||||Author|||File;
DateSubmitted|Created|70||{0:d}||DateSubmitted|||;
FileSize|Size|70||||FileSize|||File
In general the various properties break down as follows (starting at 0 for the first bit of information):
Parameter 0: Name (i.e. in DB)
Parameter 1: Title
Parameter 2: Width in pixels
Parameter 3: CssClass
Parameter 4: Template (for dates)
Parameter 5: Show link or not
Parameter 6: Sort Expression
Parameter 7: Header ImageUrl
Parameter 8: Alternate text for icon
Parameter 9: EntryType starts with
Parameter 10: Id Field
Parameter 11: Description Field
Parameter 12: Parameters
Parameter 13: Open in new window
Parameter 14: Image Url
Parameter 15: Image Url Prefix
Parameter 16: Image Url Field
Parameter 17: Image Url No
Parameter 18: Image Url Yes
Parameter 19: Visibility Field
There are a number of reserved names (i.e. parameter 0) for column generation: "Author", "Keywords", "OriginalFileName", "Remarks", "Version", "DateSubmitted", "LastModified", "Collection", "StatusDeleted", "StatusLocked", "StatusApproved", "StatusPrivate", "StatusAttention", "DownloadFileIcon", "ActionDownload", "ActionJump", "ActionJumpNew", "Description", "FileCheckBox", "FileSize", "TemplateLink", "TemplateIcon", "TemplateIconYesNo". These all use a custom parameter list so you want to avoid those. But for our purpose we can use a regular column. It would look something like this:
mycost|Cost|75||{0:0.00}||mycost|||File|||||||||||;
This is the definition of a column for mycost where the value is represented with the 0.00 pattern, 'Cost' is displayed at the top, it is 75 pixels wide, sortable on mycost, and only applies to entry types starting with 'File' (i.e. all document types and not collections or hyperlinks). This column can be stuck into your ColumnList.Text value at the place where you want to have this column and you should be able to see the stored value in the overview.
Note that this is how it is done for the regular 'collection view'.