SPJS-CASC Filter Options

[Kasey]

Would you consider adding a “Filter Field” and “Filter Value” to the Cascading Drop downs plugin? This way you could use one list for multiple Cascading Drop Down configurations ore even use it to temporarily disable options by adding an Active flag field.

I emailed this to you but wanted to post it for your records.

Love the recent enhancements. Thank you for continuing to improve upon your already excellent solution.

[Alexander Bautz]

This is a good idea and I’ll try to get it done soon.

Best regards,
Alexander

[Ivan Wilson]

Is there any trick to apply more than one filter for a cascading dropdown, even if it needs to be done when the results have been returned?

[Alexander Bautz]

I’m not sure I understand exactly what you mean, but if you need to change the dataset based on other fields in your list you can call the function manually as described here.

Alexander

[Ivan Wilson]

Can you use multiple filter criteria when you call spjs.casc.init? I tried using the following syntax for the filter option, but the operation failed:

“filter”:[{“filterField”:”Title”,”filterValue”:”testing”,”operator”:”ne”},{“filterField”:”Country”,”filterValue”:”New Zealand”,”operator”:”eq”}]

Or is there another option for updating the results of this call?

[Alexander Bautz]

Sorry, but only one filter criteria can be used at the moment. Maybe you can create a calculated column in the list to filter on?

Alexander

[Ivan Wilson]

Unfortunately I need to filter based on the properties of the current user, so I can’t implement as a calculated column.

I’m trying to use a Jquery statement to remove the option from the DOM, but I’m having problems figuring out where to place this. I’ve tried calling it from a rule that runs when the form is ready or initialized, but in both cases, it seems like the DOM hasn’t been populated with the cascading dropdown values yet. Any suggestions?

[Alexander Bautz]

I’ve added an option to use a CAML query. You find the file here. Replace the file in the “/SPJS/DFFS/plugins” folder and change your filter parameter to a CAML query.

Let me know how this works out.

Alexander

[Ivan Wilson]

Thank you Alexander, that works perfectly.

[Alexander Bautz]

Thanks for the feedback – I’m glad it worked out.

Alexander

[Ivan Wilson]

Hi Alexander, I think there is an issue with the updated code if you use the standard filter criteria. The error appears in line 184 in the call to spjs.casc.init:

