SPFilterDropdown CAMLQuery issue

[masterl1983]

Hi,

I am working in SP-Online and tried to use the SPFilterDropdown function. The caml below seems to be OK as it is working in other tools. But for same reason it is not working with SPServices.

Environment:

1. I have a Library called "Docs" with DocumentSets. I have added a Lookup field pointing to another List called "Processes"

2.) Within the List Processes I have created a LookUp field to "Docs" using the auto generated field "Related Process (Count Related)" and named it "RelatedDocSetCount".

The Dropdown should only show entries where the column "RelatedDocSetCount" is 0. So used the code below but now my dropdown is empty. Any ideas?

$().SPServices.SPFilterDropdown({ relationshipList: "Processes", relationshipListColumn: "Title", columnName: "RelatedProcess", CAMLQuery: "<Eq><FieldRef Name='RelatedDocSetCount'/><Value Type='Lookup'>0</Value></Eq>", completefunc: null, debug: true });

[masterl1983]

Any idea of what I did wrong?

[sympmarc]

Sorry for the delayed reply. I think you'll need to do a little debugging to see what's happening here, but the issue may be that your column is numeric. However, keep in mind that renaming a column doesn't change its InternalName. You should check to see what the InternalName for "Related Process (Count Related)" is and use that in your CAMLQuery.

[theJet]

I'm having the exact same issue. I've been searching high and low for the past two days and I've come up empty. I can verify that my issue is not related to the column name (internal/static) of the lookup column (I verified it using the 'change column' method where the static name is shown at the end of the url when you edit the column). I was able to use the REST api to filter on that column.

Thanks,
Ben

[sympmarc]

@theJet Can you post your code here? Perhaps we can come up with a solution together.

[theJet]

@sympmarc You bet!

Here's my code:
$().SPServices.SPFilterDropdown({ relationshipList: "Departments", relationshipListColumn: "Title", columnName: "Dept", CAMLQuery: "<Neq><FieldRef Name='Measures'/><Value Type='Lookup'>0</Value></Neq>", noneText: "< Select a Department >", debug: true });

My setup:
I'm using this code in the script editor of a new form to a list called Standard Data. The drop-down is being populated from another list called Departments in which the field "Title" holds the name of the department. In the department list I have another column called "Measures" that is a lookup (count related) field. That lookup/count is looking in the Measures list to count up how many measures are input for each department (the lookup count related is working great).

What I'm trying to do in the end here is limit the list of department names that show up in the drop-down box on the Standard Data form so that it's limited to just departments that have an entry in the Measures list. If the department does not have any measures assigned to it, they shouldn't be able to select that department (they need to go add a measure first).

Lastly, on the Standard Data list form I have a cascading drop-down feature using SPServices.SPCascadeDropdowns for the Department > Measure relationship. I was considering an attempt to add a level above Department and try to filter that list that way...

Thanks for the quick response & eyes on my code.
Best.

[sympmarc]

@theJet If you're using SPServices.SPCascadeDropdowns, you can add the filtering there instead. It may be that the two functions are conflicting with each other.

[theJet]

Thanks @sympmarc. I might try that. I was trying this on our test server which also couldn't run workflows. When I tried to perform a workflow on our production site I got it to work, so I have a workflow setup to copy over the value in that lookup (count related) field to another field which I can then use in a calculated field to filter down my drop-down to just Departments with one or more measures.

I can't tell you how much I appreciate you getting back to me.

[sympmarc]

If the Measures column is multi-select, I'm not sure the filter you're using will work. I think what might be returned in that case would be "". It's been a while since I used these functions. :) What version of SharePoint?