chewwbutta
Design Dabbler

Calculated Field Failing Against Large List

Hello!

I had a question regarding SharePoint calculated field lookups:

1. I'm trying to perform a lookup from my form to another list within the same collection, but it's failing to return results. It's worth noting that the list I'm attempting to look against has exceeded the view threshold. Would this impact the result (returning #Value!). It's a simple lookup and has been written correctly as I tested against other lists.

2. Is there any special handling that needs done for special field characters? Example: "#", "@", "$", etc.

Thank you!!

0 Kudos
Reply
3 Replies
gman
Process Pupil

Re: Calculated Field Failing Against Large List

chewwbutta,

 

The label on this indicates "workflow", but your description mentions "form", so not sure this will help. When performing queries on a lookup list we've run into issues with items containing special characters (in our case "&"). To resolve this for queries on local lists/libs we enable the the "XML encode inserted tokens" option.

When performing remote queries with the CAML editor, we have to enclose the variable as follows using a Nintex function:

fn-XmlEncode({TextStart}{WorkflowVariable:txtLookupVar}{TextEnd})

 

Hope this helps.

Gman

0 Kudos
Reply
chewwbutta
Design Dabbler

Re: Calculated Field Failing Against Large List

Thanks, @gman, I appreciate it! And that's great to know!

 

To explain the confusion: I have a dynamic form that I'm trying to perform a lookup against a list that has 50k+ items in it. I have tried to use a calculated form field and perform a lookup but my assumption, based on testing, is that because the list exceeds the view threshold it fails. I think tried to think of firing a workflow to query the list and return a true value if a matching item was found, but that also fails due to the list view threshold.

 

So now I'm really stuck because I can't think of a meaningful way to execute the lookup through either a field or a workflow. 

 

Does that make sense? Any other tricks up those sleeves? 

0 Kudos
Reply
gman
Process Pupil

Re: Calculated Field Failing Against Large List

Yes, a couple of things. We work with libraries with that many items and more. One solution is to turn up the list view threshold to a value greater than the list contents. This is for on-prem SP2013-19.

 

Another possibility is to see if you can query the list in its current state using powershell. There is a specific query method using list.GetItemByIdSelectedFields($fileID, "FileLeafRef"); which yielded the fastest response (less than a second) compared to others that were anywhere from 2 - 43 seconds.

 

Now if Powershell will work for you, the next question is how will this help you in a workflow. Well there is a great third-party Nintex Workflow action by DataOne called PowerActivity action. It provides you with PowerShell extension to Nintex workflow. Together they can do anything. It takes a little to setup, but it allows us to complete 98% of the functionality we need - all from within the workflow structure.

 

Good luck.

0 Kudos
Reply