0

I'm trying to figure out the sharepoint equivalent to the following SQL statement:

UPDATE List1, List2 SET List1.Field2 = List2.Field2
WHERE List1.Field1 = List2.Field1;

Or the Excel version:

=VLOOKUP(List1![Field1],List2,[Field2])

I'm using sharepoint lists as data storage with a Microsoft Access file as the user interface. I have the SQL statement above saved as a query within Access, but running update queries from Access is very slow due to poor network conditions and the lists having a large number of records. I'm not sure how to accomplish this in such a way that the end user is not editing the sharepoint lists directly. Thanks.

  • Is there a typo in your WHERE? Did you intend WHERE List1.Field1 = List2.Field1? – willman Jan 25 at 11:06
  • Which version of SharePoint are you using, and on-premises or online? – willman Jan 25 at 11:12
  • Yes thank you the typo has been corrected. I'm using SharePoint 2013 online – Kyle Quigley Jan 25 at 20:11
0

Unfortunately, Calculated columns cannot directly perform lookups in other tables, and if you added a relational Lookup column, you cannot reference Lookup columns from with a Calculated column formula.
However, creating a workflow (or a Power Automate Flow, depending on which version of SharePoint you are using) would be trivial.

  • Trigger your workflow on List1 item updated or modified
  • Get Value of Field2 from List2 by finding item by value of Field1
  • If the found value is different, then Update the value of Field2 on the triggered item

The If condition is important here as otherwise you could get stuck in a loop where updating the Item that triggered the workflow would re-trigger another instance of the workflow and so on.

| improve this answer | |
  • Thanks, that worked. Do you know if it's possible to reference partial strings? What I'm looking to do now is compare partial strings between the two Field1. I.e., update field2 if right(list1.field1,4)=right(list2.field1,4). It would be much easier if I knew how to edit the workflow in the same way as editing an SQL query – Kyle Quigley Jan 25 at 23:29
  • Are you using a Power Automate Flow, or a SharePoint Designer Workflow? – willman Jan 25 at 23:33
  • A SharePoint designer workflow. – Kyle Quigley Jan 27 at 16:16
  • The two SharePoint Designer actions that you want to look into are "Find substring in string" and "Extract substring from string with length" – willman Jan 27 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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