LOOKUP columns supported?

[Paul Lynch]

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

[Alexander Bautz]

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

[Paul Lynch]

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.

[Paul Lynch]

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!

[Alexander Bautz]

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

[Paul Lynch]

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)

Attachments:

1. 

   charthelp1.png
   

2. 

   charthelp2.png
   

[Alexander Bautz]

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

[Paul Lynch]

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:

1. 

   charthelp3.png
   

[Alexander Bautz]

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

[Paul Lynch]

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

Attachments:

1. 

   charthelp4.png
   

2. 

   charthelp5.png
   

[Alexander Bautz]

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

[Paul Lynch]

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.

[Paul Lynch]

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.

[Paul Lynch]

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

[Alexander Bautz]

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

[Paul Lynch]

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!

Attachments:

1. 

   charthelp6.png
   

2. 

   charthelp7.png
   

[Alexander Bautz]

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

Attachments:

1. 

   views_obj_console.png
   

[Paul Lynch]

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:

1. 

   charthelp8.png
   

[Alexander Bautz]

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

[Alexander Bautz]

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

[Author]

Posts