SPCascadeDropdowns Help

Apr 18, 2012 at 2:53 PM
Edited Apr 18, 2012 at 3:02 PM

Ok, this is going to be out of the ordinary and I am asking for help as I cannot quite wrap my head around it. I have 2 lists, one list contains locations as the title and a number column with a bitmask assigned to it, i.e. 1, 2, 4, 8, 16 etc, so 2 or more combined values are unique (I cannot get into detail but there is a reason to do it this way). The second list contains salespeople and they have an accumulated bitmask assigned based on all of their sales locations, i.e. 3, 24, etc....

I need to populate a dropdown with their locations based on the accumulated bitmask they have assigned. This is needed so they can enter data based on a single location. Does anyone have any idea on how I can accomplish this? I can easily match the user to one list but the calculations is driving me nuts....

 

Example:

John Doe has a bitmask value assigned to him of 33 which equates to 1 + 32 which are location #1 and location #6 so the dropdown should only show these 2 locations.

 

Lastly, location bitmask starts at 1 but yet a new salesperson can have a 0 (zero) value until he/she is assigned locations so their dropdown would show nothing....

Apr 18, 2012 at 3:58 PM

I think I'm tracking what you are saying...

Does your second list that contains your sales personnel have a multi-select lookup to the first list of locations?  That would make this a lot easier to query the values you need for your drop down.

 

Cheers,
Matt 

Apr 18, 2012 at 4:04 PM
Edited Apr 18, 2012 at 4:38 PM

Unfortunately no. The first list just contains the salesperson and location bitmask columns (one line per salesperson), the bitmask is what will determine the location dropdown as in my example below.

 I will only have a single dropdown, the locations one. I will be using "SPServices.SPGetCurrentUser" for the salesperson using the form and use his/her ID to query data in the second list (salesperson) and then populate the locations dropdown based on that info.

 

List A
---------------------------------
Title                     | BitMask |
---------------------------------
John Doe             |    33      |
---------------------------------
Jane Doe             |      2      |
---------------------------------
John Smith           |    63      |
---------------------------------

List B
---------------------------------
Title            |    LocCode     |
---------------------------------
Location 1   |           1         |
---------------------------------
Location 2   |           2         |
---------------------------------
Location 3   |           4         |
---------------------------------
Location 4   |           8         |
---------------------------------
Location 5   |         16         |
---------------------------------
Location 6   |         32         |
---------------------------------
Location 7   |         64         |
---------------------------------

 

Ok, based on the info above:

John Doe's locations are 1 & 6 and should only see these 2 locations in the dropdown.

Jane Doe's location is 2 and should only see this location in the dropdown.

John Smith's locations are 1 through 5 and should only see these 5 locations in the dropdown.

 

This based on a bitmask value and a summed combination of locations will be unique, refer to John Smith, his bitmask is 63 and there is no LocCode of 63 so the next lower value (32) is part of the combined bitmask so that is one location, so we subtract 32 from 63 which leaves 31 and again there is no LocCode 31 so we do this again, next lower value is 16 so now this is another location and again we subtract 16 from 31 and get 15 and so on....

 

Does that make it clearer?

Apr 18, 2012 at 5:04 PM

Post updated....

Apr 18, 2012 at 5:29 PM
Edited Apr 18, 2012 at 5:47 PM

Yes and this code I'm about to write is totally not tested whatsoever, but should be close to what you need...

 

var camlArr = [],
	camlOpts = {},
	userBitMask = 0,
	bitMaskArr = [64,32,16,8,4,2,1] /* reordered array; Large numbers first */
; //local vars

$().SPServices({
	operation: "GetListItems",
	async: false,
	listName: "YourListNameForSales",
	//CAMLViewFields: "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Body' /><FieldRef Name='Modified' /></ViewFields>",
	//CAMLQuery: //Whatever query you use to find the person you are looking up goes here!
	completefunc: function (xData, Status) {
		console.log( Status );
		console.log( xData.responseText );

		userBitMask = $( xData.responseXML ).find("[nodeName='z:row']").attr("ows_BitMaskStaticColumnName") *1; //coerced to number primitive
	}
});


