Cannot get multiple items using Lookup() function

  • 17 March 2017
  • 5 replies
  • 8 views

Badge +1

I'm using a lookup function in a Calculated Value control, it would then be sent to a JavaScript function:

getS23Delegates(lookup("Test","ID",CostCentre1,"S23Delegate",true))

The statement above would call the javascript function getS23Delegates() and send the parameter of the lookup result :

function getS23Delegates(value) {

alert(value);

}

The lookup would have to get results from the list called "Test", using CostCentre1 named control as my filter and grab the values from the list column "S23Delegate and I set the 5th parameter as true because I want to get multiple items from the "Test" list.

But I'm only getting the first item and not the second one. I'm supposed to be getting 2 S23Delegates. There might be something wrong in my code. I appreciate your help. Thanks


5 replies

Userlevel 5
Badge +14

since you filter by ID you can not get more then 1 item. ID is always unique.

can you post example of your data and desctibe what you want to get?

Badge +1

Thanks for your reply Marian. I have the following SharePoint list called Test :

S23Delegate      CostCentre      Amount

Person A            1001                  300.00

Person B            1001                  400.00

Person C            1003                  500.00

Person D            1003                  600.00

In my Nintex form, I have a drop down list that would choose which Cost Centre, If I choose 1001, it should alert pop up in my Javascript function as "Person A" and "Person B". So far in my code above, it will only pop up Person A and it will pop up twice. First Pop up is Person A and second popup is Person A again. What should I do to get Person A and Person B. Thanks

Userlevel 5
Badge +14

what are datatypes of 'CostCentre1' control and 'CostCentre' list field? are they lookups or choices?

Badge +1

Yes the CostCentre1 is a lookup from another list called CostCentre and the datatype of that column in the CostCentre is just a Text.

But it's all good now. I managed to find a workaround for this. I had to utilize the /vit_bin/List.asmx web service and the GetListItems() function of the web service with Xml nodes and caml. See example below of my code (I put it in JavaScript). The code would get the "Test" list and filter the Test list with my Cost Centre drop down lists using CAML. Another drop down list called ddlTest would populate the S23Delegate based on the result of the CAML.

function populateTest() {

var costCentre1 = NWF$('#' + ddlCostCentre1 + ' :selected').text();

var listName = "Test";
 makeSoapCall(listName, costCentre1);
}

function makeSoapCall(listName, cc1){ 


var soapEnv =
  "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'" +
  " xmlns:xsd='http://www.w3.org/2001/XMLSchema'
    xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>
    <soap:Body>
   <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>
     <listName>" + listName + "</listName>
     <viewName></viewName>
     <query><Query><Where><Eq><FieldRef Name='CostCentre' /><Value Type='Text'>" + cc1 + "</Value></Eq></Where></Query></query>
     <viewFields></viewFields>
     <rowLimit></rowLimit>
     <queryOptions><QueryOptions xmlns=''><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns><ViewAttributes Scope='RecursiveAll'/></QueryOptions></queryOptions>
   </GetListItems>
    </soap:Body>
  </soap:Envelope>";
  
  
        $.ajax({
            url: L_Menu_BaseUrl + "/_vti_bin/Lists.asmx",
            type: "POST",
            dataType: "xml",
            data: soapEnv,
            complete: processResult,
            contentType: "text/xml; charset=""utf-8"""
        });
 }
 
 function processResult(xData, status) {
  var result;
  if (isIE)
   result = xData.responseXML
  else
   result = xData.responseText
  
try {
NWF$("#" + ddlTest).empty();
   var i = 1;
   if (status == "success" && result){
   $("rs\:data", result).find("z\:row").each(function (i) {
   var s23Delegate = $(this).attr('ows_S23Delegate'); 
   var costCentre = $(this).attr('ows_CostCentre');
   i += 1;
   var options = "<option value=i>" + s23Delegate + "</option>";
   NWF$("#" + ddlTest).append(options);
});   }
}
  catch (e) {
 }
}

Userlevel 5
Badge +14

as per your original question (lookup function in calcuated control value):

since CostCentre1 control is lookup you have to get cost centre name/text first. I will assume cost centre name is the field that is being looked up for and filter by (so no additional lookup for cost centre name is needed). this you can achieve with parseLookup() runtime function like

parseLookup(CotCentre1,true)

output of this function you can then compare to CostCentre field from Test list within lookup function.

so you lookup function should look like

lookup("Test","CostCentre",parseLookup(CotCentre1,true),"S23Delegate",true)

General note: it's not a wise approach to compare lookup value to simple text field. with simple text field you can not ensure data are entered in proper form, do not have typos, extra or missing space, etc. this all can prevent successful match between text value and lookup value. the best would be to compare two lookups.

Reply