1

What I have
I've got 2 lists (listA, listB). In listA there are several lookup columns to listB.

What I want to do
I want to create a query that updates a field in all items in listA from 'empty' to '1'. The field is not a lookup.

The problem
I have to fetch listB, otherwise I get an error when I run the query. But when both lists are imported I get the following error while running the query:
"The Microsoft Access database engine encountered an error while communicating with SharePoint. More detailed information: 'The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.'"

I don't understand why the number of lookups would be an issue since the query doesn't influance them. Is there any way to solve it?

Thanks!
Ania

3
  • You might need to handle this with a calculated column field which converts 'emptys' into '1s'. Though I'm a bit confused if that's what you are trying to achieve, or do you just want to solve the error message you received. These seem like two separate questions.
    – moe
    Commented Jul 26, 2018 at 9:22
  • Actually I need to change values in the entire column to start workflows on it, so your idea seems to be perfect for me. Do you have any suggestion as to what formula I could use?
    – Ania
    Commented Jul 26, 2018 at 9:31
  • 1
    There is a limit on the number of lookup columns you can have on a list, for SP 2013 which I use, the limit is 8. It's possible to override, I've not done this myself and I don't know if it would cause performance issues... For reference check out link and link
    – Tally
    Commented Jul 26, 2018 at 9:43

1 Answer 1

2

I want to create a query that updates a field in all items in listA from 'empty' to '1'. The field is not a lookup.

Instead of converting current column field values, you could create a new calculated column with a formula which references the column you'd get the values from.

If your columns have 'empty' in their inputs (as in a form of texts), then the formula would be something like

=IF([Column]="empty","1",[Column])

and if by 'empty' you mean actually blank values, then the formula would follow the logic of

=IF(ISBLANK([Column]),"1",[Column])

With ISBLANK the first output is (in here 1) used if the column is blank and the second if the column is not blank.

You can learn more about calculated columns at Microsoft's Calculated Field Formulas and Examples of common formulas in SharePoint Lists articles.

2
  • One more question: what is the data type returned by this formula? 'Single line of text' or 'Date and Time'?
    – Ania
    Commented Jul 26, 2018 at 10:47
  • Single line of text. A value "1" wouldn't work with Date and Time as itself. You may see some examples for how to use Date and Time data type in the 2nd link I provided.
    – moe
    Commented Jul 26, 2018 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.