LOOKUP columns supported?

Forums SPJS Charts for SharePoint LOOKUP columns supported?

This topic contains 19 replies, has 2 voices, and was last updated by  Alexander Bautz 2 months ago.

  • 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: http://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

You must be logged in to reply to this topic.