This walkthrough shows how to build an automated registration form that closes down registration if the seats in the class fill up.

I will say up front that this solution uses a lot of tricks and is somewhat advanced. But, it is a no code solution that uses InfoPath Forms, SharPoint Designer Workflows, Calculated fields and SharePoint lists.

If anyone has any suggestions for improvements to this solution, please feel free to comment.

Note: You will need SharePoint Designer 2010 and InfoPath Designer 2010 to complete this walkthrough

Create the Lists

1. Calendar List – use the out of the box Calendar list and add these fields
   1. Seats – Number field
   2. FilledSeats – Number field
   3. RemainingSeats
      1. Calculated Field
      2. Formula: =Seats-FilledSeats
      3. Data type returned: Number
   4. SeatsIncrement
      1. Calculated Field
      2. Formula: =FilledSeats+1
      3. Data type returned: Number
   5. Closed – Yes/No
      1. Default Value: No
   6. StaticID – Number
      1. Note: This is a Hack we have to put in because we can’t use the real ID field in a calculated column. We will use a workflow to fill it in correctly later. I am open to suggestions on a better way to do this
   7. Register
      1. Calculated Field
      2. Data type returned: Single line of text
      3. Formula

         
         =IF(Closed=TRUE,"Closed for registration",IF(RemainingSeats>0,"<a href='#' onclick='OpenLandLRegistration("&StaticID&")'>Register</a>","Class is Full"))
         

2. Attendees List – custom SharePoint list, add these fields:
   1. Username
      1. Single line of Text Field
      2. Required
   2. Meeting
      1. Lookup Field
         1. Get Information from – Calendar List created earlier
         2. In this column – Title field
   3. MeetingID – Single line of text field

Workflows

SetStaticClassID Workflow

The SetStaticClassID Workflow is really a hack because SharePoint won’t let you use the ID field in the calculated columns. All we are doing is setting a number field with the ID field every time the calendar item is created or modified. For our particular situation, this will work fine. However, don’t assume this technique will work in every situation you need to use the ID field in a calculated column.

Go to the Calendar List created earlier. Edit the Calendar List in SharePoint Designer (note: you will need SharePoint Designer 2010 to do this)

1. Choose List Workflow in the Ribbon of SharePoint Designer
2. Give the Workflow a good name – something like SetStaticClassID
3. Add an Action – “Set Field in Current Item”
4. Click the “field” link – choose StaticID
5. Click the “value” link and clickt the fx button:
   1. Data source: Current Item
   2. Field from source: ID

Your Workflow should look like this:

Save your work

Go back to the settings page for the Workflow. Set the Start Options for “Start Workflow automatically when an item is created” and “Start workflow automatically when an item is changed”

Save and Publish your workflow

Registration Workflow

The Registration Workflow is one of the main parts of this solution. This is the Workflow that will update the remaining seats in the calendar list when an Attendee register for the course.

Go to the Attendee List created earlier. Edit the Attendee List in SharePoint Designer.

1. Choose List Workflow in the Ribbon of SharePoint Designer
2. Give the Workflow a good name – something like Registration
3. Add an Action – “Update List Item”
4. Click the “this list” link in the action
5. Change the list ot the Calendar list created earlier
6. Click the “Add…” button
   1. Choose “FilledSeats”
   2. Click the fx button for the value
      1. Data source: Calendar
      2. Field from source: SeatIncrement
      3. Field: ID
      4. Value – click the fx button
         1. Data source: Current Item
         2. Field from source: Meeting
7. In the Find the list item section
   1. Field: ID
   2. Value – click the fx button
      1. Data source: Current Item
      2. Field from source: Meeting

Save your work

Go back to the settings page for the Workflow. Set the Start Options for “Start Workflow automatically when an item is created”

Save and Publish your workflow

InfoPath Form

The end user will eventually register through the InfoPath form on the Attendees list.

1. Go to the Attendees list in SharePoint
2. Choose to Customize the Form in the Ribbon (note: you will need InfoPath Designer 2010 to do this)
   
3. AutoPopulate the Title Field: In the title field of the form
   1. Right-click – Text Box Properties
   2. Default value – click the fx button
   3. Add this in for the Formula:

      concat(userName(), " registration for event number ", Meeting)
      

      Note: copy and paste will not work on this formula. You will have to delete the “Meeting” string in the formula and use the “Insert Field or Group” button instead and choose the Meeting field

4. Add a formatting rule – “Hide this control” to both the Title and MeetingID fields
   
   Note: You should also delete the label for these fields and move them to the bottom of the form and delete the empty table rows they were in. This will make these fields invisible to the end user, but they will still be there to bind to the SharePoint list