`var c, s, err = false, camlFilter = b.filter.charAt(0) === “<” ? true : false;

the b variable contains an object, which seems to fail on the b.filter.charAt(0) statement.

[Alexander Bautz]

Ah – forgot that the filter was an object when using it in Custom JS. I have updated the file to v3.7.13.

Alexander

[MikeS]

Alex,

I’m using the manual CASC implementation (spjs.casc.init) in custom JS and trying to implement the following. Note that this CASC implementation is looking up to the same list with multi-select.

1. Set filter to another field (a SharePoint OOTB single lookup field – but could convert this to a single-line plain text if needed) in the record so only those options that contain the filter appear in the selection box, basically a dynamic filter. Not sure if CAML can do that. I looked at Multiple filters from same datasource (CAML) — perhaps that could be adapted? I’ve also looked at SPJS-Lookup but that may be more than I need in combination with spjs.casc.init

2. Once an option is selected automatically auto select all secondary options – not just if only a single option is found (as the menu-driven CASC allows).

Thanks,
MikeS

[Alexander Bautz]

You can do it like this:

var lookupValue = getFieldValue("LookupColumn1");
var query = "<Where><Eq><FieldRef Name='LookupColumn2' LookupId='FALSE' /><Value Type='Lookup'>"+lookupValue+"</Value></Eq></Where>";

spjs.casc.init({
 ...,
 ...,
 ...,
 "filter":query,
 ...,
 ...
})

In this example LookupColumn1 is the single lookup field and LookupColumn2 is the multi-lookup field.

Alexander

[MikeS]

How can I compare just the first 12 characters of LookupColumn2 to LookupColumn1? (LookupColumn2 is an aggregation of metadata that includes LookupColumn1 as a prefix.)

Thanks
MikeS

[Alexander Bautz]

This is unfortunately not possible when querying a lookup column – you can only use “equals” and not “beginswith” or “contains”.

Alexander

[MikeS]

I can easily copy the lookup column to a plain text field. How would a CONTAINS query work then?

MikeS

[Alexander Bautz]

When querying a multiline text field you can use contains – like this.

var query = "<Where><Contains><FieldRef Name='Your_MultiLineTextField' /><Value Type='Text'>"+lookupValue+"</Value></Contains></Where>";

Alexander

[Alexander Bautz]

Forgot question 2:
There is not built in code to select all items in a lookup column, but it can be done with custom.

I’m not sure I understand what you are trying to do, but if you are planning to write all items to a multiline text field you might as well skip the cascading dropdown and just use custom js to get the items and write them with code – if you can explain in more detail what you are trying to do I’ll see if this might be a better option.

Alexander

[MikeS]

I have a parent item with multiple children that are related to the parent by a lookup column. (These children originate independently of the parent and thus vLookup will not work as I am not generating the children from the parent.)

Each child has a multi-line text field that must be pulled forward to the parent thru DFFS multi-select. ** When the multi-line text field is changed in the child it would be optimal if that change was immediately reflected in the parent (like SharePoint OOTB changes).**

Lacking that ability with current spjs.casc.init code, the user edits the parent and re-selects the children in the multi-select box and then the changes in the multi-line text field are pulled into the parent.

Thanks,
MikeS

[Alexander Bautz]

You can use vLookup to show there items if you set up the query like this:

Alexander

[MikeS]

A regular vLookup would work as it would dynamically update in the parent when a child is updated. I would then use JS to write the updated child fields to a single multi-line parent field.

I’m just having a bit of a problem with the vLookup setup. Some details:

1. This is a vLookup into the same list (no child list). Does that require the [currentItem:FIN] format as shown in the tooltip?
2. However, the lookup field (Title field) is a single line text field in which only the first 12 characters provide the match needed for the vLookup. Seems like this would require use of the CAML-query section of the vLookup. Would the same format,

var query = "<Where><Contains><FieldRef Name='Your_MultiLineTextField' /><Value Type='Text'>"+lookupValue+"</Value></Contains></Where>";

then be inserted? Tried this and a number of configurations and keep getting ‘no items found.’

Thanks,
MikeS

[Alexander Bautz]

1: Yes, same format.
2: Like this:

<Where><Contains><FieldRef Name='Title' /><Value Type='Text'>[currentItem:FIN]</Value></Contains></Where>

Alexander

[MikeS]

Thank you Alex, this is working well. A few more tweaks are needed.

Is this caution from the vLookup manual a consideration if our internal list threshold for an on-site SP 2013 is 15,000 items?: “Please note that you cannot use the default lookup column connection if your parent list ever exceeds 5,000 items.”

1. Can vLookup be customized in such a way that it will only absorb the child data based on a string found in the Title field? This will allow only the parent to display the children found in the same list. Now the children and the parent all display the same information which would confuse the user. I could hide the Rollup Tab for the children and just display it for the parent, but that seems less than optimal. Your thoughts?

2. Set value from vLookup table to a field in the current form would allow a single line displayed in a vLookup table to be inserted into a field in the current item. Is there a way to take multiple multi-line values (from the children) shown in the vLookup table and automatically aggregate them in a single multi-line text field in the current (parent) item with a pipe delimiter between each child’s value?

Thanks
MikeS

[Alexander Bautz]

The 5000 items threshold only applies to SharePoint online (where it cannot be changed).
1: The CAML query I posted in my previous comment will only return the items where the Title of the child contains the value found in the field specified in [currentItem:FIN].

2: You can use custom js to find the value from the vLookup table and then append it to your current form like this:

function vLookupIsLoadedCallback(fin){
    if(fin === "vLookupTasks"){
        var arr = [];
        jQuery("#dffs_vLookupTasks .vLookupTableRow").each(function(i, row){
            arr.push(jQuery(row).find(".childTableCell:eq(1)").text());
        });
        setFieldValue("MultilineField", arr.join("|"));
    }
}

Replace vLookupTasks with your vLookup field internal name, MultilineField with your multiline text field internal name and the 1 in .childTableCell:eq(1) with the column index from the left (starts on 0).

Please note that this will only run when the vLookup table is rendered (when it is visible in a tab).

Alexander

[MikeS]

The script in No. 2 above is working well in the Edit form.

Can a CAML-like query be placed in this script so that it only runs based on characters (CONTAINS) in a CurrentItem:FIN field?

Thanks
MikeS

[Alexander Bautz]

I don’t understand what you mean. The vLookup table is populated based on the query you set up in the vLookup config – doesn’t this mean the table is already filtered to show the correct items?

Alexander

[MikeS]

Here is an example of the Title field:
Part1,All [parent]
Part1,A [child]
Part1,B [child]
Part1,C [child]
Part2,All [parent]
Part2,A [child]
Part2,B [child]
Part2,C [child]

The vLookup CAML query is working well by filtering on the ‘Part1’ or ‘Part2’ common to the family of items in this single list. However, each child as well as the parent renders all of the items, when only the parent should display the children AND the parent in the vLookup table. The children should not render the vLookup table.

MikeS

[Alexander Bautz]

I’m still not sure I understand, but the vLookup CAML query can take multiple filters like this:

<Where><And><Contains><FieldRef Name='OneField' /><Value Type='Text'>[currentItem:FIN]</Value></Contains><Contains><FieldRef Name='AnotherField' /><Value Type='Text'>[currentItem:AnotherFIN]</Value></Contains></And></Where>

Wouldn’t that work?

Alexander

[MikeS]

Unfortunately that enhanced CAML query results in only the parent being rendered in the vLookup table. I need the parent and children to be rendered for only the parent item.

I can set up a rule that will trigger on a parent specific field. That should then render the vLookup table properly. However, when I place the “function vLookupIsLoadedCallback” JS in the “Run these functions” after a {inline} field nothing displays in the multiline field. Does that script need a tweak to run in that location?

MikeS

[Alexander Bautz]

I’m sorry, but I really don’t understand. If your parent items have a specific text in a field and the child items don’t you should be able to only load the vLookup table for the parent items if you set up a CAML query.

If the vLookup field is visible in the tab also for the child items it would be empty. You could of course hide the field based on a rule triggering on the field that only the parent has a specific value in to ensure the child items don’t see the vLookup table.

If this does not make sense I need you to show me some screenshots of the difference between a parent and a child and a bit more details so I can understand the relationship between the items.

Alexander

[MikeS]

There are six screen shots attached that should help clarify the issue. The last screenshot of the CHILD should not run the vLookup and/or there should be no aggregation of content in the PartDescriptionCombo field. Only the PARENT should show CHILDREN and aggregate content in the PartDescriptionCombo field.

Thanks for your help,
MikeS

Attachments:

1. 

   01-List-view.png
   

2. 

   02-vLookup-config-1.png
   

3. 

   03-vLookup-config-2.png
   

4. 

   04-Custom-JS.png
   

[MikeS]

Last two attachments

MikeS

Attachments:

1. 

   05-PARENT-EDIT-form.png
   

2. 

   06-CHILD-EDIT-form-PartDescriptionCombo-field-should-be-blank.png
   

[Alexander Bautz]

What I still don’t fully understand is how you are supposed to know which items are parent and child when you look at the list items in a list view.

To be able to stop the code from running – and hide the vLookup for the child items you need to have for example a yes/no checkbox field named “IsParent= Yes” for parents and “IsParent=No” for children.

If you have this you can use a CAML like this in the vLookup:

<Where><And><BeginsWith><FieldRef Name='Title' /><Value Type='Text'>[currentItem:BaseRack]</Value></BeginsWith><Eq><FieldRef Name='IsParent' /><Value Type='Boolean'>0</Value></Contains></And></Where>

This query will find all items where the Title begins with the current items BaseRack AND the IsParent field = No.

Also you can use this check in the custom js function like this:

function vLookupIsLoadedCallback(fin){
    if(getFieldValue("IsParent")){ // Only run this for the parent item
        if(fin === "vLookupParentRollup"){
            var arr = [];
            jQuery("#dffs_vLookupParentRollup .vLookupTableRow").each(function(i, row){
                arr.push(jQuery(row).find(".childTableCell:eq(2)").text());
            });
            setFieldValue("PartDescriptionCombo", arr.join("|"));
        }
    }
}

Alexander

[MikeS]

Parent items are identified in two ways:

• Rack Code=AA
• Rack no includes the string “Parent”

Using your modified CAML query and adding the IsParent field (1=true) returns only the Parent record in vLookup for Parent or Children items. Both the Parent and the Children should be returned in vLookup for the Parent item.

MikeS

[Alexander Bautz]

OK, then you can use the RackCode field to control it. Remove the IsParent field and use the RackCode field to control the visibility of the vLookup filed in your form by setting up a DFFS rule hiding the field if the RackCode is not AA.

Now change the CAML like this to show all where the Title starts with the BaseRack name:

<Where><BeginsWith><FieldRef Name='Title' /><Value Type='Text'>[currentItem:BaseRack]</Value></BeginsWith></Where>

Change the Custom JS code like this (Change RackCode to match the internal name of your field):

function vLookupIsLoadedCallback(fin) {
    if (fin === "vLookupParentRollup") {
        if (getFieldValue("RackCode") === "AA") { // Only run this for the parent item
            var arr = [];
            jQuery("#dffs_vLookupParentRollup .vLookupTableRow").each(function (i, row) {
                arr.push(jQuery(row).find(".childTableCell:eq(2)").text());
            });
            setFieldValue("PartDescriptionCombo", arr.join("|"));
        }
    }
}

This will now hide the vLookupParentRollup field for child items and also only write to the PartDescriptionCombo field for the parent items.

Please note that there is no way to fill the vLookup table for only the parent items so if you do NOT hide the vLookupParentRollup in the child items with the DFFS rule it will still keep listing the items in the child items as well as in the parent.

Alexander

[MikeS]

That nailed it! Works perfectly. The “if (getFieldValue(…” statement in the vLookupIsLoadedCallback function was instrumental.

Much appreciated,
MikeS

[Alexander Bautz]

Thanks for the feedback – I’m glad you got it worked out.

Alexander

[MikeS]

Alexander,

I need to include a childTableCell column in the JS below for purposes of aggregating it in the PartDescriptionCombo field. However, I need to hide the same childTableCell column on the form (with CSS or JS I assume) as the user does not wish to view it. How could that be accomplished?

function vLookupIsLoadedCallback(fin) {
    if (fin === "vLookupParentRollup") {
        if (getFieldValue("RackCode") === "AA") { // Only run this for the parent item
            var arr = [];
            jQuery("#dffs_vLookupParentRollup .vLookupTableRow").each(function (i, row) {
                arr.push(jQuery(row).find(".childTableCell:eq(2)").text());
            });
            setFieldValue("PartDescriptionCombo", arr.join("|"));
        }
    }
}

Thanks for your help,
MikeS

• This reply was modified 3 years, 10 months ago by MikeS.

[Alexander Bautz]

I have not added a class to the header that you can address directly, but you can do it like this (change vLookupTasks for your vLookup field name):

/* Hide header - the number 6 is the count of columns from left*/
#vLookupTablePlaceholder_vLookupTasks tr .childTableHeader:nth-child(6){
}

/* Hide body - change vLookupTasks for your vLookup field name and vLookupTasks_Status to match your vLookup field name and the child list field internal name separated with _  */
#vLookupTablePlaceholder_vLookupTasks .vLookupTasks_Status{
    display:none;
}

Alexander

• This reply was modified 3 years, 10 months ago by Alexander Bautz.

[MikeS]

Alexander,

Having a bit of problem integrating these statements

/* Hide header - the number 6 is the count of columns from left*/
#vLookupTablePlaceholder_vLookupTasks tr .childTableHeader:nth-child(6){
}

/* Hide body - change vLookupTasks for your vLookup field name and vLookupTasks_Status to match your vLookup field name and the child list field internal name separated with _  */
#vLookupTablePlaceholder_vLookupTasks .vLookupTasks_Status{
    display:none;
}

with the larger code:

function vLookupIsLoadedCallback(fin) {
    if (fin === "vLookupParentRollup") {
        if (getFieldValue("RackCode") === "AA") { // Only run this for the parent item
            var arr = [];
            jQuery("#dffs_vLookupParentRollup .vLookupTableRow").each(function (i, row) {
                arr.push(jQuery(row).find(".childTableCell:eq(2)").text());
            });
            setFieldValue("PartDescriptionCombo", arr.join("|"));
        }
    }
}

Where do they go?

Also, do I replace vLookupTasks with my vLookup field as so: vLookupMyField, or is it vMyField?

Thanks
MikeS

• This reply was modified 3 years, 10 months ago by MikeS.
• This reply was modified 3 years, 10 months ago by MikeS.

[Alexander Bautz]

The code I send is for Custom CSS and not Custom JS.

Replace vLookupTasks with the internal name of your vLookup field – you find the internal name in the Fields tab in your DFFS config.

Alexander

[MikeS]

Thank you! This works perfectly.
MikeS

[Author]

Posts