Search Results for 'get values from another list'

[Keith Hudson]

//spjs_QueryItems
//retrieve items from a list
//takes an object containg the following parameters:
//listName, query, viewFields
spjs_QueryItems(argObj)

example:
function getMacros(){
var fieldName = “Title”;
var fieldType = “Text”;
var listName = “Foods”;
var targetString = getFieldValue(“Food”);
var qty = getFieldValue(“Qty”);
var res, qb = [], item;
qb.push(“<Where>”);
qb.push(“<Eq>”);
qb.push(“<FieldRef Name='” + fieldName + “‘/><Value Type= ‘” + fieldType + “‘>” + targetString + “</Value>” );
qb.push(“</Eq>”);
qb.push(“</Where>”);
res = spjs_QueryItems({“listName”:listName,”query”:qb.join(“”),”viewFields”:[“PctFat”,”CalTot”,”PctCarb”,”PctProtein”]});
if(res.count > 0){
item = res.items[0];
setFieldValue(“TotCal”,item.CalTot !== null ? item.CalTot*qty: “not set”);
setFieldValue(“PctFat”,item.PctFat !== null ? item.PctFat*100: “not set”);
setFieldValue(“PctCarb”,item.PctCarb !== null ? item.PctCarb*100: “not set”);
setFieldValue(“PctProtein”,item.PctProtein !== null ? item.PctProtein*100: “not set”);
}
}

Forum articles:
get values from another list/
vlookup-from-new-on-separate-list

• This reply was modified 6 years, 11 months ago by Keith Hudson.
• This reply was modified 6 years, 11 months ago by Keith Hudson.

[Sergio Giusti]

Hi Alex

I am trying to use Autocomplete to filter some values with CAML and I cannot get it to work, ill briefly explain what I’m trying to have happen on the form so you have some understanding, then Ill put the current code.

I have a list called Jobs where the code is running, on this list I have a lookup field to an Accounts list and another to an Account Contacts list, I am trying to get the form to offer me two autocomplete fields, when I select the ‘AccountName’ autocomplete field it is completing the first lookup column (Account) for me with the ID selected and also setting another single line of text field to the same ID of the item selected, the second autocomplete field should then show me a filtered list of items from the second autocomplete field ‘Location’ which should only show items from the lookup list that have one its columns (a lookup column called ‘Accounts’) match the ID held in the AccountID field on the form.

Here is my code:

spjs.ac.textField({
	"applyTo":"AccountName",
	"helpText":"Enter the name of the account...",
	"listGuid":"Accounts",
	"listBaseUrl":"/sites/jobs",
	"showField":"Title",
	"enforceUniqueValues":true,
	"rowLimit":15,
	"listOptionsOnFocus":false,
	"reValidateOnLoad":false,
	"orderBy":[{"fin":"Title","ascending":true}],
	"allowAddNew":false,
	"isLookupInSelf":false,
	"filterCAML":"",
	"setFields":[
		{
			"fromFIN":"Title",
			"toFIN":"AccountName",
			"parseFunction":"",
			"skipIfEmpty":false
		},
		{
			"fromFIN":"ID",
			"toFIN":"Account",
			"parseFunction":"",
			"skipIfEmpty":false
		},
		{
			"fromFIN":"ID",
			"toFIN":"AccountID",
			"parseFunction":"",
			"skipIfEmpty":false
		}

	]					
});			

spjs.ac.textField({
	"applyTo":"Location",
	"helpText":"Enter the post code of the account...",
	"listGuid":"Account Contacts",
	"listBaseUrl":"/sites/jobs",
	"showField":"PostCode",
	"enforceUniqueValues":true,
	"rowLimit":15,
	"listOptionsOnFocus":false,
	"reValidateOnLoad":true,
	"orderBy":[{"fin":"PostCode","ascending":true}],
	"allowAddNew":false,
	"isLookupInSelf":false,
	"filterCAML":"<Where><Eq><FieldRef Name='Account' LookupId='TRUE'/><Value Type='Lookup'>' + AccountID + '</Value></Eq></Where>",
	"setFields":[
		{
			"fromFIN":"Title",
			"toFIN":"Location",
			"parseFunction":"",
			"skipIfEmpty":false
		},
		{
			"fromFIN":"ID",
			"toFIN":"AccountLocation",
			"parseFunction":"",
			"skipIfEmpty":false
		}

	]					
});

In between the <Value Type=’Lookup’>’ + AccountID + ‘</Value> tags I have tried the following:

‘ + AccountID + ‘
” + AccountID + ”
‘” + AccountID + “‘
+ AccountID +
+ ‘AccountID’ +
+ “AccountID” +
[AccountID]

Nothing seems to work, is the issue that its only initially filtering the 2nd autocomplete field on page load? if so, is there a way to make the 2nd field recalculate based on when the filter value changes?

Thanks

[DougMcCourt]

Hi all – is there a way to use spsj-utility to get values from another list on the site based on a key value on the current NewForm? eg the user is on the NewForm for an Event (custom list) and I would like to look up 2 fields on another list, and set the corresponding fields on the NewForm? There really isn’t a parent / child relationship between the two lists. If needed I can have a hidden list view added to the NewForm – that list would show exactly one row for the user, and would have fields MyDivision and MyMarket that Id like to use to set the values of Division and Market on the NewForm On Form Load – thanks!

[Alexander Bautz]

I got a question about summing the values from another column in a list connected with a multi lookup column. Here is the request:

I have a Product list that contains columns for product [Title] and the [Install hours] for that product.

On our Orders list I have a look up column where multiple products can be added to an order.

Based on the items added to that order, I would like the Install hours (on the products list) to be summed in another field in the orders list.

Here is a code example to get you started:

function lookupExtraColumnData(){
	var a = [], b = [], c = 0;
	a.push("<Where>");
	a.push("<In>");
	a.push("<FieldRef Name='ID' />");
	a.push("<Values>");
	$("#dffs_LookupFieldName select:last option").each(function(i,o){
		b.push("<Value Type='Number'>"+$(o).val()+"</Value>");
	});
	a.push(b.join(""));
	a.push("</Values>");
	a.push("</In>");
	a.push("</Where>");
	if(b.length > 0){
		var res = spjs.utility.queryItems({"listName":"ProductListDisplayName","query":a.join(""),"viewFields":["InstallHours"]});
		$.each(res.items,function(i,item){
			if(item.InstallHours !== null){
				c += Number(item.InstallHours);
			}
		});
		// set the value in a field
		setFieldValue("SumFieldName",c);
	}
}

Add this function to the custom js textarea in DFFS backend, and call it for example on “save” using the “The form is saved” trigger.

You must change “LookupFieldName” for your lookup field name, the “ProductListDisplayName” with the display name or the GUID of the product list, the field name “InstallHours” with the actual field you want to sum, and the “SumFieldName” with the field to write the sum to.

Please note that the sum is written to a “static” field in the list item, and will not update on already added items if you update the “InstallHours” field in your products.

Hope this can be used as a staring point.

Alexander

[Alexander Bautz]

Hi,
Unfortunately there is no way to query into a list using JavaScript when the user doesn’t have read access. The only option I can think of where this might be possible is to use a SP Designer workflow running with an impersonation step to pull this extra information – but then the user must be able to input a “key” to user in the WF when looking up the correct record to fill in the values in the list item.

Another option could be to create the list with a “difficult” name, and hiding it from browsers in SP Designer (or using this tool). This will not prevent users typing in the correct address to the list accessing it, but it will “obfuscate” it.

Alexander

[cnibert]

Is it possible to make two separate lists work off of one another? I have two lists, the first one uses the user’s domain address to pull up all of the divisions and cost centers associated with that domain. The second list needs to look at the domain that was selected from the first list, and then propagate a field for software options related to each domain.

The reason I’m trying to use two separate lists is because with these options the list will be pretty huge as I’d have to have so many redundant lines in there to account for everything.

Here’s my debug info at the moment, it says number of items returned is 2 which is right, but the Software dropdown is still a single line of text for some reason. Also the CompanyEmailDomain is from the “Parent” list in this case:

[SPJS Cascading dropdowns v3.523]
Function called with these arguments:
lookupList:SoftwareCompanies
lookupListBaseUrl:{currentSite}
lookupListFields:Title,Software
thisListFields:CompanyEmailDomain,Software
dropDownDefaultvalue:
hideEmptyDropdowns:false
autoselectSingleOption:true
clearInvalidSelection:true
debug:true
numberOfDropdowns:2
Populating this field
Software
CAML query
<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>domain.com</Value></Eq></Where><OrderBy><FieldRef Name='Software' Ascending='TRUE'/></OrderBy>
Looking for values in this field
Software
Number of items returned
2

• This topic was modified 8 years, 6 months ago by cnibert.

[HTIC]

Hi Alex,

I’m hoping you can help. I have my charts set up in my CEWP (shown below) and I’m using the ‘spjs_getFilterValue’ function to return custom filter values which is shared between multiple charts using the filterAdditionalCharts’ option, and the ‘spjs_chartSelectionHandler’ function is used to select and return the data in a new window.

This works fine initially when I load the page and click on the chart data – the ‘spjs_chartSelectionHandler’ runs through and uses the URL to return the List data in a new window as expected. However, as soon as I select another value from the filter dropdown and repeat the same action (i.e. click on a chart item) the ‘spjs_chartSelectionHandler’ function seems to act like a loop which returns multiple instances of the same list data.

I’m not sure if it’s something to do with how I’ve written my code or the function itself but I would really appreciate your assistance?

<style type=”text/css”>
font-style:italic;
color:white;
border:1px silver solid;
background-color:#7DB0AF;
line-height:250px;
height:250px!important;
width:350px!important;
text-align:center;
margin:2px;
}
</style>

