Client Pay Portal
 kentico developer code

Building an Event Schedule with Universal Viewers in Kentico

Building calendars and events lists are a common requirement we encounter and often the solutions we put in place are less than ideal but get the job done. In this post, I will cover two different approaches using Universal Viewers that we have found to be the best solution.
 

The Requirement

The need is usually to display a list of events that span multiple days and group them by day, with a header for each day. A common use case for this is conferences or expos where you have multiple sessions over the course of a multi-day event. The desired output is typically something like this: 
event schedule example front-end
 

The Usual Approach

A common way to address this need is to create a document or folder for each day and then place the events under the correct day. With this approach, you can use a Hierarchical Repeater to accomplish the desired output because you have a true parent/child relationship. This will work fine, but it is less than ideal for a few reasons. First, you end up with unnecessary “day” documents that offer no value other than to assume the parent role. This will cause empty pages unless you create some specific usage for the individual “day” pages, or you employ redirects on each day. This leads to the second problem, which is the unnecessary extra steps for content editors to get the hierarchy correct. Because the more steps you add, the more room there is for error, this presents the third problem of enforcing the correct output. 
 
There is a better way through Universal Viewers, and there are two approaches you can take but first let's talk about Universal Viewers and Hierarchical Repeaters. Universal Viewers and Hierarchical Repeaters require a single table of data which has columns that identify the parent/child relationship or the rows as well as columns to identify what level in the hierarchy each row is. They are most commonly used for documents where each document has a NodeID, NodeLevel, and NodeParentID. In this case, the NodeID is itself and the NodeParentID is the NodeID of its parent document and the NodeLevel tells which level within the document tree the node exists. Through this, you have a document at each level necessary to use Hierarchical Transformations. This would work well if using the approach of creating a document for each day of the event but as we discussed, that is not really the best approach. Instead, content editors should be able to create the events or sessions underneath the parent document and set the date and times on each document without needing to take the extra steps to build the daily hierarchy. What we need to do is build the hierarchy ourselves by defining our own columns to use for ID, ParentID and Level. Here are two different ways to do that.
 

Using the Universal Viewer with Custom Query

With the Universal Viewer with Custom Query, you can control the data that is used for the hierarchy by writing your own query. For the purpose of this example, we'll assume we have a custom page type for Sessions with Title, StartsAt, EndsAt, Description and Location columns. Now, to build the hierarchy that is needed for day/session relationship, we will need to add three columns. These are DayID (this will be the parent ID in the relationship), StartDay (this will be item ID in the relationship) and Level (this will be the level identifier). For the individual sessions, we will set DayID to null since they will have no children, we'll set Level to “1” since this a two level relationship, and we'll set StartDay to the date part of the datetime value stored in StartsAt. The query should look like this so far:

SELECT null as DayID
    ,Title
    ,Description
    ,Location
    ,StartsAt
    ,EndsAt
    ,CONVERT(date, StartsOn) as StartDay
    ,1 as Level
FROM bw_Session


Now we have the session data as we need it, but we are still missing the parent data. To generate that, we will need to create a new dataset and then aggregate them together through a union. This new dataset will set DayID to the distinct date parts of the StartsAt values for each record which will then create the parent/child relationship with the session data by having a distinct DayID that matches the StartDay values for each session record. We will also set Level to “0” since these are the first level in the hierarchy. We will set all other columns to null since they are not important for the parent records. The query now looks like this:

SELECT DISTINCT CONVERT(date, StartsOn) as DayID
    ,null as Title
    ,null as Description
    ,null as Location
    ,null as StartsAt
    ,null as EndsAt
    ,null as StartDay
    ,0 as Level
FROM bw_Session
UNION ALL
SELECT null as DayID
    ,Title
    ,Description
    ,Location
    ,StartsAt
    ,EndsAt
    ,CONVERT(date, StartsOn) as StartDay
    ,1 as Level
FROM bw_Session


Now we have the data necessary to generate the desired output through Hierarchical Transformations. However, we still need to fulfill the requirement of display all sessions under the current document. To do this, we will need to join our data with the View_CMS_Tree_Joined data. We also need to add in the ##WHERE## and ##ORDERBY## macros so that we can control those through the web part properties. The final query should look something like this:

SELECT DayID
    ,Title
    ,Description
    ,Location
    ,StartsAt
    ,EndsAt
    ,StartDay
    ,Level
    ,NodeAliasPath
