Display attendee count in event registration detail

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|(identity)GlobalAdministrator%})

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

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?

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

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.

Boris,

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

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

Thanks, Chris