3

We have a private survey. The date the survey was completed is to appear in an Exported Excel sheet.

To do so, I have added a Date Field question in the survey named something like "Today's date" and the answer is populated with the current date.

My question is: How do I validate that the date hasn't been altered to make sure that nobody puts a past date to cover their as$es because they forgot?

I wanted to add an OR condition in the Validations such as :

OR([Date]>NOW(),[Date]<NOW())

But SharePoint is giving me a formula error. I've tried at the list level and at the question's level.

Edit: We DON'T have access to SharePoint Designer.

1

I believe what you need is to pull Modified date into the Excel, since it effectively shows the exact date when Survey was completed. And there's no way for a user to change it.

It can be done pretty easily with SharePoint Designer.

Open up SharePoint Designer, navigate to your Survey list, and click "Overview" under Views box, find <ViewFields> in the editor, and add the Modified field there. Like this:

enter image description here

Now, you can export again or even just refresh existing spreadsheet. Here's what you'll get:

enter image description here

The only thing left is to change the format of the Modified column into Date format, which is a very simple thing to do and I believe everyone can do it (because obviously that has to be done each time the data is exported).

enter image description here

And that's it, you're done!

Update: Without SharePoint Designer

If you don't have access to SPD, you can achieve exactly the same result using JSOM.

First, you'll need IDs of the list and the view.

There's an easy way to get those (any alternative way also will do). When you export to Excel, you're downloading file called owssvr.iqy. Instead of opening this file in Excel, open it in a Notepad or other text editor. You'll see something like this:

enter image description here

Copy out the GUIDs - ListView & ListName. Yep, ListName is a funny name for guid value, but in SharePoint it's OK :) Now, open you portal, then open your browser console (F12 in IE), copy-paste there the following script, replace PUT-LIST-GUID-HERE and PUT-VIEW-GUID-HERE with actual guids, and execute!

var ctx = SP.ClientContext.get_current();
var list = ctx.get_web().get_lists().getById('{PUT-LIST-GUID-HERE}');
var view = list.get_views().getById("{PUT-VIEW-GUID-HERE}");
var fieldsInView = view.get_viewFields();
fieldsInView.add("Modified");
view.update();
ctx.executeQueryAsync(function()
{
  alert('SUCCESS!');
},
function()
{
  alert('ERROR');
});

enter image description here

I tested this approach and got the SUCCESS alert, and after that Modified field appeared in the Excel after refresh.

  • Thanks for the answer, but I should've mentionned that we DON'T have access to SharePoint Designer. I upvoted your answer, i appreciate the effort Andrey. – Tommy Dubé-Leblanc Aug 27 '14 at 14:32
  • Thanks for the upvote! I believe the answer should still stay here as it might help other people who has same problem and has an access to SPD. Also I have some other ideas about how to achieve the same without SPD. I'll test them and hopefully come up with a solution. – Andrey Markeev Aug 27 '14 at 15:58
  • Ok, please see the updated answer. Hopefully it will work for you! – Andrey Markeev Aug 27 '14 at 16:28
  • We used another work around, checked if the date was > then today's date, if it was it's ok, if it's before then we had the error message. But your solution is amazing, i am gonna try it out too, thanks alot Andrey, great work. – Tommy Dubé-Leblanc Aug 27 '14 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.