if ( userBitMask > 0 ) {
	for ( var i = 0; i<bitMaskArr.length; i++ ) {
		//Get the modulus of the userBitMask and divide down...
		if ( userBitMask % bitMaskArr[i] > 0 ) {
			//minus the value of bitMask
			userBitMask -= bitMaskArr[i];
			//stuff camlArr with bitMask value and other properties required for roboCAML: http://robocaml.codeplex.com/wikipage?title=Array%20of%20Objects
			camlArr.push({
				filter: "||",
				op: "=",
				staticName: "WhateverColumnYouUseForBitMaskInLocationsList",
				value: bitMaskArr[i]
			});
		}
	}
}

//Set up caml options for roboCAML: http://robocaml.codeplex.com/wikipage?title=roboCAML.Query%28%29%3b
camlOpts.listName = "YourListNameForLocations";
camlOpts.closeCaml = "SPServices";
camlOpts.config = camlArr;



$().SPServices({
	operation: "GetListItems",
	async: false,
	listName: "YourListNameForLocations",
	//CAMLViewFields: "<ViewFields><FieldRef Name='Title' /><FieldRef Name='Body' /><FieldRef Name='Modified' /></ViewFields>",
	CAMLQuery: roboCAML.Query( camlOpts ), /* requires roboCAML, http://roboCAML.codeplex.com */
	completefunc: function (xData, Status) {
		console.log( Status );
		console.log( xData.responseText );

		var $nodeSet = $( xData.responseXML ),
			output = ""
		; //local vars

		$nodeSet.find("[nodeName='z:row']").each(function() {
			//Build your DDL
			//output += "<option>";
		}
		$("#someAwesomeElement").append( output );
	}
});

 

 

I can try to help you along further, but I might have to mock it up altogether... 

Cheers,

Matt

 

/* Just noticed, you'll want to gracefully handle the query *if* the user bit mask = 0 */

Apr 18, 2012 at 7:41 PM

Wow....thank you so much. I will test this out and get back to you.

Apr 19, 2012 at 12:32 PM

Ok, tested this out and it inteferes with other SPCascadeDropdowns in my form, it completely breaks their functionality.

Apr 19, 2012 at 2:17 PM

That'd be expected. :)

My example isn't using that function.  However SPCascadeDropdowns does allow the usage of a CAML string.  You could use some similar logic from above to get the correct CAML to pass into your cascade function.  Keep in mind, what you are doing isn't trivial in nature.  It may be good for you to stand back, look at the issue from 50,000 ft. and whiteboard the solution...

I do that all the time.

Cheers,

Matt

Apr 19, 2012 at 2:36 PM

Agreed, although 50,000 feet will give me a nosebleed. ;)

Apr 20, 2012 at 10:46 AM
Edited Apr 20, 2012 at 12:30 PM

Matt,

 

I'm having a tough time trying to wrap my head around this...writers block? LOL. Anyway, wouldn't it be better to create a function with your code:

if ( userBitMask > 0 ) {
for ( var i = 0; i<bitMaskArr.length; i++ ) {
//Get the modulus of the userBitMask and divide down...
if ( userBitMask % bitMaskArr[i] > 0 ) {
//minus the value of bitMask
userBitMask -= bitMaskArr[i];
//stuff camlArr with bitMask value and other properties required for roboCAML: http://robocaml.codeplex.com/wikipage?title=Array%20of%20Objects
camlArr.push({
filter: "||",
op: "=",
staticName: "WhateverColumnYouUseForBitMaskInLocationsList",
value: bitMaskArr[i]
});
}
}
}

and then pass the bitmask value to it and then pass it to the CAML Query? Thats the best solution I can think of and shouldn't interfere with the other cascadedropdowns, plus I can use the function elsewhere.... Ideas? BTW, this I just found out from my boss is time critical, ugh....last thing I need. :(

Apr 20, 2012 at 1:02 PM

I've revised the code from above to this:

var camlArr = [],
	camlOpts = {},
	userBitMask = 67, //Hardcoded like a BOSS!
	bitMaskArr = [64,32,16,8,4,2,1] /* reordered array; Large numbers first */
; //local vars

