Can custom JS emulate SP "Reverse Lookup"?

Forums Dynamic Forms for SharePoint Can custom JS emulate SP "Reverse Lookup"?

This topic contains 38 replies, has 2 voices, and was last updated by  Alexander Bautz 1 week, 4 days ago.

  • Author
    Posts
  • #26427

    Mike
    Participant

    Alexander,

    The OOTB Reverse Lookup in SharePoint is very useful for several use cases. Using DFFS cascading dropdowns means that capability is lost. Is there any way to emulate reverse lookup when using DFFS cascading dropdowns with large lists (over 5K), perhaps with some custom JavaScript? Something that works with a single choice or multiple choice?

    Thanks for your help,
    Mike

    • This topic was modified 2 months, 3 weeks ago by  Mike.
  • #26457

    Alexander Bautz
    Keymaster

    Yes, this is possible. If you can tell me which field in “list A” to use when searching for a match in “list B” I can create a little code snippet you can use as a starting point.

    Alexander

  • #26634

    Mike
    Participant

    I have described the DFFS reverse lookup use cases in the attached diagram so the requirements and use cases could be as clear as possible. Let me know if any questions. Thanks for your help.

    Mike

  • #26667

    Alexander Bautz
    Keymaster

    Thanks for the specifications. I’ll see if I can get this looked at during the weekend.

    Alexander

  • #26704

    Alexander Bautz
    Keymaster

    Look here for a possible solution: https://spjsblog.com/2019/08/16/dffs-reverse-lookup/

    Alexander

  • #26784

    Mike
    Participant

    Picking up the conversation here from the blog post so I can add attachments and formatted code.

    The script is returning what is shown in the attachment regardless of the parameters entered in the function call.

    Setup
    Referring back to the diagram: The Ticket list is my Child list and the Part Number list is my Parent list. The Child list lookup field to the Parent (Part Number) list is “DFFS_PN_Lookup” and is configured in the DFFS Cascading dropdowns Edit panel for this list. I then assign the same Part Number to various Tickets via this lookup field.

    I have configured the Part Number Parent list DFFS Display form with the Custom JS, CSS, and a Tab ID “ReverseLookup.” I have configured a Rule that calls the function (init_getRelatedItems) when the Display form tab is loaded.

    When I click the Parent list Reverse Lookup tab I get no matches, even though there should be several.

    • This reply was modified 1 month, 4 weeks ago by  Mike. Reason: added attachment
    • #26793

      Alexander Bautz
      Keymaster

      Hi,
      I misunderstood and thought your DFFS_PN_Lookup was an actual lookup column. I’ll look at this later tonight and write a new function that can be used with text field connections instead of a lookup column connection.

      Alexander

  • #26800

    Alexander Bautz
    Keymaster

    I have now updated the article with a new method you can use with the cascading dropdown connection – let me know how it works out.

    Alexander

  • #26844

    Mike
    Participant

    I’m getting the following error when clicking the Reverse Lookup tab in the Parent list after incorporating the code for common text cascading dropdown:

    DFFS: Configuration error in “Run these functions / trigger these rules”. Ensure you use the correct function name or rule friendly name.

    The rule “RunReverseLookupScript” tried to invoke the function:
    “init_getRelatedItemsText”

    Error:
    TypeError: window[f] is not a function

    Note that there appeared to be a few extra characters at the very end of this line in the code:

    “filter”: “<Where><Contains><FieldRef Name=’DFFS_PN_Lookup’ /><Value Type=’Text’>”+getFieldValue(“Title”)+”;</Value></Contains></Where>”;
    “,

    I had to delete the characters

    ;”

    to get rid of the JS error messages.

    Mike

    • This reply was modified 1 month, 3 weeks ago by  Mike.
    • #26862

      Alexander Bautz
      Keymaster

      Sorry about that – I have fixed it in the article.

      Thanks for letting me know,
      Alexander

  • #26879

    Mike
    Participant

    Same error persists:

    DFFS: Configuration error in “Run these functions / trigger these rules”. Ensure you use the correct function name or rule friendly name.

    The rule “RunReverseLookupScript” tried to invoke the function:
    “init_getRelatedItemsText”

    Error:
    TypeError: window[f] is not a function

    • #26881

      Alexander Bautz
      Keymaster

      I tested the original code snippet that I modified earlier today and it seems OK to me. If you can attach your code here (in a text file as attachment) I can look at it and see if I can spot the error.

      Also, you can test it here to see if you have any obvious syntax errors: https://jshint.com/

      Alexander

  • #26889

    Mike
    Participant

    See attached.

    I’m calling this from a Rule with:

    init_getRelatedItems

    Having trouble wrapping the

    function init_getRelatedItems (){
    // Call getRelatedItems inside
    getRelatedItems({
    “placeholderID”: “ReverseLookup”,


    });
    }

    snippet around the function.

  • #26897

    Alexander Bautz
    Keymaster

    I’m a bit confused – you have no function named init_getRelatedItemsText in the attached text file, but the function getRelatedItemsText is called from the dffs_ready function. Maybe you can remove the call to init_getRelatedItemsText from your rule?

    Alexander

  • #26899

    Mike
    Participant

    Just about there . . .
    I modified the initial steps of the function call as follows:

    function init_getRelatedItemsText (){
    getRelatedItemsText({
    “placeholderID”:”ReverseLookup”,

    and it is called by the following rule:

    init_getRelatedItemsText

    However, the results below (from the Parent list Reverse Lookup tab) indicate the raw data is correct but not picking up the CSS formatting for some reason. Any ideas?

    <table class=’dffs_relatedItems_table’><tr class=’dffs_relatedItems_headerRow’><td class=’dffs_relatedItems_headerCell’>Title</td><td class=’dffs_relatedItems_headerCell’>Author</td><td class=’dffs_relatedItems_headerCell’>Created</td></tr><tr class=’dffs_relatedItems_row’><td class=’dffs_relatedItems_cell’>TN-001</td><td class=’dffs_relatedItems_cell’>Mike</td><td class=’dffs_relatedItems_cell’>11/20/2018</td></tr><tr class=’dffs_relatedItems_row’><td class=’dffs_relatedItems_cell’>TN-004</td><td class=’dffs_relatedItems_cell’>Mike</td><td class=’dffs_relatedItems_cell’>11/20/2018</td></tr></table>

    Mike

    • This reply was modified 1 month, 3 weeks ago by  Mike.
    • This reply was modified 1 month, 3 weeks ago by  Mike.
  • #26911

    Alexander Bautz
    Keymaster

    This might be a problem related to copying and pasting the code from the article. If it outputs the raw HTML code I guess the formatting of the < and > has somehow been interpreted as plain text and not proper HTML brackets. Try doing a search and replace in for example Notepad to ensure they are the correct type. Also, from your code snippet it looks like the quotes are the wrong type also.

    Alexander

  • #26937

    Mike
    Participant

    I recopied the raw HTML into the DFFS Custom JS from the page source. I ended up with an error as shown in the attachment. Not obvious to me how to recover from this as need to escape the “>” and it’s throwing that error.

    Mike

    • This reply was modified 1 month, 2 weeks ago by  Mike.
  • #26947

    Alexander Bautz
    Keymaster

    Hi,
    I’m not sure why the format is off – maybe it is related to the browser you use to copy the code. Please try the attached code where I have verified the format and let me know how it works out.

    Alexander

  • #26958

    Mike
    Participant

    Success! Just modified the function with the call I make from a rule:

    // Function call – change parameters here
    function init_getRelatedItemsText (){
    getRelatedItemsText({
    “placeholderID”: “ReverseLookup”,

    and now the reverse lookup works. Doing some additional testing with different data combinations and use cases.

    A big thank you,
    Mike

  • #26964

    Alexander Bautz
    Keymaster

    I’m glad you got it running.

    Alexander

  • #26969

    Mike
    Participant

    I’m having a bit of trouble getting the other script to work (SharePoint lookup column between parent and child).

    The reverse lookup from the Parent (small list) to the Child (large list) always returns “There are no related items.” So script is executing but not seeing the related items that were associated using a SharePoint lookup column in the Child list looking up into the Parent list.

    Any ideas?

    Mike

  • #26983

    Alexander Bautz
    Keymaster

    Are you sure you have specified the correct filter? – if you can attach (or email me) some screenshots of the lists you try to connect (and the fields used to connect them) along with your code I can take a look and see if I can see what the problem is.

    Alexander

  • #27028

    Mike
    Participant

    The attached diagram indicates the fields and interconnects between two lists (Large child list and a parent list).

    The attached text file is the Custom JS from the Parent list. A Parent list Rule calls the script from ‘Run these functions’ with the entry: init_getRelatedItems.

    The Reverse lookup from the Parent list to the Child list always returns a blank. No errors and no text.

    Thanks for your help.
    Mike

    • This reply was modified 1 month, 1 week ago by  Mike.
  • #27033

    Mike
    Participant

    Ignore first diagram. Unable to delete.

  • #27041

    Alexander Bautz
    Keymaster

    Sorry, I accidentally removed both diagrams because I thought the zip file contained the new one. Can you post the diagram again?

    Alexander

  • #27043

    Mike
    Participant

    Sure. See attached.
    Mike

  • #27066

    Alexander Bautz
    Keymaster

    I think the problem is filtering on multichoice lookup columns with REST. I tested this on Office 365, but it might not work if you are on SP on premises.

    Try changing the filter like this:

    "filter": "DFFS_Rack_Lookup/Id eq '" + spjs.dffs.data.thisItemID + "'",

    If this does not work, use the getRelatedItemsText function instead, and use this query:

    "filter": "<Where><Eq><FieldRef Name='DFFS_Rack_Lookup' /><Value Type='LookupMulti' LookupId='TRUE'>"+spjs.dffs.data.thisItemID+";</Value></Eq></Where>",

    Let me know how this works out.

    Alexander

  • #27068

    Mike
    Participant

    I am using SP 2013 on premises. 15K Large list threshold.

    The first solution returns a blank reverse lookup (no change from original problem).

    The second solution returns “There are no related items” constantly. I assumed the second solution was to use the complete script that I had working for the other case (i.e., DFFS Cascading dropdowns with common text strings) and use the query above.

    Note that I had two typos in my diagram. See attached Parent and Child corrections in BOLD text.

    Mike

  • #27114

    Mike
    Participant

    I’ve tried a few other things but to no avail. Can you help me with my post from Sep 11?

    Thanks
    Mike

  • #27133

    Alexander Bautz
    Keymaster

    Hi,
    I think I need to see it live to be able to help you out here. Are you able to share your screen with me in a meeting sometime next week? – email me and we can find a time.

    Alexander

  • #27143

    Mike
    Participant

    Sharing the screen is not possible for this local installation. Are there screen shots that might be helpful?

    Mike

  • #27145

    Alexander Bautz
    Keymaster

    I would need screenshots of both the lists (list settings and form) so I can recreate them exactly like yours in my test site. I will also need the current custom js you have in the forms (the parts relevant to this script).

    I suspect part of the problem is that your local SP does not support the same functionality that SharePoint online does.

    Alexander

  • #27186

    Mike
    Participant

    I have attached a ZIP file with screen shots, Custom JS, and a diagram. They are numbered in the order that I think would be most helpful for reviewing the issue and match the diagram.

    These results were captured on lists using the latest DFFS version (4.4.4.10) on a local installation of SP 2013. The setup is pretty straight forward in both lists. Let me know if you need anything else.

    Thanks for your help,
    Mike

  • #27189

    Alexander Bautz
    Keymaster

    Thanks,
    I’ll get this looked at later this week and get back to you.

    Alexander

  • #27227

    Alexander Bautz
    Keymaster

    Sorry for the late reply. I looked at it now, but to replicate your lists I need to see a screenshot of List settings for each of the lists where I can see the list name and the name of all the fields (please mask the full URL to your site in the screenshot).

    Alexander

  • #27260

    Mike
    Participant

    I’ve attached a suitably masked screen shot of the list settings page for each list (Parent and Child).

    Mike

  • #27276

    Alexander Bautz
    Keymaster

    I have replicated the lists from your screenshots and tested your code – you must change:

    "filter": "Title eq '" + spjs.dffs.data.thisItemID + "'"

    to

    "filter": "RackNo eq '" + spjs.dffs.data.thisItemID + "'"

    Please note that you might have to specify the RackNo field like this (there is some difference in on-prem and office 365):

    "filter": "RackNo/Id eq '" + spjs.dffs.data.thisItemID + "'"

    in this code

    function init_getRelatedItems(){
        getRelatedItems({
        "placeholderID": "ReverseLookup",
        "listName": "{4B365BC6-1580-497E-BFC4-67CA4F998BAC}", // Child list GUID
        "listBaseUrl": _spPageContextInfo.webServerRelativeUrl,
        "filter": "RackNo eq '" + spjs.dffs.data.thisItemID + "'", // Please note that the field you want to filter by must be indexed if your list has more than 5000 items
        "select": ["Title", "Author/Id", "Author/Title", "Created"],
        "expand": ["Author"],
        "linkToItem": {
        ...
        ...

    This is because it is the RackNo field that is the Lookup to RackNo in the TEST_Racks list and in this field you find the item id.

    Let me know if you have further questions.

    Alexander

  • #27286

    Mike
    Participant

    Unfortunately it now returns a blank reverse lookup. Any tips to try?

    Mike

  • #27292

    Alexander Bautz
    Keymaster

    It’s unfortunately extremely difficult to do this kind of troubleshooting without looking at it live. Based on your images and code example it seems like the connection is by lookup column, and in this case the filter needs to use RackNo (or RackNo/Id) and not Title like I described in my previous comment.

    To debug further you must use the browser developer tools to intersect the function and look at what’s going on. You can start by adding this to the top of your getRelatedItems function:

    debugger;

    This will pause the script at this point, and you can use f10 or f8 to step forward in the code – click the line number in the margin to set a breakpoint. Hover over variables – like “res” to see it’s content and see if you can figure out what’s going on.

    Alexander

You must be logged in to reply to this topic.