SPDisplayRelatedInfo list primary key is two fields

Jan 7, 2010 at 3:28 PM

Marc thanks for your great work on this library that has quickly become essential to my productivity with SharePoint.

Sorry to be so needy lately, but I can't figure out a smooth way to accomplish what I am trying to do.

I am using SPCascadeDropdowns and SPDisplayRelatedInfo to display some details on a new item form. It works exactly as it is supposed to. The problem is that in the SPDisplayRelatedInfo my relatedList has a key of two columns, Equipment_x0020_Type and Branch. I know I need to use the CAMLQuery option to accomplish my task, but the problem is that the Branch value is a dropdown option on the form and is a lookup field from another list. I am not able to decide how to write the CAML Query. I am not sure if I need to use the drop-down value which I could get with a javascript function or if I need the drop-down display text which I am not sure how to get with javascript. Here's what I have so far.

$(document).ready(function() {
	$().SPServices.SPCascadeDropdowns({
		relationshipList: "Equipments",
		relationshipListParentColumn: "Branch",
		relationshipListChildColumn: "Equipment_x0020_Type",
		parentColumn: "Branch",
		childColumn: "Equipment Type",
	});
	
	$().SPServices.SPDisplayRelatedInfo({
	columnName: "Equipment Type",
	relatedList: "Equipments",
	relatedListColumn: "Equipment_x0020_Type",
	relatedColumns: ["Vendor_x0020_Name", "Site_x0020__x0023_"],
	CAMLQuery: "<Eq><FieldRef Name='Branch'/><Value Type='Text'>" + <DON'T KNOW WHAT GOES HERE> + "</Value></Eq>",
	displayFormat: "list"
});
});

Coordinator
Jan 7, 2010 at 3:35 PM

I don't think you're going to be able to do what you're explaining with SPDisplayRelatedInfo as it's written.  I'm wondering if your data model makes sense, though.  If Equipment_x0020_Type and Branch are the keys on the Equipments list, then aren't the Equipment_x0020_Types actually different?

M.

Jan 7, 2010 at 4:36 PM

Not sure I follow your question...

The Equipments list holds a branch location, a type of physical business equipment at that branch, and vendor information for that branch and equipment type.

I am making a list where users can log issues at their branch location for a type of equipment and need to display vendor information to the user once they pick their branch and equipment type.

Coordinator
Jan 7, 2010 at 6:27 PM
Edited Jan 7, 2010 at 6:29 PM

Sorry.  I think I engaged my typing fingers before my brain.  Since the Branch column is a dropdown, you can look at the internal dropdownCtl function in the library:

function dropdownCtl(colName) {
  if((this.Obj = $().find("select:[Title='" + colName + "']")).html() != null) {
   this.Type = "S";
  } else if((this.Obj = $().find("input:[Title='" + colName + "']")).html() != null) {
   this.Type = "C";
  } else if((this.Obj = $().find("select:[Title='" + colName + " possible values']")).html() != null) {
   this.Type = "M";
  } else
   this.Type = null;
 }

and then this section of SPCascadeDropdowns:

switch(parentSelect.Type) {
   case "S":
    parentSelectSelected.push(parentSelect.Obj.find("option:selected").text());
    break;
   case "C":
    parentSelectSelected.push(parentSelect.Obj.attr("value"));
    break;
   case "M":
    parentSelections = parentSelect.Obj.closest("span").find("select:[Title='" + opt.parentColumn + " selected values']");
    $(parentSelections).find("option").each(function() {
     parentSelectSelected.push($(this).html());
    });
    break;
   default:
    break;
  }

The three different branches handle "simple" (S) dropdowns (< 20 options), "complex" (C) dropdowns (20+), or "multiple" (M).  If you have fewer than 20 Branches, use the first selector; 20 or more, the second. So something like this:

CAMLQuery: "<Eq><FieldRef Name='Branch'/><Value Type='Text'>" + $().find("select:[Title='Branch']").("option:selected").text() + "</Value></Eq>",

or

CAMLQuery: "<Eq><FieldRef Name='Branch'/><Value Type='Text'>" + $().find("input:[Title='Branch']").attr("value") + "</Value></Eq>",

I think this is right, but of course I haven't tested it.

 

M.

 

Jan 7, 2010 at 6:45 PM

Great! Thanks I'll try it out and let you know what I come up with.

Jan 19, 2010 at 4:12 PM

Here the correct CAML for this:

CAMLQuery: "<Eq><FieldRef Name='Branch' /><Value Type='Lookup'>" + $().find("select:[Title='Branch']").find("option:selected").text() + "</Value></Eq>"