5. In the Username field of the form
   1. Right-click – Text Box Properties
   2. Default value – click the fx button
   3. Add this in for the Formula: userName()
6. Add a formatting rule to the Username field to “Disable this control”
7. In the Meeting field of the form
   1. Click the “Default Value” button in the Control Tools – Properties tab of the ribbon
   2. Default value – click the fx button
   3. Add this in for the Formula using the “Insert Field or Group” button: MeetingID
8. Add a formatting rule to the Meeting field to “Disable this control”
9. Publish the form
   

Querystring

In order to automatically set the the meeting in the InfoPath form, we will use a query string to the MeetingID field. There are a few problems to this:

1. You can’t send querystrings to InfoPath: Solution = use a Query String Filter Web Part on the SharePoint page
2. The Query String Filter Web Part can’t set a lookup field. Since our Meeting column is a lookup field, this won’t work. Thus, we created a MeetingID field. This is a hidden field on the InfoPath form that is purely used to set the Meeting lookup field in the form. The end user will never see this field

Go to the Attendees list on the SharePoint site

Right-Click on the “Add new item” link and choose to open in a new window or a new tab

Now you will be on the edit form page – click Site Actions – Edit Page

Add a Query String (URL) Filter field to the site

Modify the Settings and add

• Fiter Name – QueryString
• Query String Parameter Name – meeting_id

Connect the Query String (URL) Filter Web Part to the InfoPath Form Web Part

Use the “MeetingID” filed as the Consumer Field Name

Now when anyone comes to the new form of your Attendees list, it will be filtered by the querystring on the MeetingID field. The InfoPath form is setup to use the MeetingID text field to populate the Meeting dropdown box. This allows us to automatically pick the dropdown of the event the attendee wishes to register from.

Javascript

The last step to this is a javascript file. The javascript does two things:

1. Creates the dialog box javascript to open our new form. The call to this javascript method is in the calculated field on the attendees list to register
2. Turns the calculated field column into html (reference: http://sharepointjavascript.wordpress.com/2009/10/15/reformat-url-from-calculated-column-with-decent-clickable-link-text/)

Note: The solution below uses JQuery because it is the easiest way to loop through multiple elements on the client side. If you do not have JQuery in your solution then:

1. Download the latest version of JQuery (use a “minified” version) from here: latest version of JQuery
2. Upload the file to your SharePoint site. I usually like to use the Style Library library at the root of the site collection
3. Add a reference to JQuery on your page:

   <script type="text/javascript" src="/Style Library/jquery-1.7.2.min.js"></script>
   

   Note: consider adding this as the first line of text in a Text File and then add the rest of the Javascript below to that same Text File. Then you can upload the Text File to a library on your site (I usually use the Style Library or Site Assets Library for this) and you can reference the text file from a Content Editor WebPart on the page we are creating.

IMPORTANT: Please change the url in the javascript to point to your new form (remember, you can right click on the add new on the Attendees list and open in new tab or window). This will show you the path to the new form and the List GUID. Also, make sure you keep the meeting_id at the end as a query string parameter.

<script type="text/javascript">
function OpenLandLRegistration(meeting_id){
     var options = {
          url:"/Lists/Attendees/Item/newifs.aspx?List=034d3042%2D1f03%2D43cc%2D96fc%2Dc4ef0acfa92a&meeting_id="  + meeting_id,
          width: 750,
          height: 600,
          dialogReturnValueCallback: DialogCallback
     };
    SP.UI.ModalDialog.showModalDialog(options);
}

function DialogCallback(dialogResult, returnValue){  }

// Reformat the url in the calculated column
reformatCalculatedColumnUrl();

function reformatCalculatedColumnUrl(){
     $(".ms-listviewtable td.ms-vb2:contains('href')").each(function(){
       $(this).html($(this).text());
     });
}

</script>

Piecing it together

All you have to do now is add the Calendar list as a webpart to the page in SharePoint you want this at. Also add a reference to the javascript on that page (Quick tip: Add the above javascript to a text file in SharePoint, put a Content Editor WebPart on your page and reference the text file in the options of the Content Editor Web Part. Just make sure this Content Editor WebPart is underneath the Calendar list webpart)

Edit the Calendar list webpart and choose the default view (this should be the All Events or Current Events view depending on yoru requirements). Do not use the Default Calendar view (so that you can show the new register link). Modify the view you chose. Make sure the “Register” column is visible to the end users because that is the link they will click to register.

Now the end users can add a new meeting in the Calendar (make sure they know to set the amount of seats available). I also suggest putting a nice message on the InfoPath form telling the end user to Save the form to register for the class. Otherwise, it might not be intuitive for the end user to know to hit the Save button in order to register. You could also add a Register button that just saves the form if you want.