FROM
(SELECT DISTINCT CONVERT(date, StartsOn) as DayID
    ,null as Title
    ,null as Description
    ,null as Location
    ,null as StartsAt
    ,null as EndsAt
    ,null as StartDay
    ,0 as Level
    ,null as NodeAliasPath
FROM bw_Session s
INNER JOIN View_CMS_Tree_Joined d ON s.SessionID = d.DocumentForeignKeyValue
WHERE ##WHERE##
UNION ALL
SELECT null as DayID
    ,Title
    ,Description
    ,Location
    ,StartsAt
    ,EndsAt
    ,CONVERT(date, StartsOn) as StartDay
    ,1 as Level
    ,NodeAliasPath
FROM bw_Session s
INNER JOIN View_CMS_Tree_Joined d ON s.SessionID = d.DocumentForeignKeyValue
WHERE ##WHERE##) as x
ORDER BY ##ORDERBY##


That’s it! You can set the where condition to NodeAliasPath LIKE 'CurrentDocument.NodeAliasPath%'(CurrentDocument.NodeAliasPath would need to be a macro in this case) to get the sessions under the current document and order by StartsAt. Now we have the query that gets a single table of data with parent/child relationship that we need to use the Hierarchical Transformations to produce the desired output. There is no need for unnecessary day documents and content editors can more efficiently create sessions and know they will display correctly.
 

Using the API and Basic Universal Viewer

The second approach is accomplished using a Basic Universal Viewer and creating the parent/child relationship through code instead of through SQL. This means you will need to build a custom web part unless you are using the ASPX template model. The first thing we need to do is add the control to the page like so:
 
<cms:BasicUniView ID="ScheduleUniView" runat="server"></cms:BasicUniView>

Then we need to start building our data source that we will bind to the Basic Universal Viewer.  The first step is to get a data set for our sessions like so:
 
DataSet ds = DocumentHelper.GetDocuments("bw.Session")
    .Path(CurrentDocument.NodeAliasPath, PathTypeEnum.Children)
    .OrderBy("StartsAt");


Now we need to add the three columns for the parent/child relationship: DayID, StartDay, and Level:

ds.Tables[0].Columns.Add("DayID");
ds.Tables[0].Columns.Add("StartDay");
ds.Tables[0].Columns.Add("Level");


We now need to loop through the rows in the table and set the DayID, StartDay and Level values. We will also need to track the distinct StartDay, so we will create a list to store those in:

List<string> dates = new List<string>();

//set the values on the new columns for the session documents
foreach (DataRow dr in ds.Tables[0].Rows)
{
    dr["DateID"] = "";
    dr["Level"] = 1;
    string startDay = dr["StartsAt"].ToDateTime(DateTimeHelper.ZERO_TIME, "en-US").ToShortDateString();
    dr["StartDay"] = startDay;
    if (!dates.Contains(startDay))
    {
        dates.Add(startDay);
    }
}

Next, we need to add a row to the table for each distinct StartDay to create the parent records. Just like with the SQL solution, we set the Level to “0” for parents and “1” for children:

foreach (string date in dates)
{
    DataRow row = ds.Tables[0].NewRow();
    row["DayID"] = date;
    row["Level"] = 0;
    ds.Tables[0].Rows.Add(row);
}


Our dataset is now complete, but there are a few other things we need to do before binding it to the Basic Universal Viewer. First, we need to convert it to a GroupedDataSource and define the item ID and level columns. Then we need to get and apply the Hierarchical Transformation. Lastly, we need to set the HierarchicalDisplayMode on the viewer and tell it which column contains the parent ID. Then we bind it.

if (!DataHelper.DataSourceIsEmpty(ds))
{
    GroupedDataSource gpd = new GroupedDataSource(ds, "StartDay", "Level");

    // Gets the hierarchical transformation from the system
    TransformationInfo ti = TransformationInfoProvider.GetTransformation("bw.Session.SessionSchedule");

    // Checks that the transformation exists
    if (ti != null)
    {
        // Checks that the transformation is hierarchical
        if (ti.TransformationIsHierarchical)
        {
            // Stores the structure of the hierarchical transformation into a HierarchicalTransformations object
            HierarchicalTransformations transformation = new HierarchicalTransformations("DayID");
            transformation.LoadFromXML(ti.TransformationHierarchicalXMLDocument);
            transformation.EditButtonsMode = EditModeButtonEnum.None;
            // Assigns the hierarchical transformation to the UniView control
            ScheduleUniView.Transformations = transformation;
        }
    }

    ScheduleUniView.HierarchicalDisplayMode = HierarchicalDisplayModeEnum.Inner;

    ScheduleUniView.RelationColumnID = "DayID";
    ScheduleUniView.DataSource = gpd;
    ScheduleUniView.DataBind();
}


That's it! We now have the desired output and again did not need to create unnecessary day document and deal with the pains of going that route. If this is going to be a web part, we would probably want to expose some properties instead of hard coding some of these values but for they are hard coded here for simplicity of the example.

In Conclusion

These solutions were based around a session calendar, but the same solutions can easily be applied to other requirements and scenarios. Maybe you have a page type that has a Type or Category field, and you want to list all of these documents by Type/Category with a header for the Type/Category. You could use either of these same approaches from this article to meet that requirement.

I hope you found this information useful. I welcome you to use the comments below to share your thoughts and opinions.

Author

Wiz E. Wig, Mascot & Director of Magic
Wiz E. Wig

Director of Magic