I am still working out some problems but did want to point out for anyone who might want to use this code that the above was missing and extra find() method.

Nick

Jan 19, 2010 at 8:02 PM

Marc,

Hoping you can help me figure out what is going on. Here is my code:

$(document).ready(function() {
	$().SPServices.SPCascadeDropdowns({
		relationshipList: "Equipments",
		relationshipListParentColumn: "Branch",
		relationshipListChildColumn: "Equipment_x0020_Type",
		parentColumn: "Branch",
		childColumn: "Equipment Type",
		debug: true
	});
		
	$().SPServices.SPDisplayRelatedInfo({
		columnName: "Equipment Type",
		relatedList: "Equipments",
		relatedListColumn: "Equipment_x0020_Type",
		relatedColumns: ["Vendor_x0020_Name", "Site_x0020__x0023_"],
		CAMLQuery: "<Eq><FieldRef Name='Branch' /><Value Type='Lookup'>" + $().find("select:[Title='Branch']").find("option:selected").text() + "</Value></Eq>",
		displayFormat: "list"
		});
});

The SPDisplayRelatedInfo isn't always pulling the correct info. I think it has something to do with the dynamic CAMLQuery. It pulls the correct info for the first branch location selected, but all other branches display only the first branches related information.

Does that make since, and do you have any ideas?

Coordinator
Jan 19, 2010 at 8:11 PM

This might be a little tough to debug from afar, but here are a few thoughts/questions:

  • When you say "first branch", do you mean always the first branch in the list or the first branch selected?  When you change the branch selected, does anything change? Do you see any delay or refresh of the values which indicate that the Web Service call is working?
  • If you remove the CAMLQuery altogether, do you get what you'd expect? (This would be all of the items which have the selected Branch.) You probably should switch to the 'table' format for this test.
  • In the CAMLQuery, you're specifying <Value Type='Lookup'. Are you sure that $().find("select:[Title='Branch']").find("option:selected").text() is returning the rightID? You might just want to alert that to be sure.

M.

Jan 20, 2010 at 2:36 PM
  • Yes, "first branch" mean the first branch select in the drop down. When I change the selected branch the SPCascadeDropdown function call works to change the Equipment Type dropdown. And the SPDisplayRelatedInfo call works to display different information based on what equipment type is selected, but the info it displays is related to the inital branch selection. It is like the dynamic piece of the CAMLQuery is not being re-evaluated.
  • If I remove the CAMLQuery, I do get what I would expect. I get multiple rows of data because I am querying only on equipment type and not by branch.
  • I did have an alert in a completeFunc with the find() javascript and it did return the selected branch.

I took some screen shots to help make it clear. Is there a place I can upload them?

Coordinator
Jan 20, 2010 at 4:35 PM

I sent you a direct message so that you could email me the screenshots.  Send me the current state of your code as well so that I can take a look.

M.

Jan 20, 2010 at 4:36 PM

Marc,

My suspicions seem to be correct. The dynamic part of the CAMLQuery is not being reevaluated on each call. I added an alert to the library to see the full CAMLQuery on each call like below:

