Creating a Report with Access and SharePoint list

0
votes

Linked SharePoint List table into Microsoft Access. One of my columns within the SharePoint list is "Person or Group" It is a lookup that has several attributes from SharePoint users. Those attributes include ID, Name, e-mail address. I use the e-mail address attribute for workflows within SharePoint. Now I want to create a report from the SharePoint List that print out all of the names that are assigned to each record/row. Instead of printing out the name it returns the ID number. How do I get the access report to return the name that is linked from the sharepoint list?

1
sqlsharepointms-access
Its hard to show code because it is in a SharePoint environment that the list table is linked too. Also the special attributes that the list item called "person or Group" has – punkdis

1 Answers

2
votes

Anytime you link to SharePoint site, if you look close, you will ALWAYS see a table (list) called UserInfo.

So, to get the user name in place of ID?

First setup a link to this userInfo table.

So on ribbon on External data (import and link), choose to link to a SharePoint list. Choose your site and then in the list of tables, choose the UserInfo one:

Eg:

enter image description here

Thus will thus setup a link to the UserInfo list/table.

Then, simply build a query (left join) from the ID column of the user.

The query will look like this:

enter image description here

Build a query that left joins in UserInfo.

However, do note that if you look at the table, you don't see the extra columns, but you do in the query builder. Note that SharePoint editor is added to EVERY table. so note these fields for EVERY table:

enter image description here

So you well might not even require to use a query joined to the UserInfo table since as above shows in Access ALL tables have the SharePoint editor automatic added to every linked table.

However, linking to the UserInfo table based on the "id" as you have should allow you to display the "name" in place id.