Dynamic CAMLQuery

Mar 30, 2010 at 1:10 PM

Hello,

I'm posting this as suggested in this thread:   http://spservices.codeplex.com/Thread/View.aspx?ThreadId=80081

My situation is I want to filter a child by the selection of options in 2 dropdowns (DDL).
While the library adroitly handles 1 parameter (dropdown).  Getting it to work with two dropdowns has been a bit challenging.

Situation:
Like the original poster in above thread.  I see the CAMLQuery seems to stay at what it was when call is first issued (usually in document on ready function -- however I've  hooked it into a UI selection event so call is re-issuing whever the user changes selection).  In my event I see it working initially as planned.   However once that call is made, can I change UI selections, which vary my CAMLQuery subsequent calls are not made to re-render the child.

Seemingly, from the availability of the CAMLQuery I should be able to update this, re-issue the call and have my child ddl re-populated based on the new
criteria...but it's not which is what I was hopeful for.  However that doesn't seem to be working.  As I see the webservice call on 1st issue, but not
subsequent calls with differing CAMLQuery.  My code is below if it helps.  I'm hooked into onPropertyChange event of the optHid (my list is 20+ items). But no call is re-issued to the webservice.   I'm sure it's something dooable, but figure the call is not being resent because of performance \ caching or something that was set in the DOM by the library to know when to actually trigger a call out to the list webservice.

I was looking at your code and got some ideas, but please enlighten about the purpose of CompleteFunc and CAMLQuery as originally envisioned.  I think I understand CompleteFunc but CAMLQuery seems static? 

Thanks and again inspired work.
Kim

 

 

$(document).ready(function() {


    $('#' + typeCodeHiddenName)[0].onpropertychange
        = function()
        {

            // Reset the event listener
            var typeCodeHiddenName = jP.Form.TypeCode.Item.childNodes[0].children[0].attributes('optHid').value;
            //$('#' + typeCodeHiddenName)[0].onpropertychange = null;   //(avoided causing weird behavior)
            
            var cmd = "$('#" + typeCodeHiddenName  + "').val();";
            var typeCodeSelectionOptionID = eval(cmd);
            var typeCodeText = typeCodeSelectionOptionID;
            var query = "<Eq><FieldRef Name='TypeID'/><Value Type='Text'>" + eval( "$('#" +  jP.Form.TypeCode.Item.childNodes[0].children[0].attributes('optHid').value  + "').val();"  ) + "</Value></Eq>";            
            //alert('typeCodeHidden changed: ' + query );
            

            if(typeCodeSelectionOptionID != '0' && typeCodeSelectionOptionID != '')
            {            
                //Filter DetailCode (Observer) by CategoryCode (Observer)
                $().SPServices.SPCascadeDropdowns(
                    {
                        relationshipWebURL: "/sites/CS-PR/AQS",
                        relationshipList: "AQS_Detail",
                        relationshipListParentColumn: "CategoryName",
                        relationshipListChildColumn: "Title",
                        parentColumn: "Category Code (Observer)",
                        childColumn: "Detail Code (Observer)",
                        CAMLQuery: "<Eq><FieldRef Name='TypeID'/><Value Type='Text'>" + eval( "$('#" +  jP.Form.TypeCode.Item.childNodes[0].children[0].attributes('optHid').value  + "').val();"  ) + "</Value></Eq>",            
                        debug: true,
                        async: false            
                    }
                );
            }
        };

};

 

Mar 30, 2010 at 8:38 PM

My first question is does your alert work when you use it? Just want to see if your event is firing. Secondly, I am unable to follow your code without the relevant html that goes with it. What are you trying to accomplish with your dropdowns? I use dynamic camlqueries and they work fine so I just wanted to understand what is going on. It seems to me that it could be the selector you are using but it really does depend on the code and what you are trying to do.

Mar 31, 2010 at 11:37 AM
Edited Mar 31, 2010 at 11:44 AM

spevilgenioius...thank you for responding.

Yes the alert box works and shows the camlQuery varying.

Here's my scenario:

My page has these drop down (DDL) controls:

  1. Region
  2. Vendor
  3. Center
  4. DeskType
  5. CategoryCode
  6. TypeCode
  7. DetailCode

All of the relationships of 1 to 6 are (1 parent filtering 1 child).  The relationship of 7 to the others is by this relationship list: DetailCode

 

1
*3rd Pty Rtr Connect
3
Isolation Dclnd
1
TS360 DeclinedUse SHIFT+ENTER to open the menu (new window).
A
1
*3rd Pty Rtr Connect
3
Isolation Dclnd
2
TS360 Tran AcptUse SHIFT+ENTER to open the menu (new window).
A
1
*3rd Pty Rtr Connect
3
Isolation Dclnd
3
TS360 VoiceMailUse SHIFT+ENTER to open the menu (new window).
A
1
*3rd Pty Rtr Connect
3
Isolation Dclnd
4
SP+ DeclinedUse SHIFT+ENTER to open the menu (new window). Edit
A
2
*Account Maintenance
6
Acc Maintenance
61
TS360 DeclinedUse SHIFT+ENTER to open the menu (new window).
A
2
*Account Maintenance
6
Acc Maintenance
62
TS360 Tran AcptUse SHIFT+ENTER to open the menu (new window).
A
2
*Account Maintenance
6
Acc Maintenance
63
TS360 VoiceMailUse SHIFT+ENTER to open the menu (new window).
A
2
*Account Maintenance
6
Acc Maintenance
64
Add/Del AliasUse SHIFT+ENTER to open the menu (new window).
A
2
*Account Maintenance
6
Acc Maintenance
65
Add/Del SubAcctUse SHIFT+ENTER to open the menu (new window). Edit
A


The DetailCode drop down needs to be filtered by current selection in two Parent DDL controls: CategoryCode and TypeCode

The earlier code snippet is attempting to do this (on CategoryName & TypeID).  CategoryCode DDL to DetailCode DDL filtering is
just like the others...only difference is it's trying to use a dynamic CAMLQuery to specify the additional filter value of TypeID.  

I'm encouraged to know it's working for you.   Mine seems to be stuck on it's first evaluation and then that's it. 
My guess: It's got something to do with me (usually is) or the dynamic nature of the page.

Please let me know if I'm still not beling clear or if more info is needed.
Kim

 

 

Coordinator
Mar 31, 2010 at 12:19 PM

Kim:

Using a dynamic CAMLQuery and having two three columns interacting are sort of two separate things.  Given the rules you are trying to enforce, you might be best off just building yourself a little function to manage the DetailCode column separately.  You have a known situation with columns and values you can control.  In my library, I have to handle a lot of eventualities and boundary conditions that you won't encounter.  You can still use SPCascadeDropdowns for the other relationships, of course.

I'll be interested in where you go with this.   If you come up with something which you think might be generalizable, please share!

M.

Mar 31, 2010 at 2:43 PM

Thanks Marc,

I'm wondering if adding an eval() in the code section below will do it?
I've a feeling that *IF* (can I make that any more emphasized) the camlQuery were re-evaluated at this point I'd get the results I want.

I'm not sure I understand...but you and Spevilgenious mentioned dynamic camlqueries.  It's obvious the facility is there in the lib parameter, but
I'm not seeing any dynamism for the query tracing through the function.   What I see is a camlQuery string that inspite of the UI activity and my attempts to make it dynamic remains the same.

Also, I'm not sure how are the two are separate?  I have a list with 2 parent columns.  Seems ideal for camlQuery.   Actually it's sorta interesting working on this from the outside in.   I'm seeing how the magic is done and if you mean two parents to one DDL then I'd agree with your earlier statement.  There's alot going on behind the scenes that you just take for granted...until like me you need to dive in.  Long Live Open Source.

 

Existing Code

function cascadeDropdown(opt) {
<snip...>

     if(opt.CAMLQuery.length > 0){
            debugger;
            camlQuery += opt.CAMLQuery + "</And>";
        }

        camlQuery += "</Where></Query>";

<snip...>
}

 

Possible Solution

function cascadeDropdown(opt) {
<snip...>

     if(opt.CAMLQuery.length > 0){
            debugger;
            camlQuery += eval(opt.CAMLQuery) + "</And>";
        }

        camlQuery += "</Where></Query>";

<snip...>
}

Thanks
Kim

Coordinator
Apr 1, 2010 at 1:37 PM

I think that there's a difference between what you are trying to do and how SPCascadeDropdowns is implemented.  When you call SPCascadeDropdowns, it attaches to the change event for the column with the function based on the parameters you have passed to it.  That attachment won't change over the lifecycle of the page.  I'm not saying that it isn't possible (with code all things are possible), but it isn't the way it works now.

M.

Apr 1, 2010 at 2:00 PM

Excellent tip,

Is it possible...ah yes, all thing are possible with code...to re-cast the code in that change event.  I mean like delete it and re-add it?
I looked and it wasn't obvious what to do but this seemed similar to what the poster I referred to in opening the thread did.

$().find("select:[Title='Equipment Type']").unbind('change');


Thanks,
Kim

 

Coordinator
Apr 1, 2010 at 2:10 PM

Yes, I think you should experiment with that.  I'm actually very interested in whether this is possible and how you make it work if you do.  I'm just a bit too tied up right now to try to do it myself.

M.

Apr 1, 2010 at 7:16 PM

Actually that is sort of what I was thinking. You can unbind that change function and then add a new change function that uses the camlquery set to the values that are needed. This function would have to manually add the options to the dropdown but that is not hard to do. This sounds pretty cool.

Apr 2, 2010 at 12:13 PM

Thank you both!  I'll reply back with what I find.

Coordinator
Apr 2, 2010 at 12:48 PM

One other thought occurred to me as I was flying away for the long weekend...

You probably could take advantage of the completefunc on the parent columns to bind your additional change event(s) which would alter the child column appropriately.

M.