function showRelated(opt) {

		var columnSelectSelected = null;

		// Find the column's select (dropdown)
		var columnSelect = new dropdownCtl(opt.columnName);

		// Get the current column selection(s)
		switch(columnSelect.Type) {
			case "S":
				columnSelectSelected = columnSelect.Obj.find("option:selected").text();
				break;
			case "C":
				columnSelectSelected = columnSelect.Obj.attr("value");
				// Check to see if at least opt.numChars have been typed (if specified)
				if(opt.numChars > 0 && columnSelectSelected.length < opt.numChars) return;
				break;
			case "M":
				break;
			default:
				break;
		}

		// If the selection hasn't changed, then there's nothing to do right now.  This is useful to reduce
		// the number of Web Service calls when the parentSelect.Type = "C", as there are multiple propertychanges
		// which don't require any action.
		if(columnSelect.Obj.attr("showRelatedSelected") == columnSelectSelected) return;
		columnSelect.Obj.attr("showRelatedSelected", columnSelectSelected);
		var divId = "showRelated_" + encodeColumn(opt.columnName);
		$("#" + divId).remove();
		columnSelect.Obj.parent().append("<div id=" + divId + "></div>");


		// Only get the requested columns
		var relatedColumnsXML = [];

		// Get information about the related list and its columns
		$().SPServices({
			operation: "GetList",
			async: false,
			webURL: opt.relatedWebURL,
			listName: opt.relatedList,
			completefunc: function(xData, Status) {
				// If debug is on, notify about an error
				$(xData.responseXML).find("faultcode").each(function() {
					if(opt.debug) errBox("SPServices.SPDisplayRelatedInfo",
						"relatedList: " + opt.relatedList,
						"List not found");
					return;
				});
				// Output each row
				$(xData.responseXML).find("Fields").each(function() {
					$(xData.responseXML).find("Field").each(function() {
						for (i=0; i < opt.relatedColumns.length; i++) {
							// If this is one of the columns we want to display, save the XML node
							if($(this).attr("Name") == opt.relatedColumns[i]) relatedColumnsXML[i] = $(this);
						}
					});
				});
			}
		});

		// Get the list items which match the current selection
		var camlQuery = "<Query><Where>";
		if(opt.CAMLQuery.length > 0) camlQuery += "<And>";
		camlQuery += "<" + opt.matchType + "><FieldRef Name='" + opt.relatedListColumn + "'/><Value Type='Text'>" + escapeColumnValue(columnSelectSelected) + "</Value></" + opt.matchType + ">";
		if(opt.CAMLQuery.length > 0) camlQuery += opt.CAMLQuery + "</And>";
		camlQuery += "</Where></Query>";
                alert(camlQuery);
 		var viewFields = " ";
This shows the equipment type changing on every call, but the branch does not change. On each call it remains the same branch name as the initially selected branch. However if I display this code:
alert($().find("select:[Title='Branch']").find("option:selected").text());
in a completeFunc it changes with each branch selection.
Coordinator
Jan 20, 2010 at 4:41 PM
Edited Jan 20, 2010 at 4:42 PM

Oh, OK.  I see what's going on here.  It's actually doing what I would expect now that I think it through better.

This call:

$().SPServices.SPDisplayRelatedInfo({
  columnName: "Equipment Type",
  relatedList: "Equipments",
  relatedListColumn: "Equipment_x0020_Type",
  relatedColumns: ["Vendor_x0020_Name", "Site_x0020__x0023_"],
  CAMLQuery: "<Eq><FieldRef Name='Branch' /><Value Type='Lookup'>" + $().find("select:[Title='Branch']").find("option:selected").text() + "</Value></Eq>",
  displayFormat: "list"
});

Is only happening once, when the page loads. Therefore, every time that showRelated is called, the value for the Branch is set to the same value: What $().find("select:[Title='Branch']").find("option:selected").text() evaluates to initially.

M.

Jan 20, 2010 at 4:48 PM

So, can I fix this by calling SPDisplayRelatedInfo a different way? Like onChange or something like that?

Jan 20, 2010 at 7:09 PM

I have been able to bind the SPDisplayRelatedInfo call to the onchange method of the branch dropdown. It works correctly once, I think to do it this way, I need to be able to remove the SPDisplayRelatedInfo call from the element then readd it on change.

Is this possible?

Here is my code so far:

$(document).ready(function() {
	$().SPServices.SPCascadeDropdowns({
		relationshipList: "Equipments",
		relationshipListParentColumn: "Branch",
		relationshipListChildColumn: "Equipment_x0020_Type",
		parentColumn: "Branch",
		childColumn: "Equipment Type",
		debug: true
	});
		
		/*$().SPServices.SPDisplayRelatedInfo({
		columnName: "Equipment Type",
		relatedList: "Equipments",
		relatedListColumn: "Equipment_x0020_Type",
		relatedColumns: ["Vendor_x0020_Name", "Site_x0020__x0023_"],
		CAMLQuery: "<Eq><FieldRef Name='Branch' /><Value Type='Lookup'>" + $().find("select:[Title='Branch']").find("option:selected").text() + "</Value></Eq>",
		displayFormat: "list"
		});*/


	$().find("select:[Title='Branch']").change(function() {
		//I think I need a call here to remove SPDisplayRelatedInfo from the element
		$().SPServices.SPDisplayRelatedInfo({
			columnName: "Equipment Type",
			relatedList: "Equipments",
			relatedListColumn: "Equipment_x0020_Type",
			relatedColumns: ["Vendor_x0020_Name", "Site_x0020__x0023_"],
			CAMLQuery: "<Eq><FieldRef Name='Branch' /><Value Type='Lookup'>" + $().find("select:[Title='Branch']").find("option:selected").text() + "</Value></Eq>",
			displayFormat: "list"
		});
	});
		
});

I have commented out the inital SPDisplayRelatedInfo call but if I can remove it from the element onchange then I should be okay removing the comments.

Coordinator
Jan 20, 2010 at 7:27 PM

SPDisplayRelatedInfo is also going to bind itself to the change event on the select, so you probably will have a conflict, or at least two chucks of code running in sequence.  In what you are doing above, you're binding to the change event, but you also need to trigger it.  I think you may end up in an infonite loop, though.

M.

Jan 20, 2010 at 8:06 PM

Correct me if I misspeak here, because I am getting into uncharted waters, but SPDisplayRelatedInfo should bind to the Equipment Type change event, not the Branch change event?

And wouldn't the trigger happen when I select another option from the dropdown?

Jan 20, 2010 at 8:08 PM

Just figured it out and it seems to be working correctly. Here is the final code:

<script language="javascript" type="text/javascript" src="/jQueryLibrary/jquery-1.3.2.min.js"></script> 
<script language="javascript" type="text/javascript" src="/jQueryLibrary/jquery.SPServices-0.4.7.min.js"></script> 
<script language="javascript" type="text/javascript">	

$(document).ready(function() {
	$().SPServices.SPCascadeDropdowns({
		relationshipList: "Equipments",
		relationshipListParentColumn: "Branch",
		relationshipListChildColumn: "Equipment_x0020_Type",
		parentColumn: "Branch",
		childColumn: "Equipment Type",
		debug: true
	});
		
		$().SPServices.SPDisplayRelatedInfo({
		columnName: "Equipment Type",
		relatedList: "Equipments",
		relatedListColumn: "Equipment_x0020_Type",
		relatedColumns: ["Vendor_x0020_Name", "Site_x0020__x0023_"],
		CAMLQuery: "<Eq><FieldRef Name='Branch' /><Value Type='Lookup'>" + $().find("select:[Title='Branch']").find("option:selected").text() + "</Value></Eq>",
		displayFormat: "list"
		});


	$().find("select:[Title='Branch']").change(function() {
		$().find("select:[Title='Equipment Type']").unbind('change');
		$().SPServices.SPDisplayRelatedInfo({
			columnName: "Equipment Type",
			relatedList: "Equipments",
			relatedListColumn: "Equipment_x0020_Type",
			relatedColumns: ["Vendor_x0020_Name", "Site_x0020__x0023_"],
			CAMLQuery: "<Eq><FieldRef Name='Branch' /><Value Type='Lookup'>" + $().find("select:[Title='Branch']").find("option:selected").text() + "</Value></Eq>",
			displayFormat: "list"
		});
	});		
});

</script>

Coordinator
Jan 20, 2010 at 8:09 PM
Edited Jan 20, 2010 at 8:09 PM

I think you're correct. (You're paying more attention to these details than I can!)  What you might want to do, though, is trigger the change event right after the SPDisplayRelatedInfo call so that it executes once at page load.