<div class=”spjs_chartPlaceholder_master”></div>
<script type=”text/javascript”>
// Set this to true to load the Google Visualization API release candidate
var loadRC = false;
// Set this to true to allow for the use of variables in the “Filter setup textarea”
var allowEval = true;
</script>
<script type=”text/javascript” src=”http://restricted.sharepoint/sites/SPJQ/jquery-1.11.3.min.js”></script&gt;
<script type=”text/javascript” src=”https://www.google.com/jsapi”></script&gt;
<script type=”text/javascript” src=”http://restricted.sharepoint/sites/SPJQ/spjs-charts-v4.14_min.js”></script&gt;

<script type=”text/javascript”>
function spjs_getFilterValue(list,field){
var q = “<Where><IsNotNull><FieldRef Name=’ID’ /></IsNotNull></Where><OrderBy><FieldRef Name='”+field+”‘ /></OrderBy>”;
var res = spjs.charts.qItems({“listName”:list,”query”:q,”viewFields”:[field]});
var b = [];
var fObj = {};
b.push({‘f’:’All’,’v’:’*’});
$.each(res.items,function(i,item){
if(fObj[item[field]] === undefined && fObj[item[field]] == null){
fObj[item[field]] = 1;
if(item[field] == “QF”){
b.push({“f”:”QF”,”v”:”QF”,”selected”: “true”});
} else {
b.push({“f”:””+item[field]+””,”v”:””+item[field]});
}
}
});
return b;
}
var myCustomFilterOptions = spjs_getFilterValue(“List_BF”,”DT”);
</script>

