LOOKUP columns supported?

Forums SPJS Charts for SharePoint LOOKUP columns supported?

Viewing 19 reply threads
  • Author
    Posts
    • #22208
      Paul Lynch
      Participant

      Hi Alex,

      Does the current version of charts support lookup columns from a SharePoint list?

      I’ve tried to use one in a basic table, but when I add it the form is just stuck loading.

      I’m using data type string for each field, there are two fields, this is second field (index 1). Both have default Roles.

      Any idea, or are these lookup columns to other lists not supported?

      Kind Regards,
      Paul

    • #22239
      Alexander Bautz
      Keymaster

      Hi,
      It seems I had a bug related to lookup columns. I have fixed it in v7.0.0.9 – please let me know how this works out for you.

      Alexander

    • #22329
      Paul Lynch
      Participant

      Thanks Alex,

      I upgraded to version 7.0.0.9 but still have the same issue. Just seems to get stuck on the loading screen when I add a lookup.

    • #22331
      Paul Lynch
      Participant

      PS Other info

      • I reset the chart, before rebuilding from scratch.
        I’m using a basic table with two fields (Title – String) and (Lookup – String)
        The lookup column is to a list in same site, with 7 different choices.


      I CAN get the lookup field to show in my table I switch the Data Type from String to Boolean (Yes/No) – it just shows as a tick for every record!

    • #22333
      Alexander Bautz
      Keymaster

      Can you email me some screenshots?

      1. Is the lookup single choice or multichoice?
      2. Which field is shown in the lookup column (the Title field or another field from the lookup list)?
      3. Are you using A CAML filter in the Filters tab?

      Alexander

    • #22335
      Paul Lynch
      Participant

      Is the lookup single choice or multichoice? – no multiple values selected

      Which field is shown in the lookup column (the Title field or another field from the lookup list)? – Title field

      Are you using A CAML filter in the Filters tab? – no

      (see attached screen shots)

    • #22342
      Alexander Bautz
      Keymaster

      Try applying a CAML filter (by selecting av view in the dropdown in the Filters tab). This makes the query run as a webservice call and not as a REST API call and these two handle lookup in different ways.

      I have both working in my setup, but it might be something I haven’t taken into account.

      Is the lookup list in the same site as the table chart you are trying to create?

      Alexander

    • #22344
      Paul Lynch
      Participant

      Strange I have no filters I can choose in the optional filter (under filter tab) – see image attached. I created a view in the lookup list to see if that had any effect and it didnt.

      Yes both the lists are on the same site.

      Attachments:
    • #22353
      Alexander Bautz
      Keymaster

      Are you sure you have a view with a filter to select from (the AllItems.aspx view does not have a filter by default and cannot be selected in this drowdown).

      Alexander

    • #22355
      Paul Lynch
      Participant

      Sorry yes I did not add a filter to the view, I did now, just filtered by ID >is greater than 0

      I can now see the “Filtered View” as an option. Although when I select it the CAML generated is “undefined” – and an error now pops up when chart loads.

      Hope this is progress! (copy pasted errors

    • #22370
      Alexander Bautz
      Keymaster

      Not sure why the CAML isn’t pulled from the view, but try typing it in manually – like this (pulls in all items):

      <Where><IsNotNull><FieldRef Name="ID" /></IsNotNull></Where>

      If if behaves better with a CAML query it makes it a bit easier to look for the bug.

      Alexander

    • #22376
      Paul Lynch
      Participant

      It seems to work fine now, if I change the data type of lookup field from “String” to a “Split options in separate series”. I think on the older version of charts, I tried this but it didnt work, so had dismissed it.

      I created a new web part page, which when I matched original configuration of old chart
      web part page, and set String for lookup, but this time an error popped up telling me not to use a string, hence it now worked!

      Although on both charts, I still cannot get the CAML to populate automatically from drop down. So you’re manual CAML code is helpful thanks.

      Thank you for persisting with this.

    • #22378
      Paul Lynch
      Participant

      I actually need to filter out some data in the look up column. So really need the CAML working if possible.

      To make a specific filter, I thought I had to update the view of the chart, that the CAML pulls from, which I have done. Unfortunately if I try to then select CAML drop down, top update the code, it just populates a single word “undefined” in the query box.

      So for me to get this to work I will have to manually write the exact CAML query for my filter, which I am not sure how!

      Perhaps I can try this without a lookup column and modify the CAML from another chart test page.

      It would be good to get this working again though.

    • #22380
      Paul Lynch
      Participant

      Just to let you know, the CAML Query drop down does not work for me. even without using any lookup columns!

    • #22402
      Alexander Bautz
      Keymaster

      Hi,
      I’m not sure why pulling the view CAML doesn’t work – are you on SP on prem or Office 365?

      Can you try bringing up the developer console (hit F12 and select Console) and type in this and hit enter:

      spjs.charts.data.listViews

      If you get an object back – expand it and post the code or an image here.

      Even if pulling the CAML from the view doesn’t work you can write the CAML manually. If you need help, just describe what you want to filter on – and give me the internal name of your field and I’ll write the CAML for you.

      PS: Thanks for the beer!

      Best regards,
      Alexander

    • #22404
      Paul Lynch
      Participant

      This is an Office 365 E3 licensed SharePoint Online Team Site

      I’ve attached the console image (although some object arrays are cut off not sure how important they are)..

      Also I pressed F12 on the chart page, whilst in edit chart view. (Not sure if that is relevant).

      _________________________________________________________________________________

      In order to get this chart ready for me to demo

      I’d like to filter the chart to only show the entries in the list where the
      “lookup column name” – (internal fieldname) is as below..
      “Portal Status” – (Deployed)

      This looks up another list called “Chart List”, picks the Title field (internal fieldname is also Title).

      3 Title values I wish to show;
      Identified
      In development
      Internal

      _________________________________________________________________

      Separate question – is there any function in the chart software to rename returned values? E.g. if I were to rename the above “Internal” as something else like “Deprecated”. Or at least rename their values in a legend?

      Thanks!

    • #22415
      Alexander Bautz
      Keymaster

      Hi,
      Your fields internal name is not correct – you can find it by following these instructions: https://spjsblog.com/general-tips/

      The CAML should look something like this:

      <Where><Eq><FieldRef Name="Put_Your_Lookup_Column_Name_Here" /><Value Type="Text">Pur_Your_Filter_Value_Here</Value></Eq></Where>

      In the console output for spjs.charts.data.listViews I’m actually looking for the items circled in red in the attachment – please expand one of them.

      Alexander

    • #22419
      Paul Lynch
      Participant

      It can expand quite massively tbh so not sure if this image is any good.

      ________________________________________________________________________

      Thanks I was able to work out the CAML to manually add the filters with your code!

      
      
      <Where>
              <Or>
                  <Eq>
                      <FieldRef Name='Deployed' ></FieldRef>
                      <Value Type='Text'>Internal</Value>
                  </Eq>
                  <Or>
                      <Eq>
                      <FieldRef Name='Deployed' ></FieldRef>
                          <Value Type='Text'>Identified</Value>
                      </Eq>
                      <Eq>
                      <FieldRef Name='Deployed' ></FieldRef>
                          <Value Type='Text'>In development</Value>
                      </Eq>
                  </Or>
              </Or>
      </Where>
      Attachments:
    • #22464
      Alexander Bautz
      Keymaster

      The screenshot is OK – I see that the caml property is empty. Not sure why it is, but I’ll see if I might be able to figure it out for the next release.

      I’m glad you got the CAML sorted out manually though.

      Alexander

    • #22469
      Alexander Bautz
      Keymaster

      Could you hit F12 and bring up the dev console in one of your chart pages and run this snippet:

      jQuery.ajax({
          url: _spPageContextInfo.siteAbsoluteUrl + "/_api/lists?$select=Id,Title,RootFolder,ParentWebUrl,Views&$expand=RootFolder,Views",
          method: "GET",
          headers: {
              "Accept": "application/json; odata=verbose"
          },
          success: function (data) {
              jQuery.each(data.d.results, function (i, l) {
                  jQuery.each(l.Views.results, function (j, v) {
                      console.log(v.ViewQuery);
                  });
              });
          },
          error: function (err) {
              console.log(err);
          }
      });

      It should write out a bunch of ViewQueries – let me know if it lists anything looking like a CAML query.

      Alexander

Viewing 19 reply threads
  • You must be logged in to reply to this topic.