UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Table With Lookup, SharePoint 2013    
 
   
lex
post Apr 9 2019, 08:55 AM
Post#1



Posts: 752
Joined: 20-October 05



I'm on O365 and am updating via Access and would like to update a table that has a Lookup field.

How do I do this? If I mockup my update query as follows then View it, it doesn't look right. Any thoughts?

CODE
UPDATE Issues SET Issues.[Affected Work Areas].[Value] = 14
WHERE (((Issues.JobNumber)=111361));



Attached File  2019_04_09_9_54_18.jpg ( 22.1K )Number of downloads: 0


The above should all show "Macomb". 14 is the index in the lookup table.

thank you

Lex
Go to the top of the page
 
GroverParkGeorge
post Apr 9 2019, 09:04 AM
Post#2


UA Admin
Posts: 35,681
Joined: 20-June 02
From: Newcastle, WA


One of the reasons Lookup fields in tables are looked on with such disfavor is that they complicate life while adding little benefit. Perhaps this would be less opaque if you reverted to the standard approach for creating Foreign Key fields in tables, storing only the actual Foreign Key values, with no Lookups to hide what's actually happening.

Here, as a trouble-shooting step, I would make a copy of the table in question, run the update against that table and evaluate the results to see if it is actually going to produce acceptable results.

This is not SharePoint 2013, really, is it? If you have an Office 365 acount and are using MS Access as part of that subscription, it's simply Access, version O365. Is that not the case?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
lex
post Apr 9 2019, 09:51 AM
Post#3



Posts: 752
Joined: 20-October 05



Hi George! I pretty much know what you've said, but am trying to develop in both SharePoint and Access and chose to use some out of the box stuff in SharePoint.

I performed the update, and from Access Datasheet I see

Attached File  2019_04_09_10_43_41.jpg ( 43.47K )Number of downloads: 0


If I click in

Attached File  2019_04_09_10_44_35.jpg ( 20.45K )Number of downloads: 0


If I go into the lookup table

Attached File  2019_04_09_10_45_33.jpg ( 30.48K )Number of downloads: 0


If I go to SharePoint

Attached File  2019_04_09_10_46_58.jpg ( 122.5K )Number of downloads: 1


and

Attached File  2019_04_09_10_48_23.jpg ( 24.99K )Number of downloads: 1


very odd. looks like it's storing the "14" somewhere... but I don't know where. Seems like a waste of effort.

… and my version of Access says 365 ProPlus. Maybe your verbiage "Access, version O365" is more correct?

Thanks for your thoughts.
Go to the top of the page
 
GroverParkGeorge
post Apr 9 2019, 10:31 AM
Post#4


UA Admin
Posts: 35,681
Joined: 20-June 02
From: Newcastle, WA


I see. I misunderstood. This is not an Access table. This is a SharePoint list linked into Access. The only way to enforce something like RI in SharePoint lists is the lookup field mechanism.

Unfortunately, I'm not on top of that stuff and would have to look into it.

I think you're right about the versioning. Slightly different terminology than I used, but it is Office 365, as opposed to Office 2016 or 2019, etc.




--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
lex
post Apr 9 2019, 10:48 AM
Post#5



Posts: 752
Joined: 20-October 05



Sorry I should have said update list in my title. Thanks for your thoughts. Have a great day
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th September 2019 - 04:54 AM