Display attendee count in event registration detail

Eric Ogburn asked on November 12, 2015 00:20

I'd like to get the number of attendees registered for a booking event so I can display a "seats left" message on the event detail page. Is this possible to do within a transformation?

Recent Answers


Laura Frese answered on November 12, 2015 02:52

This is a bit of a challenge since the Event capacity is stored in the booking event page type (CONTENT_BookingEvent) and the event attendees are in the Events Module (Modules> Events > Classes > Event Attendee - data stored in the Events_Attendee table). However, I found a way to do it that doesnt require any programming and is kind of a hack. If there is a better way of doing it I would really like to know.

1) Create a custom query in the Event booking system page type:
SELECT (##COLUMNS##) AS Difference

2) Create a Transformation that looks kind of like
Seats Left: <%# Eval("Difference") %>

3) Add a Repeater with Custom Query web part to the page you want to show the spots left on. Select the custom query you created in the Event booking system page type. Click on the black triangle next to the "Selected columns" field to open the macro editor and enter the following (SELECT EventCapacity from View_CONTENT_BookingEvent_Joined WHERE NodeID = {%CurrentDocument.NodeID#%}) - (SELECT COUNT(*) FROM Events_Attendee WHERE AttendeeEventNodeID = {%CurrentDocument.NodeID#%})

4) Select the transformation you created to display the seats left.

Thats it. The COLUMNS macro is being used as a place holder for our calculation query that gets the event capacity from the booking event table and the number of attendees from the event attendee table, which are matched by the Event NodeID and finds the difference.

You could also create a custom transformation method to get the data, do the calculation, and return the seats left. That way would be cleaner. More info on creating custom transformation methods here

0 votesVote for this answer Mark as a Correct answer

Eric Ogburn answered on November 12, 2015 04:43

Thanks Laura!

Your code works great, except I'd like to include it inside my existing CMS.BookingEvent.EventRegistrationDetail transformation.

Something like...

Event name: <%# Eval("EventName", true) %>
Capacity: <%# Eval("EventCapacity") %> 
Seats Left: <%# Eval("Difference") %>

When I use your code I don't get EventName and EventCapacity. Will I need a custom transformation method to accomplish this?

0 votesVote for this answer Mark as a Correct answer

Laura Frese answered on November 12, 2015 05:44

Thats probably the best way to go if you are comfortable with code. If you follow the instructions here and

1) Create a custom query in the Event(booking event) page type. I named mine seatsleft SELECT (SELECT EventCapacity from View_CONTENT_BookingEvent_Joined WHERE NodeID = @nodeid) - (SELECT COUNT(*) FROM Events_Attendee WHERE AttendeeEventNodeID = @nodeid) as Difference

2) your transformation method

public int SeatsLeft(object nodeid)
    {
        if (nodeid == null)
        {
            return 0;
        }
        else
        {
            int event_nodeid = Convert.ToInt32(nodeid);
            var query = new DataQuery("cms.bookingevent.seatsleft");
            QueryDataParameters parameters = new QueryDataParameters();                
            parameters.Add("@nodeid",event_nodeid);
            query.Parameters = parameters;
            DataSet ds_attendees = query.Result;
            if (ds_attendees.Tables.Count > 0)
            {
                if (ds_attendees.Tables[0].Rows.Count > 0)
                {
                    return Convert.ToInt32(ds_attendees.Tables[0].Rows[0]["Difference"]);
                }
            }
            return 0;
        }
    }

3) In your CMS.BookingEvent.EventRegistrationDetail transformation add

Seats left: <%# SeatsLeft(CurrentDocument.NodeID ) %>

4) If there are any issues, check the Event log to see if there are any errors

0 votesVote for this answer Mark as a Correct answer

Boris Pocatko answered on November 18, 2015 05:42

FYI, the above solution probably won't work on version 9, since we are removing page-type specific views to improve performance. It's much safer to create the query with our DataQuery API.

1 votesVote for this answer Mark as a Correct answer

Chris Isom answered on June 12, 2016 08:34

Boris,

Can you give me an example? I have a simular issue.

https://devnet.kentico.com/questions/eventattendeeinfoprovider-geteventattendeescount-kentico-9-example

Thanks, Chris

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.