0

I'm working with Sharepoint using CSOM and Javascript. I need to cache usage statistics in a List with one entry per user, updating values in a user's list entry when they interact with the site in a particular manner.

I'm wondering if it's possible to write a CAML query that will be smart enough to create an entry if a particular user doesn't already exist in the list, but also knows to update an existing entry instead if the user already has one going? I'm thinking something similar to this:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE 
name=VALUES(name), age=VALUES(age)

If CAML can't really handle something like this, am I restricted to making two async calls, one to verify the existence of a user in the cache list, and another to either create or update an entry?

1 Answer 1

0

CAML doesn't have anything built in for that, so you are limited to making two async calls with the JavaScript client object model.

The code snippet below checks a list called "Training Progress" for an item created by the current user. It updates it if it exists or creates it if it doesn't exist.

ExecuteOrDelayUntilScriptLoaded(
function(){
    var numRight = 12; /* dummy data */
    var clientContext = new SP.ClientContext.get_current();
    var list = clientContext.get_web().get_lists().getByTitle("Training Progress");
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml("<View><Query><Where><Eq><FieldRef Name=\"Author\" LookupId=\"TRUE\"/><Value Type=\"Lookup\">" + _spUserId + "</Value></Eq></Eq></Where></Query><RowLimit>1</RowLimit></View>");
    var currentProgress = list.getItems(camlQuery);
    clientContext.load(currentProgress);
    clientContext.executeQueryAsync(Function.createDelegate(this,
        function(){ /* List exists, grab the user's item if it exists */
        var itemEnumerator = currentProgress.getEnumerator();
        var itemId = -1;
        while(itemEnumerator.moveNext()){ 
            var item = itemEnumerator.get_current();
            itemId = item.get_item("ID");
        }
        if(itemId == -1){ /* Create item in list */
            var itemCreateInfo = new SP.ListItemCreationInformation();
            var newItem = list.addItem(itemCreateInfo);
            newItem.set_item('Title',_spUserId);
            newItem.set_item('Certification',numRight);
            newItem.update();
            clientContext.load(newItem);
        }else{ /* Update item in list */
            var item = list.getItemById(itemId);
            item.set_item('Certification',numRight);
            item.update();
            clientContext.load(item);
        }
        clientContext.executeQueryAsync();
    }),Function.createDelegate(this,
        function(sender,args){ /* List doesn't exist. */
                alert(args.get_message());
    }));
},"SP.JS");

You can even take this a step further and create the list if it doesn't exist, but that assumes the code is running under the context of someone with access to create lists.

1
  • Thank you for the example, it's not ideal but it's also not as bad as I thought it would be.
    – IAmKale
    Mar 2, 2015 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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