Getting User Information w/ SPGetLastItemId

Jan 11, 2010 at 8:53 PM

OK, Marc,

Here's my dilemma: I want to use GetListItems to check another list to see if the current user has already created an item in that list with a matching field. How do I check a person column against the current user?

And then, how do I AND them together?

Basics

I have a list of Case Studies. For each Case Study, there is a link that asks the user to "Take the survey." The "survey" is not a SharePoint Survey, but rather, a List. The questions in the survey are basic: "Was this beneficial?" "Did you learn anything?" "Was the conclusion consistent with your analysis?"

The problem is that a given user can answer these same questions multiple times. I want to stop that.

Details

If the Survey list contains a record created by the current user for this particular case number, then I want to give a link to DispForm.aspx to see their previous survey results. If there isn't a record that matches that criteria, I want to give a link to NewForm.aspx so they can fill out the survey.

So, how do I get the user information? What format does it need to be in for the query to find it? How do I AND the user info and the case number columns in the Query XML?

Thanks!

Blessings,
Jim Bob

Coordinator
Jan 12, 2010 at 4:11 AM

Jim Bob:

I think that the $().SPServices.SPGetLastItemId function may be all that you need. It will return the last item ID that a user created on a list.  If the ID returned is 0, then the user doesn't have any items in that list yet.

M.

Jan 12, 2010 at 12:05 PM

Dear Marc,

The user can have multiple items in the list, but only one per case number. Will SPGetLastItemId accommodate that?

Blessings,
Jim Bob

Coordinator
Jan 12, 2010 at 12:35 PM

Oops.  No, it doesn't give you that extra filter, though that might be a nice little enhancement! Why don't I add that in for you?

M.

Jan 12, 2010 at 1:09 PM
Edited Jan 12, 2010 at 1:14 PM

That's a great idea! :) Of course, while you're at it, the ability to filter on multiple columns would be great.

It would help in this situation, but it could also be great on projects where a user can only have one "active" record at a time. If Status='Active' then don't let user create a new one until that one is changed to Status='Complete' (etc.).

Thanks so much for your responsiveness.

Blessings,
Jim Bob

Coordinator
Jan 12, 2010 at 1:58 PM
Edited Jan 12, 2010 at 1:59 PM

Jim Bob:

Just for you (but I'll put this into v0.4.8).  The way I've implemented this is consistent with the way the CAMLQuery option works in other functions. You can specify a CAML clause which will be Anded with the existing clauses to further filter the results.  Replace what you have in the library with this. I did a quick test, and it should work fine.

To call it, you'd do something like this:

lastID = $().SPServices.SPGetLastItemId({
    listName: "Survey List",
    CAMLQuery: "<Eq><FieldRef Name='Status'/><Value Type='Text'>Active</Value></Eq>";
   });

M.

 // Function to return the ID of the last item created on a list by a specific user. Useful for maintaining parent/child relationships
 // between list forms
 $.fn.SPServices.SPGetLastItemId = function(options) {
  var opt = $.extend({}, {
   webURL: "",    // URL of the target Web.  If not specified, the current Web is used.
   listName: "",   // The name or GUID of the list
   userAccount: "",  // The account for the user in DOMAIN\username format. If not specified, the current user is used.
   CAMLQuery: ""   // [Optional] For power users, this CAML fragment will be Anded with the default query on the relatedList
  }, options);
  var userId;
  var lastId = 0;
  $().SPServices({
   operation: "GetUserInfo",
   async: false,
   userLoginName: (opt.userAccount != "") ? opt.userAccount : $().SPServices.SPGetCurrentUser(),
   completefunc: function (xData, Status) {
    $(xData.responseXML).find("User").each(function() {
     userId = $(this).attr("ID");
    });
   }
  });
  // Get the list items for the user, sorted by Created, descending. If the CAMLQuery option has been specified, And it with
  // the existing Where clause
  var camlQuery = "<Query><Where>";
  if(opt.CAMLQuery.length > 0) camlQuery += "<And>";
  camlQuery: "<Eq><FieldRef Name='Author' LookupId='TRUE'/><Value Type='Integer'>" + userId + "</Value></Eq>";
  if(opt.CAMLQuery.length > 0) camlQuery += opt.CAMLQuery + "</And>";
  camlQuery += "</Where><OrderBy><FieldRef Name='Created_x0020_Date' Ascending='FALSE'/></OrderBy></Query>";
  $().SPServices({
   operation: "GetListItems",
   async: false,
   webURL: opt.webURL,
   listName: opt.listName,
   CAMLQuery: camlQuery,
   CAMLViewFields: "<ViewFields><FieldRef Name='ID'/></ViewFields>",
   CAMLRowLimit: 1,
   completefunc: function(xData, Status) {
    $(xData.responseXML).find("[nodeName=z:row]").each(function() {
     lastId = $(this).attr("ows_ID");
    });
   }
  });
  return lastId;
 };
Jan 12, 2010 at 2:12 PM

Once I've added it to the library, how do I min it?

Coordinator
Jan 12, 2010 at 2:16 PM

Try it with the unminified version to make sure it'll work for you.  If it does, I'll make a special minified version for you.  ;+)

M.

Jan 12, 2010 at 2:51 PM

