Can custom JS emulate SP "Reverse Lookup"?

[MikeS]

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 4 years, 8 months ago by MikeS.

[Alexander Bautz]

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

[MikeS]

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

Attachments:

1. 

   DFFS_Large_List_Reverse_Lookup_Schematic.png
   

[Alexander Bautz]

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

Alexander

[Alexander Bautz]

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

Alexander

[MikeS]

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 4 years, 8 months ago by MikeS. Reason: added attachment

Attachments:

1. 

   DFFS_Reverse_Lookup_Fail.png
   

[Alexander Bautz]

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

[Alexander Bautz]

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

[MikeS]

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 4 years, 7 months ago by MikeS.

[Alexander Bautz]

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

Thanks for letting me know,
Alexander

[MikeS]

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

[Alexander Bautz]

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

[MikeS]

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.

Attachments:

1. DFFS-Reverse-Lookup-Text-Fields.txt

[Alexander Bautz]

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

[MikeS]

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 4 years, 7 months ago by MikeS.
• This reply was modified 4 years, 7 months ago by MikeS.

[Alexander Bautz]

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

[MikeS]

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 4 years, 7 months ago by MikeS.

Attachments:

1. 

   DFFS_Reverse_Lookup_JS_error.png
   

[Alexander Bautz]

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

Attachments:

1. getRelatedItemsText_source_code.zip

[MikeS]

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

[Alexander Bautz]

I’m glad you got it running.

Alexander

[MikeS]

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

[Alexander Bautz]

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

[MikeS]

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 4 years, 7 months ago by MikeS.

[MikeS]

Ignore first diagram. Unable to delete.

Attachments:

1. DFFS-Reverse-Lookup-SharePoint-Lookup-script.zip

[Alexander Bautz]

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

Alexander

[MikeS]

Sure. See attached.
Mike

Attachments:

1. 

   DFFS_Reverse_Lookup_Standard_Lookup_Schematic.png
   

[Alexander Bautz]

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

[MikeS]

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

Attachments:

1. 

   DFFS_Large_List_Reverse_Lookup_Schematic.png
   

[MikeS]

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

Thanks
Mike

[Alexander Bautz]

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

[MikeS]

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

Mike

[Alexander Bautz]

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

[MikeS]

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

Attachments:

1. DFFS_Reverse_Lookup_error.zip

[Alexander Bautz]

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

Alexander

[Alexander Bautz]

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

[MikeS]

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

Mike

Attachments:

1. 

   Parent_TEST_Racks_List_Settings.png
   

2. 

   Child_TEST_Part_Number_List_Settings.png
   

[Alexander Bautz]

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

• This reply was modified 4 years, 6 months ago by Alexander Bautz.

[MikeS]

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

Mike

[Alexander Bautz]

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

[Author]

Posts