<script type=”text/javascript”>

function spjs_chartSelectionHandler(chartID, selection, data) {
var message, item, value, x, y, z, x1, x2;

if (selection.length > 0) {
item = selection[0];
message = “”;
value = “”;
switch (chartID) {
case ‘69743111-c1d0-4562-99e8-f72a85697457’:
if (item.row !== null) {
x = data.getFormattedValue(item.row, 0);
x = x.replace(” “, “%20”);
x = x.replace(“,”, “%2C”);
//console.log(x);
if (item.column !== null) {
z = data.getFormattedValue(item.row, item.column);
//console.log(z);
y = data.getColumnLabel(item.column);
//console.log(y);
}
}
if (x === undefined && y === undefined && z === undefined) {
return;
}
if (confirm(“Open filtered list?”)) {
window.open(‘http://restricted.sharepoint/***************************.aspx?&FilterField1=MOM&FilterValue1=&#8217; + x, ”, ‘width=1000’);
return;
}
break;

//case ‘b2bf592a-3c16-49dc-bae2-76d68b6e3d7a_0’:
case ‘69743111-c1d0-4562-99e8-f72a85697457_0’:
if (item.row !== null) {
x2 = data.getFormattedValue(item.row, 2);
x2 = x2.replace(” “, “%20”);
x2 = x2.replace(“,”, “%2C”);
//console.log(x2);

if (x2 === undefined) {
return;
}
}
if (confirm(“Open filtered list?”)) {
window.open(‘http://restricted.sharepoint/***************************.aspx?&FilterField1=Title&FilterValue1=&#8217; + x2, ”, ‘width=1000’);
return;
}
break;
default:
if (item.row !== null && item.column !== null) {
message = “{row:” + item.row + “,column:” + item.column + “,chart:” + chartID + “}”;
value = data.getValue(item.row, item.column);
if (message === “”) {
message = “nothing”;
}
alert(“You selected ” + message + “, the value is: ” + value);
}
//console.log(“Chart selection not configured.”);
break;
}
}
}
</script>