I'm getting really close with this...

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" type="text/javascript"></script> 
<script src="/js/jquery.SPServices-0.4.6.js" type="text/javascript"></script> 

<script type="text/javascript">
$(document).ready(function() {
    var svyLink = $('td[id^=surveylink]');
    // alert (svyLink.html());

    var CaseNum = $('span[id*=ff2]').text();
    // alert (CaseNum);
    lastID = $().SPServices.SPGetLastItemId({
        listName: "CME Survey",
        CAMLQuery: "<Contains><FieldRef Name='Case_x0020_Number'/><Value Type='Text'>" + CaseNum + "</Value></Contains>"
    });
    // alert (lastID);

    if (lastID != '0') {
        svyHREF = "/site/Lists/CME%20Survey/DispForm.aspx?ID=" + lastID;
        svyLink.html("<a href='" + svyHREF + "'>" +
        "<img alt='' src='/images/quiz.jpg' border='0'/></a>" +
        "<strong>Survey completed</strong>" +
        "(<a href='" + svyHREF + "'>View answers</a>)");
    };
});

</script>

It's not getting the lastID, though, when it should. It's returning '0' even there's something there.

Blessings,
Jim Bob

Coordinator
Jan 12, 2010 at 2:59 PM

If you don't specify the CAMLQuery option, do you get a legitimate ID back?

M.

Jan 12, 2010 at 3:03 PM

No.

Is there something I need to do to update the library in cache or something?

Blessings,
Jim Bob

Coordinator
Jan 12, 2010 at 3:09 PM

No, the library should load dynamically on each page request.  Add the alert line below into the completefunc for the GetListItems call in SPGetLastItemId.  That should show you the XML which the Web Service is returning.  (Don't you just love live, remote debugging?!?!?)

completefunc: function(xData, Status) {
  alert(xData.responseXML.xml);
    $(xData.responseXML).find("[nodeName=z:row]").each(function() {
     lastId = $(this).attr("ows_ID");
    });


M.

Jan 12, 2010 at 3:14 PM
Edited Jan 12, 2010 at 3:21 PM

(Re: Live, remote debugging: Oh yeah! It's the best!)

Got this error: 

Cannot complete this action.

Please try again.

 

Blessings,

Jim Bob

 

PS: That's with CAMLQuery commented out (and the comma after the list name).

Coordinator
Jan 12, 2010 at 3:48 PM

If it's *with* the comma after listName, then you need to remove it:

lastID = $().SPServices.SPGetLastItemId({
        listName: "CME Survey"
        //CAMLQuery: "<Contains><FieldRef Name='Case_x0020_Number'/><Value Type='Text'>" + CaseNum + "</Value></Contains>"
    });

jQuery will barf if the last option has a comma.

M.

Jan 12, 2010 at 3:51 PM
Edited Jan 12, 2010 at 3:53 PM

FOUND IT!

In your code, the bolded line below is wrong:

  // Get the list items for the user, sorted by Created, descending. If the CAMLQuery option has been specified, And it with
  // the existing Where clause
  var camlQuery = "<Query><Where>";
  if(opt.CAMLQuery.length > 0) camlQuery += "<And>";
  camlQuery: "<Eq><FieldRef Name='Author' LookupId='TRUE'/><Value Type='Integer'>" + userId + "</Value></Eq>";
  if(opt.CAMLQuery.length > 0) camlQuery += opt.CAMLQuery + "</And>";
  camlQuery += "</Where><OrderBy><FieldRef Name='Created_x0020_Date' Ascending='FALSE'/></OrderBy></Query>";

It should be:

  camlQuery += "<Eq><FieldRef Name='Author' LookupId='TRUE'/><Value Type='Integer'>" + userId + "</Value></Eq>";

Now it works like a charm!

Blessings,
Jim Bob

Coordinator
Jan 12, 2010 at 3:57 PM

Ooh, that's bad.  I'm sorry about that. My test was not a test; I just realized that I was pointing at an older version of the library on my test page. How embarrassing!  That's what you get for working with the developer on pre-alpha code, I guess!

So is it doing what you need?

M.

Jan 12, 2010 at 3:59 PM

Yes, it's perfect. Thanks, again!

Blessings,
Jim Bob

Coordinator
Jan 12, 2010 at 4:01 PM

Groovy! Thanks for the enhancement idea; it'll be in the next alpha of v0.4.8, so if/when you upgrade, you'll be all set.

M.

Jul 15, 2010 at 1:05 AM

I'm having a problem using it.

My document library has folders, and it doesn't return the last ID from the items inside the folders. 

Coordinator
Jul 15, 2010 at 4:50 AM
Edited Jul 15, 2010 at 4:52 AM

Bobby:

It sounds like you need the capability to pass in the queryOptions parameter so that you can set the ViewAttributes to allow Scope="Recursive". I'll add this to the list of enhancements for v0.5.7. However, if you need something to work sooner, you could just take a look at the SPGetLastItemId function and expand upon it yourself. If you do, pitch it back for inclusion here!

This goes on the assumption that you are using SPGetLastItemId directly. If you're using it in the context of SPRedirectWithID, then it'll be a little bit mlore complicated, but not horribly so, so let me know.

M.

Coordinator
Jul 15, 2010 at 4:53 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.