Dynamic CAML query with SPFilterDropdown

Apr 28, 2011 at 2:47 PM

Marc,

I'm trying to implement a "dynamic" filter for my dropdown.  Here's the code that I have currently:

$().SPServices.SPFilterDropdown({
             relationshipList: "ProjectTeam",
             relationshipListColumn: "Title",
             columnName: "ProductLead",
             CAMLQuery: "<Eq><FieldRef Name='Project'/><Value Type='Text'>Entry Test for Liz</Value></Eq>",
             completefunc: null
            });

 

This works fine.  Instead of filtering on "Entry Test for Liz", I'd like to be able to pass in a parameter that I've set up within the dataview.  Can you point me in the right direction for this type of CAML query?

Thanks,

Liz

Coordinator
Apr 28, 2011 at 2:51 PM

Liz:

Script won't run in the DVWP because that runs server side. What you'll want to do is put the value into the markup that the DVWP emits somewhere so that you can use a selector to grab the value and then pass it as a variable in the CAML. Something like:

var myParameter = $("#myParameter").html();
$().SPServices.SPFilterDropdown({
             relationshipList: "ProjectTeam",
             relationshipListColumn: "Title",
             columnName: "ProductLead",
             CAMLQuery: "<Eq><FieldRef Name='Project'/><Value Type='Text'>" + myParameter + "</Value></Eq>",
             completefunc: null
});

M.

May 5, 2011 at 4:53 AM

Thanks for getting back to me so quickly.  It worked perfectly.

I have another semi-related question.  I'd like to be able to use the RequireUnique functionality in the library on a data view.  Is there a simple way to modify the code you've written to accomplish this?

 

Thanks again,

Liz

Coordinator
May 5, 2011 at 11:24 AM

What you'll need to do is make sure that the markup you emit in the DVWP matches what you would have on an out of the box form. In the case of SPRequireUnique, the function simply looks for:

$("input[Title='" + columnDisplayName + "']");

meaning an input element with the Title set to the columnName you pass in. If you're using the controls that SharePoint Designer generates for you in the DVWP, you should be all set. If you're using a different control, then you need to make sure it emits a similar element.

M.

May 6, 2011 at 12:15 PM

Is this what my function should look like?

$("input[Title='" + ProjectTitle + "']").SPServices.SPRequireUnique({   
            columnStaticName: "ProjectTitle",
            duplicateAction: 0,
            ignoreCase: false,
            initMsg: "This value must be unique.",
            initMsgCSSClass: "ms-vb",
            errMsg: "This value is not unique.",
            errMsgCSSClass: "ms-formvalidation"
            });

 

May 6, 2011 at 1:10 PM

I've also tried this: 

$("input[title=ProjectTitle]").SPServices.SPRequireUnique({   
            columnStaticName: "Title",
            duplicateAction: 1,
            ignoreCase: true,
            initMsg: "This value must be unique.",
            initMsgCSSClass: "ms-vb",
            errMsg: "This value is not unique.",
            errMsgCSSClass: "ms-formvalidation"
            });

 

I can get the initial message to show up, but it appears next to the save and cancel buttons as well as at the top of the page twice.  Also, it isn't preventing duplicate entries.  I've used the code for Get Static from Display and vice versa to double check that I'm using the correct names for my field. 

Any help would be appreciated.  Liz

May 6, 2011 at 4:50 PM

If you are using a later version of jQuery, your code should include the single quotes on the selectors like $("input[Title*='ProjectTitle']")

Coordinator
May 9, 2011 at 12:49 AM

Liz:

Assuming that your Single line of text column is called "ProjectTitle" (no spaces or other characters, what you need is just this:

$().SPServices.SPRequireUnique({   
  columnStaticName: "ProjectTitle",
  duplicateAction: 1,
  ignoreCase: true,
  initMsg: "This value must be unique.",
  initMsgCSSClass: "ms-vb",
  errMsg: "This value is not unique.",
  errMsgCSSClass: "ms-formvalidation"
});

The point I was making about the selector for the input element is that since you are using a DVWP, you just need to make sure that the markup for the column you want to use SPRequireUnique with is consistent with the markup that SharePoint generates out of the box for an input element.

M.

May 9, 2011 at 2:09 AM
I'm still having the issue where the initMsg and the errMsg are appearing at the very top of the page. It doesn't appear that the code is linking to the field. Is there a way to debug the code? To see what's working and what isn't?

Thanks,
Liz

On Sun, May 8, 2011 at 8:49 PM, sympmarc <notifications@codeplex.com> wrote:

From: sympmarc

Liz:

Assuming that your Single line of text column is called "ProjectTitle" (no spaces or other characters, what you need is just this:

$().SPServices.SPRequireUnique({   
  columnStaticName: "ProjectTitle",
  duplicateAction: 1,
  ignoreCase: true,
  initMsg: "This value must be unique.",
  initMsgCSSClass: "ms-vb",
  errMsg: "This value is not unique.",
  errMsgCSSClass: "ms-formvalidation"
});

The point I was making about the selector for the input element is that since you are using a DVWP, you just need to make sure that the markup for the column you want to use SPRequireUnique with is consistent with the markup that SharePoint generates out of the box for an input element.

M.

Read the full discussion online.

To add a post to this discussion, reply to this email (SPServices@discussions.codeplex.com)

To start a new discussion for this project, email SPServices@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Coordinator
May 9, 2011 at 2:14 AM

Liz:

Sure, you can definitely debug this. It's always hard for me to tell exactly what's going on without direct access to the system, though. You're the only one who knows what your customizations to the form are and what is happening in the DOM when you call the function. I use the Developer Tools in IE, Firebug with Firefox, and sometimes Fiddler to debug this stuff. Others seem to prefer the Chrome debugging toolset.

M.

May 9, 2011 at 2:38 AM
I think I've found the issue. My forms are created in a separate document library than the lists to which I'm posting.

So my page url is WPPage/ProjectTitle.aspx?TitleID=239 for example.

The document library that holds all of the web pages that I've built from "scratch" is WPPage. The list is "Project". I believe I need to feed in the list name and list item id for the function to work, so that the following actually retrieves the correct list name, and id, etc. (in the spservices js file)

var queryStringVals = $().SPServices.SPGetQueryString();
var thisID = queryStringVals.ID;
var thisList = $().SPServices.SPListNameFromUrl();

Can I move these into var opt, as follows:

var opt = $.extend({}, {
thisID: 1,
thislist: "ListName"
}

as defaults and then feed in the correct value from the page that holds the data view, similar to this?

$().SPServices.SPRequireUnique({
columnStaticName: "Title",
duplicateAction: 1,
ignoreCase: true,
initMsg: "This value must be unique.",
initMsgCSSClass: "ms-vb",
errMsg: "This value is not unique.",
errMsgCSSClass: "ms-formvalidation",
thisID: 130,
thisList: "Project"
});


Thanks,
Liz


On Sun, May 8, 2011 at 10:14 PM, sympmarc <notifications@codeplex.com> wrote:

From: sympmarc

Liz:

Sure, you can definitely debug this. It's always hard for me to tell exactly what's going on without direct access to the system, though. You're the only one who knows what your customizations to the form are and what is happening in the DOM when you call the function. I use the Developer Tools in IE, Firebug with Firefox, and sometimes Fiddler to debug this stuff. Others seem to prefer the Chrome debugging toolset.

M.

Read the full discussion online.

To add a post to this discussion, reply to this email (SPServices@discussions.codeplex.com)

To start a new discussion for this project, email SPServices@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Coordinator
May 9, 2011 at 2:46 AM

I'd have to dig into the code some, but I don't think what you outline is going to solve it. I'd suggest copying the ProjectTitle.aspx page into the list context and get things running there. The code in SPServices makes some decisions based on the current context.

Keep in mind that it's impossible for me to predict all of the potential variations. I try to cover the obvious possibilities, but having your list forms in some unknown-to-SPServices location is probably the issue.

M.

May 30, 2011 at 6:51 PM

Just wanted to let you know.  I've solved my issue above.  Most of the changes where in how to send the ID of the list item and the list name into the function.  Here's how I modified the SPRequireUnique:

 

$.fn.SPServices.SPRequireUnique = function (options) {

        var opt = $.extend({}, {
            columnStaticName: "Title",                    // Name of the column
            duplicateAction: 0,                            // 0 = warn, 1 = prevent
            ignoreCase: false,                            // If set to true, the function ignores case, if false it looks for an exact match
            initMsg: "This value must be unique.",        // Initial message to display after setup
            initMsgCSSClass: "ms-vb",                    // CSS class for initial message
            errMsg: "This value is not unique.",        // Error message to display if not unique
            errMsgCSSClass: "ms-formvalidation",        // CSS class for error message
            showDupes: false,                            // If true, show links to the duplicate item(s) after the error message
            thisID: 41,
            thisList: "Project",
            completefunc: null                            // Function to call on completion of rendering the change.
        }, options);

       
        // Set the messages based on the options provided
        var msg = "<span id='SPRequireUnique" + opt.columnStaticName + "' class='{0}'>{1}</span><br/>";
        var firstMsg = msg.replace(/\{0\}/g, opt.initMsgCSSClass).replace(/\{1\}/g, opt.initMsg);
       
        // We need the DisplayName
        var columnDisplayName = $().SPServices.SPGetDisplayFromStatic({
            listName: opt.thisList,
            columnStaticName: opt.columnStaticName
        });
        var columnObj = $("input[Title='" + columnDisplayName + "']");
        var newMsg = opt.initMsg;
            $("span#SPRequireUnique").val(newMsg);
        $(columnObj).blur(function () {
            var columnValueIDs = [];
            // Get the columnDisplayName's value
            var columnValue = $(this).attr("value");

            // Call the Lists Web Service (GetListItems) to see if the value already exists
            $().SPServices({
                operation: "GetListItems",
                async: false,
                listName: opt.thisList,
                // Make sure we get all the items, ignoring any filters on the default view.
                CAMLQuery: "<Query><Where><IsNotNull><FieldRef Name='" + opt.columnStaticName + "'/></IsNotNull></Where></Query>",
                // Filter based on columnStaticName's value
                CAMLViewFields: "<ViewFields><FieldRef Name='ID' /><FieldRef Name='" + opt.columnStaticName + "' /></ViewFields>",
                // Override the default view rowlimit and get all appropriate rows
                CAMLRowLimit: 0,
                completefunc: function(xData, Status) {
                    var testValue = opt.ignoreCase ? columnValue.toUpperCase() : columnValue;
                    $(xData.responseXML).find("[nodeName='z:row']").each(function() {
                        var thisValue = opt.ignoreCase ? $(this).attr("ows_" + opt.columnStaticName).toUpperCase() : $(this).attr("ows_" + opt.columnStaticName);
                        // If this value already exists in columnStaticName and it's not the current item, then save the ID in the array
                        if((testValue === thisValue) && ($(this).attr("ows_ID") !== opt.thisID)) {
                            columnValueIDs.push([$(this).attr("ows_ID"), $(this).attr("ows_" + opt.columnStaticName)]);
                        }
                    });
                }
            });
            var newMsg = opt.initMsg;
            $("span#SPRequireUnique").val(newMsg);

            $("input[value='OK'], input[value='Save']").attr("disabled", "");
            if(columnValueIDs.length > 0) {
                newMsg = opt.errMsg;
                $("span#SPRequireUnique").html(newMsg).attr("class", opt.errMsgCSSClass);
                if(opt.duplicateAction === 1) {
                    $("input[Title='" + opt.columnDisplayName + "']").focus();
                    $("input[value='OK'], input[value='Save']").attr("disabled", "disabled");
                }
                if(opt.showDupes) {
                    var out = " " + columnValueIDs.length + " duplicate item" + (columnValueIDs.length > 1 ? "s" : "") + ": ";
                    for (i=0;i < columnValueIDs.length; i++) {
                        out += "<a href='DispForm.aspx?ID=" + columnValueIDs[i][0] + "&Source=" + location.href + "'>" + columnValueIDs[i][1] + "</a> ";
                    }
                    $("span#SPRequireUnique").append(out);
                }
            }

        });
        // If present, call completefunc when all else is done
        if(opt.completefunc !== null) {
            opt.completefunc();
        }
    }; // End of function SPRequireUnique

Coordinator
May 31, 2011 at 12:51 PM

Glad you got it working. It's one of the joys of open source that you can tailor the code to do exactly what you need!

M.