Daryl Rasmussen's Not-so-random Thoughts about SharePoint Here in my world

Recently I was helping a client put together a survey in SharePoint 2010, showing them all the various types of questions, and things you can do with them.  They were having a hard time visualizing it, so I ended up building their survey for them so they could see the overall effects in their desired question set.  About 65 questions in all, and to my dismay the “Next” button (which shows up at the top and bottom of the form if you have either a Page Separator field or if you implement branching logic in your questions) wasn’t working.

The first “page” of the survey would load, but when the “Next” button was clicked, that page would just re-load.  It would NOT move on to the next “page” (after the Page Separator or branch).  I searched everywhere on the internet for a possible reason.  Nada.  Nothing.  I can’t believe I’m the only person who ever encountered this.  And I certainly can’t show it to the client in this state.  WTF?

I thought maybe the client had re-ordered the questions in the branching logic, resulting in some kind of circular reference.  I painstakingly went through every question (hint – click the “Change the order of the questions” link from the Survey Settings page to see a summary of what is branching where).  No luck.  Everything seemed fine.

I then thought perhaps the use of special characters like quotes and apostrophes in the question title were mucking things up.  So I removed all of them (Changing questions like “Please enter any comments regarding the president’s address” to something like “Please enter any comments regarding the presidential address”).  No joy.  Still it would not leave page 1.

Then I thought maybe my survey list was corrupt.  Hey, lets face it.  Sometimes that happens with SharePoint.

So I rebuilt it (all 65 questions of it!) from scratch, stopping after every page separator question was added, and after every branch section was set up to test the new survey.  63 questions in, and everything is looking good.

The answer came when I added my 64th question (which was a plain text field for people to enter their phone number if they wanted to be contacted regarding their submission).  The fly in the ointment was that it was using the column validation capabilities of SharePoint to ensure the phone number was entered in a standard format.

The field was called “Optional – Phone” and had the following Column Validation formula:

=AND(LEN([Optional – Phone])=14,IF(ISERROR(FIND(“(“,[Optional – Phone],1)),FALSE,(FIND(“(“,[Optional – Phone])=1)),IF(ISERROR(FIND(“)”,[Optional – Phone],5)),FALSE,(FIND(“)”,[Optional – Phone],5)=5)),IF(ISERROR(FIND(” “,[Optional – Phone],6)),FALSE,(FIND(” “,[Optional – Phone],6)=6)),IF(ISERROR(FIND(“-“,[Optional – Phone],10)),FALSE,(FIND(“-“,[Optional – Phone],10)=10)),IF(ISERROR(1*CONCATENATE(MID([Optional – Phone],2,3),MID([Optional – Phone],7,3),MID([Optional – Phone],11,4))),FALSE,AND(1*CONCATENATE(MID([Optional – Phone],2,3),MID([Optional – Phone],7,3),MID([Optional – Phone],11,4))>1000000000,1*MID([Optional – Phone],2,3)<>911,1*MID([Optional – Phone],7,3)<>911,1*MID([Optional – Phone],7,3)<>555)))

It also had the following User Message:

Please enter the phone number in the format (###) ###-####

Do you see the problem?  I didn’t at first, but here’s what I now believe:

When the user is finished answering the questions on Page 1 and clicks next, the above validation rule fires, and fails.  The “Optional – Phone” field is on a different page that the user has not gotten to yet, so of course they haven’t filled the “Optional – Phone” field in yet.  And even though the field is not mandatory, the first part of the validation rule says that the length must be 14 characters.

The length at this point is still zero.  The User Message is probably being displayed under the “Optional – Phone” field, but since the field isn’t visible on the current page, the user gets no feedback and the “Next” button just reloads the current page.

Of course this is just a theory, because I can’t find anything in Microsoft’s documentation to say that this is what’s happening, however when I change the Column Validation formula to allow either zero or 14-character length strings, the problem goes away, so it seems a valid assumption.

For those who are interested, here’s the corrected formula:

=OR(LEN([Optional – Phone])=0, AND(LEN([Optional – Phone])=14,IF(ISERROR(FIND(“(“,[Optional – Phone],1)),FALSE,(FIND(“(“,[Optional – Phone])=1)),IF(ISERROR(FIND(“)”,[Optional – Phone],5)),FALSE,(FIND(“)”,[Optional – Phone],5)=5)),IF(ISERROR(FIND(” “,[Optional – Phone],6)),FALSE,(FIND(” “,[Optional – Phone],6)=6)),IF(ISERROR(FIND(“-“,[Optional – Phone],10)),FALSE,(FIND(“-“,[Optional – Phone],10)=10)),IF(ISERROR(1*CONCATENATE(MID([Optional – Phone],2,3),MID([Optional – Phone],7,3),MID([Optional – Phone],11,4))),FALSE,AND(1*CONCATENATE(MID([Optional – Phone],2,3),MID([Optional – Phone],7,3),MID([Optional – Phone],11,4))>1000000000,1*MID([Optional – Phone],2,3)<>911,1*MID([Optional – Phone],7,3)<>911,1*MID([Optional – Phone],7,3)<>555))))

After many days of cursing and frustration, the Survey is working again.

The moral of this story is  “Be careful with Column Validation in Survey Lists”.

That’s SP Here.  I would bet a case of doughnuts that the same is true in MOSS 2007, SP 2013, and O365.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Tag Cloud

%d bloggers like this: