GetListItems not showing all columns in SharePoint

19 Apr
2013

I struggled with this for a while today. I was using SPServices’ GetListItems to pull data from SharePoint. I was using an external content type, and for the life of me could not figure out why the query was only pulling in some of my columns. The others just weren’t there. So I searched the web for a while, coming up with nothing that seemed to pertain to me. So I decided to look at my data. Turns out those columns didn’t have any data! So, in short, look at your data! I was using SPXmlToJson, as well as just the standard $.each() function to try to pull those columns; but with no data, nothing was coming back.

Bottom line: look at your data first!

1 Comment

Using Google Maps API with SharePoint, jQuery, and SPServices

17 Oct
2012

Ok, that title is a mouthful. But I just posted an article on Nothing But SharePoint detailing how you can use jQuery, SPServices, and the Google Maps API to make a fun SharePoint solution. Check out the video below and read jQuery + SharePoint Case Study: Google Maps and SPServices to see the full solution.

SharePoint Success!

15 Oct
2012

This is meant to be a little encouragement in the midst of your day of end users telling you SharePoint is “hard to use,” “counter-intuitive,” or any other expletive adjective of their choice. With a little stylistic care and a little luck, you can have successes that take you a long way down the road of End User adoption!

A couple of weeks ago I wrote an article briefly describing a scheduling system built in SharePoint using nothing but 3 lists, a standard publishing page, and some jQuery.

The goal: to direct parents of 700 students to the online scheduling system for Parent-Teacher Conferences. All bookings had to be performed in the 10 days leading up to the 2-day conferences. A parent could book one slot with each of their child’s teachers, and each slot was 20 minutes long from 12 noon until 6pm. Keep in mind, no signup of this kind has ever been done electronically at this school. Previously everything was on slips of paper delivered in backpacks…followed up by half a day of phone calls to the secretaries.

The potential concerns:

  • no ability to “train” parents how to use the system, it must be intuitive
  • a good portion of the parent population speak English as a second language, with mostly Chinese or Korean as a first/only language, and about 2 dozen other languages next to those.
  • all bookings must be made ENTIRELY online (no call-ins, paper signups or emails…for consistency)
  • everyone must book their “own” slots (teachers nor secretaries can book time slots for parents)
  • parents must be able to change their time slots at will
  • priority must be given in the following order:
    • Teachers (to block certain slots for coffee breaks, etc)
    • Teachers who have children at the school (to book slots for their children)
    • Parents with more than one child in the school
    • Parents with only one child in the school (everyone else)

So, on to the success!

As of this writing we’ve just finished Day 8 of the rollout, and we have 612 students who have been booked using the system! By my count that’s just under 88% of children in the school. Now you’re probably about to ask “so how many phone calls and emails did you get?” I don’t have an exact number, but I’d say we’ve received about 20-25 calls/emails from people with “issues.” So with a 96% success rate (4% margin of error) on this first-time rollout, I’m happy!

A breakdown of the issues we did encounter:

  • a glitch where about 4 double-bookings occurred
  • a bug that wasn’t allowing users on IE7 to proceed with bookings (a simple browser switch did the trick until the fix was applied)
  • a glitch where about 5 or 6 parents were able to book slots that the teachers had blocked out
  • several login issues (separate from the actual scheduling system)
  • 2 cases of “my children” not appearing because of data integrity issues (extra spaces in names being pulled from the SQL server)

All that to say, don’t give up on your SharePoint end users! If a system is necessary enough and simple enough, you can bring them on board faster than you may initially have thought.

2 Comments

SharePoint Scoreboard: A Case Study in jQuery on NBSP

15 Oct
2012

This is a bit delayed, but I’m way behind schedule on stuff! About 10 days ago I posted a 2-part series called SharePoint ScoreBoard: A Case Study in jQuery on NothingButSharePoint.

It covers creating a custom solution using jQuery, SPServices, and of course SharePoint 2010.

Head over to NBSP and check out the articles and let me know what you think!

Part 1 - SharePoint ScoreBoard: A Case Study in jQuery

Part 2 - SharePoint ScoreBoard: A Case Study in jQuery

SharePoint Sketches: SPServices Cascading Dropdowns

26 Sep
2012

I’ve been finding design solace in my notebook lately, and I’m realizing this is turning into a mini-series on sketched-out, front-end development in SharePoint. So for this part of the series I’m covering SPServices Cascading Dropdowns.

Although cascading dropdown menus have been a feature of SPServices for ages, I have never taken the time to sit down and walk through how it all works. As I was using it on a recent project I decided I needed to draw out what I was reading in the Documentation to make sure I understood. So, here’s how SPCascadeDropdowns plays out in my mind (descriptions below):

jQuery SPServices SPCascadeDropdowns

Description:

In this solution I’ve used SPServices Cascading Dropdowns to create a new “Student” list item. As well as storing the student’s name, the create form allows me to select a division (Elementary School, Middle School, or High School), and then select a teacher who is part of that division using cascading dropdowns.

Setup:

Create 3 lists:

  • Divisions
    • Column 1 – Title (one list item for each division)
  • Teachers
    • Column 1 – Title (for Teacher’s name)
    • Column 2 – Division (Lookup column from Divisions list)
  • Students
    • Column 1 – Title (for Student’s name)
    • Column 2 – Division (Lookup column from Divisions list)
    • Column 3 – Teacher (Lookup column from Teachers list)

Once you have your three lists, populate the first two (Divisions and Teachers) with your content.

Next, open up the NewForm.aspx (or EditForm.aspx) file for your list in SharePoint Designer. Look for the PlaceHolderAdditionalPageHead Content Placeholder. Put the following script inside that placeholder (assuming you’ve already loaded jQuery and SPServices elsewhere):