if ( userBitMask > 0 ) {
	for ( var i = 0; i<bitMaskArr.length; i++ ) {
		if ( userBitMask >= bitMaskArr[i] ) {
			//debugger;

			//minus the value of bitMask
			userBitMask -= bitMaskArr[i];
			//What's the new value?
			//console.log( "userBitMask: " + userBitMask );
			//stuff camlArr with bitMask value and other properties required for roboCAML: http://robocaml.codeplex.com/wikipage?title=Array%20of%20Objects
			camlArr.push({
			filter: "||",
			op: "=",
			staticName: "WhateverColumnYouUseForBitMaskInLocationsList",
			value: bitMaskArr[i]
			});
		}
	}
	//How many in camlArr?
	//console.log( camlArr.length );
}

The modulus wasn't that great of an idea. :)  Now it works...

What ends up happening above is camlArr is stuffed with three objects.  That's then piped to roboCAML to produce this output:

<Query>
	<Where>
		<Or>
			<Eq>
				<FieldRef Name='WhateverColumnYouUseForBitMaskInLocationsList' />
				<Value Type='Number'>64</Value>
			</Eq>
			<Or>
				<Eq>
					<FieldRef Name='WhateverColumnYouUseForBitMaskInLocationsList' />
					<Value Type='Number'>2</Value>
				</Eq>
				<Eq>
					<FieldRef Name='WhateverColumnYouUseForBitMaskInLocationsList' />
					<Value Type='Number'>1</Value>
				</Eq>
			</Or>
		</Or>
	</Where>
</Query>

This CAML will find only the locations that match the bitMask setup in your locations list.  If you want to break out the above code into it's own function, that seems fine to do.  I'm not sure how to answer your question about the cascade dropdowns though.  I don't know what you are doing with it...


Cheers,

Matt

Apr 20, 2012 at 1:52 PM

Matt,

 

I want to use the $().SPServices.CascadeDropdown to populate the Location dropdown with the returned values...the last part of your code is building the options and appending it to a dropdown.

Apr 20, 2012 at 2:14 PM

Okay, that makes some more sense... You'll have to update my code a bit and instead of doing the last GetListItems call, you'll want to make your SPServices.CascadeDropdown call instead.

Here's the docs for that:

http://spservices.codeplex.com/wikipage?title=$().SPServices.SPCascadeDropdowns&referringTitle=Documentation

I've never used it b/c I generally am forced to roll my own.  However, I do know it accepts a CAMLQuery, so instead of adding the closeCaml property to camlOpts, you can just omit it and roboCAML will emit instead:

 

		<Or>
			<Eq>
				<FieldRef Name='WhateverColumnYouUseForBitMaskInLocationsList' />
				<Value Type='Number'>64</Value>
			</Eq>
			<Or>
				<Eq>
					<FieldRef Name='WhateverColumnYouUseForBitMaskInLocationsList' />
					<Value Type='Number'>2</Value>
				</Eq>
				<Eq>
					<FieldRef Name='WhateverColumnYouUseForBitMaskInLocationsList' />
					<Value Type='Number'>1</Value>
				</Eq>
			</Or>
		</Or>

 

 

Does that help?

Cheers,

Matt

Apr 20, 2012 at 2:50 PM
Edited Apr 20, 2012 at 2:53 PM

Matt,

Yes it does help. To add insult to injury (add more complexity to it hence me not able to roll my own) let me give you more details...

There will be a total of 3 dropdowns, the first populated with the location based on the users bitmask as we are working on above, the second dropdown (no bitmask) populates based on the first dropdowns selection (Location) and the third also has a bitmask that is not accumulative as in the first (users bitmask) that contains data based on the value user selected in the first.

Now you see my dilemma and complexity of this entire project....

Regards,

Bob

Apr 20, 2012 at 2:55 PM

Something tells me you should re-architect your lists and use lookups.  Your life would be so much easier.

Apr 20, 2012 at 2:58 PM
Edited Apr 20, 2012 at 2:58 PM

I wish it were that easy, unfortunately this is how it is. The bitmask is used everywhere in databases of every type, i.e. Oracle, MS-SQL, MySQL, DB2, etc...the lists are populated from MS-SQL.