SPJS-CASC Filter Options

Forums Requests SPJS-CASC Filter Options

Viewing 31 reply threads
  • Author
    Posts
    • #15657
      Kasey
      Participant

      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.

    • #15712
      Alexander Bautz
      Keymaster

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

      Best regards,
      Alexander

    • #19881
      Ivan Wilson
      Participant

      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?

    • #19908
      Alexander Bautz
      Keymaster

      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

    • #19914
      Ivan Wilson
      Participant

      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?

    • #19925
      Alexander Bautz
      Keymaster

      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

    • #20450
      Ivan Wilson
      Participant

      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?

      • #20468
        Alexander Bautz
        Keymaster

        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

    • #20481
      Ivan Wilson
      Participant

      Thank you Alexander, that works perfectly.

    • #20488
      Alexander Bautz
      Keymaster

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

      Alexander

    • #20494
      Ivan Wilson
      Participant

      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.

    • #20503
      Alexander Bautz
      Keymaster

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

      Alexander

    • #32474
      MikeS
      Participant

      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

    • #32476
      Alexander Bautz
      Keymaster

      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

      • #32480
        MikeS
        Participant

        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

      • #32482
        Alexander Bautz
        Keymaster

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

        Alexander

      • #32486
        MikeS
        Participant

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

        MikeS

      • #32488
        Alexander Bautz
        Keymaster

        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

    • #32478
      Alexander Bautz
      Keymaster

      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

    • #32484
      MikeS
      Participant

      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

      • #32492
        Alexander Bautz
        Keymaster

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

        Alexander

      • #32495
        MikeS
        Participant

        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

    • #32497
      Alexander Bautz
      Keymaster

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

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

      Alexander

    • #32505
      MikeS
      Participant

      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

    • #32511
      Alexander Bautz
      Keymaster

      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

    • #32518
      MikeS
      Participant

      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

    • #32520
      Alexander Bautz
      Keymaster

      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

    • #32522
      MikeS
      Participant

      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

      • #32524
        Alexander Bautz
        Keymaster

        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

    • #32526
      MikeS
      Participant

      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

      • #32528
        Alexander Bautz
        Keymaster

        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

    • #32536
      MikeS
      Participant

      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

    • #32542
      MikeS
      Participant
    • #32570
      Alexander Bautz
      Keymaster

      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

    • #32572
      MikeS
      Participant

      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

    • #32574
      Alexander Bautz
      Keymaster

      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

    • #32585
      MikeS
      Participant

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

      Much appreciated,
      MikeS

    • #32592
      Alexander Bautz
      Keymaster

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

      Alexander

    • #32804
      MikeS
      Participant

      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 ago by MikeS.
      • #32808
        Alexander Bautz
        Keymaster

        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

    • #32822
      MikeS
      Participant

      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 ago by MikeS.
      • This reply was modified 3 years ago by MikeS.
      • #32826
        Alexander Bautz
        Keymaster

        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

    • #32843
      MikeS
      Participant

      Thank you! This works perfectly.
      MikeS

Viewing 31 reply threads
  • You must be logged in to reply to this topic.