Multiple filters from same datasource (CAML)

[Paul Lynch]

Hi Alexander

On latest DFFS build (as of 5th November 2020)

I have logic that hides or shows a tab (depending on choices made in form for different option in fields).
Each tab has different fields (in main form/shown tab) that capture the cascade choices.
Each tab has different cascading field options – via a filtering the SAME datasource (a SP List called SCENARIOS).
Both the first level and second level are “multi” cascades.

The datasource (a SharePoint list called SCENARIOS) is setup with three columns (they are not lookup columns).

Column 1 (feed the cascading drop down level 1)
Column 2 (feed the cascading drop down level 2)
Column 3 is a choice field (multiple) – which is used to filter the options in the cascade.
Column 4 is a choice field (multiple) – which is used to filter the options in the cascade

Everything is working absolutely fine for simple filtering.

I found the info from another post, or perhaps the built in help, to filter an individual column with one option..

{“filterField”:”Column 3″,”filterValue”:”option1″,”operator”:”substringof”}

I can filter by a single option in Column 3.

But there are two filters I need to add.

1) Filter IF Column 3 is option1, option3, or option3
2) Filter IF Column 3 is option 1 AND Column 4 is option-x or option-y or option-x

Is this possible modifying the above filter, or do I need to write CAML. Is there a tool/link you can recommend to help me write it?

Hope this makes sense, if not I can send additional config/screenshots.

Many Thanks,
Paul

[Paul Lynch]

Hi Alexander,

I have figured out the CAML queries with help from an excellent and free client side CAML query builder/checker QuickCAML, which works with SharePoint Online (even with SPO multi factor activated which stops many local clients working).

The only question I have now is that at present I am using different cascading drop down fields (in parent form) to collect/store choices for different tabs (as each cascading drop down has different filtered choices some REST some are now CAML) – these are shown via rules.

Ideally I would store the data in the same fields for all tabs, but I am not sure how to differentiate between which cascades run and which do not.

Do not think this is possible in the cascading drop down tab in the DFFS config (backend).

Guess I would have to invoke the function manually, when a rule shows a tab, it runs a specific cascading drop down and filter for that field.

I’ve been trying to get the datasource and invoke function to work,

var res = spjs.utility.queryItems({"SCENARIOS":"CascInitialFilterSource", "query":"<Where> <Or><Contains><FieldRef Name="Column3" /><Value Type="Text">Option1</Value></Contains><Contains> <FieldRef Name="Column3" /><Value Type="Text">Option3</Value></Contains></Or></Where>"

It is this line which I am confused about, as I am not sure which entries to change in second part of the datasource..

viewFields":["Title", "FieldValue", "LookupValue"]});

Here is an example of my working CAML query, with a simple OR filter on one column, the list is called SCENARIOS.. not sure if this helps

<View>
    <Query>
        <Where>
            <Or>
            <Contains> 
                <FieldRef Name="Column3" />
<Value Type="Text">Option1</Value>
            </Contains>
               <Contains> 
                <FieldRef Name="Column3" />
<Value Type="Text">Option3</Value>
            </Contains>
            </Or>
        </Where>
    </Query>
    <ViewFields>
        <FieldRef Name="Column3"/>
    </ViewFields>
</View>

• This reply was modified 3 years, 5 months ago by Paul Lynch.
• This reply was modified 3 years, 5 months ago by Paul Lynch.

[Alexander Bautz]

Hi,
I’m not 100% sure I understand, but look at this code and see if you might be able to use it. It will trigger on change of a dropdown choice “YOUR_FIELD_NAME” and call the function to rebuild the cascading dropdowns.

Read through the snippet and change field names and the case in the switch to match.

Alexander

function init_cascading_dropdown(){
    var opt = getFieldValue("YOUR_FIELD_NAME");
    // Remove casc if already added
    spjs.casc.kill("ThisListField1");
    var filterCAML = "";
    switch(opt){
        case "option1":
            filterCAML = "<Where><Or><Contains><FieldRef Name='Column3' /><Value Type='Text'>Option1</Value></Contains><Contains><FieldRef Name='Column3' /><Value Type='Text'>Option3</Value></Contains></Or></Where>";
        break;
        case "option2":
            filterCAML = "<Where><Or><Contains><FieldRef Name='Column3' /><Value Type='Text'>OptionA</Value></Contains><Contains><FieldRef Name='Column3' /><Value Type='Text'>OptionB</Value></Contains></Or></Where>";
        break;
        case "option3":
            filterCAML = "<Where><Or><Contains><FieldRef Name='Column3' /><Value Type='Text'>OptionX</Value></Contains><Contains><FieldRef Name='Column3' /><Value Type='Text'>OptionY</Value></Contains></Or></Where>";
        break;
    }
    spjs.casc.init(
        {
            "manualMode": true,
            "dataSource": "",
            "lookupList": "SCENARIOS",
            "lookupListBaseUrl": "/Sites/YourSite",
            "lookupListFields": ["SourceField1", "SourceField2"],
            "thisListFields": ["ThisListField1", "ThisListField2"],
            "setFields": [],
            "dropDownDefaultvalue": "Select...",
            "filter": filterCAML,
            "hideEmptyDropdowns": false,
            "autoselectSingleOption": true,
            "clearInvalidSelection": false,
            "addOwnValue": false,
            "addOwnValueMouseover": "",
            "cancelOwnValueMouseover": "",
            "sideBySide": true,
            "multichoiceDelimiter": ";\d",
            "debug": false
        }
    );
}

// Attach a change event on the choice field
jQuery("#dffs_YOUR_FIELD_NAME select").on("change", function(){
    init_cascading_dropdown();
});

[Paul Lynch]

Thanks Alexander, will work through this and let you know the outcome

[Author]

Posts