M.

Mar 26, 2010 at 8:15 PM

Is this similar to this?
http://spservices.codeplex.com/Thread/View.aspx?ThreadId=73049

I mean is this a solution to requiring two inputs (one dynamic...meaning SPCascadeDropdown call is re-issued like above with dynamic CAML based on UI) to filter a dropdown?
Earlier (in the link above) this wasn't thought to be possible, but is this possible workaround?

Many Thanks,
(And Inspired Work Marc)

Kim

 

Coordinator
Mar 28, 2010 at 12:49 AM

Kim:

I'm not sure exactly what you're looking for.  Can you start a new thread with the details of what you are trying to solve?

M.

Jun 4, 2010 at 9:03 AM
Edited Jun 4, 2010 at 9:32 AM
I had to comment out an optimization in the library for this to work - otherwise the info would not be updated when calling trigger('change') // If the selection hasn't changed, then there's nothing to do right now. This is useful to reduce // the number of Web Service calls when the parentSelect.Type = "C", as there are multiple propertychanges // which don't require any action. //if(columnSelect.Obj.attr("showRelatedSelected") == columnSelectSelected) return; My relevant code is here: $("select[title$='Fiscal Yea']").unbind('change'); $().SPServices.SPDisplayRelatedInfo({ columnName: "Fiscal Year", relatedList: "Financial Targets", relatedListColumn: "Year", relatedColumns: ["Title", "Factor"], displayFormat: "table", CAMLQuery: "<Eq><FieldRef Name='FactorType' /><Value Type='Lookup'>" + $("select[title$='Scheme Type']").find("option:selected").text() + "</Value></Eq>" }); $("select[title$='Fiscal Yea']").trigger('change'); Suggestion: It would be great if there would be a parameter to the change event handler to bypass this optimization manually. Robert
Coordinator
Jun 4, 2010 at 11:23 AM

Robert:

I'm not sure I follow you. Can you start a new thread and explain a bit more?

Thanks,
M.