Issues with using a second list as data source

Hi there,

I am trying to use a secondary sharepoint list as another data source for one of my charts and used (this post on the documentation site.

However I have a slightly different scenario than described in the post.
Namely my oData query does not work of the ID field but rather uses another shared field based on SAP groups. The reason for this is that one list contains more entries than the other so the ID's wouldn't be identical.

Snippet

renderer.config.additionalList.listServerRelativeUrl = "/MySite/MyList";
renderer.config.additionalList.queryFunc = function (itemData){
  return "(SAP_x0020_Group_x0020_No eq " + itemData["SAP_x0020_Group_x0020_No"] + ")";
}

However the query doesn't seem to work as the custom html I wrote for the boxes doesn't get populated an none of onBoxRendered events trigger. There seems to be an error with how i set it up because all other custom render events i made also stop functioning when trying to implement this.

Appreciate any help and constructive feedback!
Thank you

Hi @Nick_B
I believe your code is almost there!
Here's the problem: in the OData filter query the string values should be put inside the quotation marks. So, this line:

return "(SAP_x0020_Group_x0020_No eq " + itemData["SAP_x0020_Group_x0020_No"] + ")";

should be replaced by this:

return "(SAP_x0020_Group_x0020_No eq '" + itemData["SAP_x0020_Group_x0020_No"] + "')";

Note there's extra single quote character after "eq" and before ")"
I hope this helps.

Also, please try to use the full path to the list.
So, if you list it here:

"something.sharepoint.com/sites/Mysite/Lists/Mylist"

use the following::

"/sites/Mysite/Lists/Mylist"

Hi @v.uspenskii,

Thanks a lot for helping with my second question today :stuck_out_tongue:

Sadly this time your suggestion does not seem to be solving my issue. I tried your fix with the added ' but when trying to show the information in my box using

<div class="field-container ">
  	test: {{pochContext.listData.SAP_x0020_Group_x0020_N}}
  </div>

I still get no output at all.

When trying to use a custom formatting rules for box background color it disables all other formatting...

if(itemData.pochContext.SAP_x0020_Group_x0020_No == 'MyNumber'){
    box.$elem.addClass('my-box-style');            
}

Also just a thing to confirmed in the first part of my oData query (the part containing eq) this references my second list, while the itemData['info'] refers to the column in my current list, correct?

Maybe you could let the admins know that there's an error in their documentation article as well? It's a bit confusing for newbies like myself when RTFM doesn't work.

Thanks again for being so kind to help me twice today!

Hi @Nick_B,
actually, I don't think there's an error in the docs. I just reproduced the example similar to yours. Here's my setup:

  1. This is the main list:

  2. This is the additional list, I added some additional info for some of the Job titles in the main list. The internal column name here is the same ("JobTitle"), but it may not be.
    image

  3. Here's my code to connect with the list:

renderer.config.additionalList.listServerRelativeUrl = "sites/OrgChart/Lists/GOT additional";
renderer.config.additionalList.queryFunc = function (itemData){
  return "(JobTitle eq '" + itemData["JobTitle"] + "')";
}

Also, I added the following container to the Box template:

   <div class="field-container ">
    {{pochContext.listData.Description}}
  </div>

Here pochContext.listData.Description token takes data from the "Description" column in the additional list.

  1. Here's the result:
    image

I believe that you might want to debug a bit. I would suggest o start with the following:
Make sure you use the correct names for the itemData object and the correct column name for the additional list. This should be the internal column name! You can copy it from the URL of the Column settings:

If this doesn't help, please drop a message to [email protected] and refer to this topic, the Plumsail team will do its best to help you debug!