<script type="text/javascript">
$(document).ready(function(){
	$().SPServices.SPCascadeDropdowns({
		relationshipList: "Teachers",
		relationshipListParentColumn: "Division",
		relationshipListChildColumn: "Title",
		parentColumn: "Division",
		childColumn: "Teacher",
		debug: true
	})
})
</script>

Explanation of how script works

The trickiest part of SPservices and the SPCascadeDropdowns function is figuring out which columns to use where. So here’s the breakdown:

relationshipList: This is the list in your setup that contains both of the columns you eventually want to use in the cascade (but not the target list where the cascading happens). In this case it is the “Teachers” list because it contains both of the columns we’ll use.

relationshipListParentColumn: This is the “base” column from the relationshipList (“Teachers”), the one you will use to begin the cascading process. In this case it is “Division,” because we first select a division, and then select a teacher.

relationshipListChildColumn: This is the column from the relationshipList (“Teachers”) that comes after the base column has been selected. In this case it is “Title,” NOT “Teacher,” because remember we’re looking at the relationship list.

ParentColumn: This is the column in your target list (“Students”) (and therefore in the New and Edit forms) that will begin the cascade. In this case it is “Division,” because as I mentioned earlier it is the column that we select first to obtain the cascaded list of teachers.

ChildColumn: This is the column in your target list (“Students”) that will be altered based on what you choose in the ParentColumn. In this case it is “Teacher,” because we want to receive a drop-down list of teachers once we select a division.

Once you stop and look at it, everything seems to make sense. But it can be slightly confusing when your source tables all have similar column names because they are lookup columns.

Hope this helps!

2 Comments

SharePoint in Education: Case Study Sketches (jQuery and SPServices)

25 Sep
2012

Here’s a preview inside my notebook for how the new SharePoint Scheduling Assistant works. It’s been honed down to use 3 master lists instead of creating a new list for every teacher (70+ in this case). It’s built using jQuery, SPServices, and of course SharePoint 2010. Check out the more extensive case study to see how it was done. Hope to post more soon with even more details!

SharePoint, jQuery, and SPServices in Education

1 Comment

SharePoint and jQuery SPServices in Education: A Case Study

24 Sep
2012

This is a morphed, updated, renewed version of the SharePoint Scheduling Assistant. I’m not releasing this version quite yet, but this is a brief case study on how it’s working at a specific school.

Business Needs

The International School of Beijing needed a way for Elementary School parents to book time slots with teachers during bi-annual parent-teacher conferences.

The requirements:

  • Easy to use (training is impossible).
  • Integrate into the current intranet portal.
  • Sync with the database to omit any kind of manual setup for secretaries.
  • Deny parents the ability to book more than one slot with the same teacher.
  • Allow parents to book the same slot for two different teachers (in case mother and father come in and meet with 1 teacher each to save time)
  • If two or more parents are online at the same time, correctly queue the submissions so a double booking does not occur.
  • Allow teachers to pre-block out slots where parents cannot book times.
  • Allow teachers the ability to see their entire schedule, including which student is coming in during which time slot.

Solution (SharePoint, jQuery, and SPServices)

A SharePoint solution was built with jQuery to meet the requirements of the Elementary School. Let’s break down how the solution was created:

Part 1 – SharePoint

Within SharePoint three lists were created:

  • Bookings (Each reservation had its own row in this list that stored the details of who booked it, for which teacher, etc.)
  • Time Slots (A pre-determined list of time slots…in this case a list item was created for each 20 minute time slot from 12-6pm on Thursday and Friday Oct 18 and 19).
  • People (An external content type that pulled records from the database for each student, each of their parents, and each of their teacher names)

One of the trickier parts of this solution was grabbing the external content from the database in a usable way. Once it was in, we were able to work with the data. However, this list has 10,000+ items, so dealing with larger data like this was quite challenging. Enter jQuery.

Part 2 – jQuery and SPServices

The bulk of this solution was created with the jQuery and jQuery SPServices Libraries. Using jQuery, we stepped through the process like this:

  1. Get the details of the logged in user (parent) using SPServices
  2. Hit the SharePoint external data list once, filter it by Parent (matching to the Parent login ID), and store it in a javascript object for later use. This was the biggest strain on the system. We minimized the data call to only one time, but even still, pulling a list of 10,000 records even once isn’t super quick.
  3. Get all the children of the logged in parent, push them to a drop-down menu
  4. Get all the teachers of the selected child from #3, push them to another drop-down menu
  5. Get all time slots from the Time Slots list, push to a third drop-down menu
  6. Retroactively disable all time slot options that have already been booked (by searching through the Bookings list for records that match the time slot and the selected teacher)

Once the data was present, the parent could interact with it in two ways, book, and delete.

To book a time slot, the selected child, selected teacher, and selected time slot (along with a comments area) was submitted and saved to the list. To delete a time slot, a delete button is appended to each reservation in the on-screen schedule with the ID of the reservation as the ID of the link element.

The data from the Bookings list is checked once during the time slot selection process, and again during the form submit process to make sure no double bookings have occurred.

Benefits

This solution enables, for the first time at this school, parents to go online and manage their entire Parent-Teacher Conference schedule. Also, as an added bonus, if a mother and father both login, they can see the complete schedule for their child, even if only one of the parents made the bookings.

Once a parent books a time slot, they receive an email containing the details of their reservation.

An additional feature of this solution was that it was duplicated and tweaked for teachers to use a similar interface to pre-block out time slots where they did not want parents to come (ie, lunch, coffee break, going home). In that scenario the “one-block” restriction was removed for teachers, allowing them to customize when parents would be given the option to come in.

2 Comments