sharepoint [user information list] column names

Question

I am able to query the [User Information List] via code in Visual Studio but I cannot find the actual column names for the table. I can also work with this table via Access 2007 Linked to SharePoint. It uses friendly names as opposed to the actual names like "ows_name" etc. Is there a list somewhere that has the actual default column names? I can access this list via code or Access 2007 linked tables but for some reason CAML query builder and Stramit.CamlViewer2007 give me access denied. I also get the same message if I go to http://thewebsite/_catalogs/users/simple.aspx .

So I am unable to see the column names. In code I am not having luck guessing the names. Of course it woudl have been great for Microsoft to just have a field that is the name called name but that would be to easy :)

Answer 1

You will have to be a site collection administrator to view any of those fields with any of the tools.

---

certdev.com

Answer 2

Is the [User Information List] setup with a standard set of columns on all installs? I just need the field names.

In Access 2007 I can query for the fields I need with the following:

strSQLstring =

"SELECT ID, [Content Type], Name, Account, [E-Mail], Picture, Department, [Job Title] FROM [User Information List] WHERE ([Content Type] = 'Person') ORDER BY Name";

This is what I need to pull out of the list.


I am trying to do this via SharePoint Web Services now but it is getting strange results. The field names are different in the Database so things are showing up or not at all.



ows_ID comes out correct. ows_Title shows the persons name instead of ows_Name. ows_Name is showing the username/domain accoutn instead. I just don't know the field names.


I am testing with:

 

string str_ows_ID = "";
string str_ows_Name = "";

ResumeDatabaseLists.

Lists myservice = new ResumeDatabaseLists.Lists();

 

// default credentials - this is the person logged in.

 

//myservice.Credentials = System.Net.CredentialCache.DefaultCredentials;

 

// Account with full control

myservice.Credentials =

new NetworkCredential("userr", "pass", "domain");

myservice.Url =

"http://website/HR/_vti_bin/lists.asmx";

 

string listName = "{0000000-EF88-4B0D-932B-92A0BC64E79F}";

 

string viewName = string.Empty;

 

string rowLimit = "100";

 

 

// Instantiate an XmlDocument object

System.Xml.

XmlDocument xmlDoc = new System.Xml.XmlDocument();

System.Xml.

XmlElement query = xmlDoc.CreateElement("Query");

System.Xml.

XmlElement viewFields = xmlDoc.CreateElement("ViewFields");

System.Xml.

XmlElement queryOptions = xmlDoc.CreateElement("QueryOptions");

strCAMLquery = strCAMLquery +

"<Query>";

strCAMLquery +=

"<Where>";

strCAMLquery +=

"<IsNotNull>";

strCAMLquery +=

"<FieldRef Name='ID' />";

strCAMLquery +=

"</IsNotNull>";

strCAMLquery +=

"</Where>";

strCAMLquery +=

"</Query>";

 

/*Use CAML query*/

query.InnerXml = strCAMLquery;

 

// FIELDS

viewFields.InnerXml =

"<FieldRef Name='ID' /><FieldRef Name='Name' />";

queryOptions.InnerXml =

"";

 

 

System.Xml.XmlNode nodes = myservice.GetListItems(listName, viewName, query, viewFields, rowLimit, null, null);

 

foreach (System.Xml.XmlNode node in nodes)

{

 

if (node.Name == "rs:data")

{

 

for (int i = 0; i < node.ChildNodes.Count; i++)

{

 

 

 

 

// DISPLAY THE RESULTS

 

if (node.ChildNodes[i].Name == "z:row")

{

 

 

 

 

if (node.ChildNodes[i].Attributes["ows_ID"] == null)

{

str_ows_ID =

"ID Missing";

}

 

else

{

str_ows_ID = node.ChildNodes[i].Attributes[

"ows_ID"].Value;

}

 

 

 

 

if (node.ChildNodes[i].Attributes["ows_Name"] == null)

{

str_ows_Name =

"Name Missing";

}

 

else

{

str_ows_Name = node.ChildNodes[i].Attributes[

"ows_Name"].Value;

strClean = str_ows_Name.Substring(str_ows_Name.IndexOf(

"#") + 1);

str_ows_Name = strClean;

}

 

 

 

// need to get this from USER INFORMATION LIST JOB_TITLE

 

if (node.ChildNodes[i].Attributes["ows_Title"] == null)

{

str_ows_Position_x0020_Category =

"Title Missing";

}

 

else

{

str_ows_Position_x0020_Category = node.ChildNodes[i].Attributes[

"ows_Title"].Value;

strClean = str_ows_Position_x0020_Category.Substring(str_ows_Position_x0020_Category.IndexOf(

"#") + 1);

str_ows_Position_x0020_Category = strClean;

}

 

 

 

 

if (node.ChildNodes[i].Attributes["ows_Modified"] == null)

{

str_ows_Modified =

"Modified Missing";

}

 

else

{

str_ows_Modified = node.ChildNodes[i].Attributes[

"ows_Modified"].Value;

strClean = str_ows_Modified;

str_ows_Modified = strClean;

}

 

 

if (node.ChildNodes[i].Attributes["ows_Biographical_x0020_Sketch"] == null)

{

str_ows_Biographical_x0020_Sketch =

"Bio Missing";

}

 

else

{

str_ows_Biographical_x0020_Sketch = node.ChildNodes[i].Attributes[

"ows_Biographical_x0020_Sketch"].Value;

 

//strClean = str_ows_Modified;

 

//str_ows_Modified = strClean;

}

 

 

 

 

 

strSharepointListsXML +=

"<div align='center'>" + strNL;

strSharepointListsXML +=

"<table border='1' width='770' cellspacing='5' cellpadding='5'>" + strNL;

strSharepointListsXML +=

"<tr>" + strNL;

strSharepointListsXML +=

"<td align='center' width='20'><b><font color='#000080'>" + str_ows_ID + "</font></b></td>" + strNL;

strSharepointListsXML +=

"<td align='center' width='225'><b><font color='#000080'>" + str_ows_Name + "</font></b></td>" + strNL;

strSharepointListsXML +=

"<td align='center' width='269'><b><font color='#000080'>" + str_ows_Position_x0020_Category + "</font></b></td>" + strNL;

strSharepointListsXML +=

"<td align='center' width='181'><font color='#000080'>" + str_ows_Modified + "</b></font></td>" + strNL;

strSharepointListsXML +=

"</tr>" + strNL;

strSharepointListsXML +=

"<tr>" + strNL;

strSharepointListsXML +=

"<td align='left' colspan='4'>" + strNL;

strSharepointListsXML +=

"<b><font color='#000080'>BIOLOGICAL SKETCH:</font></b><br>" + strNL;

strSharepointListsXML += str_ows_Biographical_x0020_Sketch + strNL; ;

strSharepointListsXML +=

"</td>" + strNL;

strSharepointListsXML +=

"</tr>" + strNL;

strSharepointListsXML +=

"</table>" + strNL;

strSharepointListsXML +=

"<br>" + strNL;

strSharepointListsXML +=

"<br>" + strNL;

strSharepointListsXML +=

"</div>" + strNL;

 

 

}

 

 

}

}

}

// END foreach (System.Xml.XmlNode node in nodes)

Answer 3

Hi belcherman,

 

Thanks for you post.

 

I think you can get the column name of the “User Information List” by SharePoint OM.  Here is some sample code, hope it’s helpful.

public void GetUserInfoColumns(string WebUrl)

        {

            SPSecurity.RunWithElevatedPrivileges(delegate()

            {

                using (SPSite site = new SPSite(WebUrl))

                {

                    using (SPWeb web = site.OpenWeb())

                    {

                        SPList userList = web.Lists["User Information List"];

                        SPFieldCollection fieldColl = userList.Fields;

                        foreach (SPField field in fieldColl)

                        {

                            Console.WriteLine("Name:{0};Type:{1}", field.Title, field.TypeAsString);

                        }

                    }

                }               

            });

        }

And this is the result:

Name:Content Type ID;Type:ContentTypeId

Name:Name;Type:Text

Name:Approver Comments;Type:Note

Name:File Type;Type:Text

Name:Account;Type:Text

Name:Work e-mail;Type:Text

Name:About me;Type:Note

Name:SIP Address;Type:Text

Name:Locale;Type:Integer

Name:Calendar Type;Type:Integer

Name:AdjustHijriDays;Type:Integer

Name:TimeZone;Type:Integer

Name:Time Format;Type:Boolean

Name:Alternate Calendar Type;Type:Integer

Name:CalendarView Options;Type:Integer

Name:WorkDays;Type:Integer

Name:WorkDay StartHour;Type:Integer

Name:WorkDay EndHour;Type:Integer

Name:Is Site Admin;Type:Boolean

Name:Deleted;Type:Boolean

Name:Picture;Type:URL

Name:Department;Type:Text

Name:Title;Type:Text

Name:Is Active;Type:Boolean

Name:First name;Type:Text

Name:Last name;Type:Text

Name:Name;Type:Computed

Name:Work phone;Type:Text

Name:Office;Type:Text

Name:User name;Type:Text

Name:Web site;Type:URL

Name:Responsibilities;Type:Note

Name:MyWork Email;Type:Note

Name:ID;Type:Counter

Name:Content Type;Type:Text

Name:Modified;Type:DateTime

Name:Created;Type:DateTime

Name:Created By;Type:User

Name:Modified By;Type:User

Name:Has Copy Destinations;Type:Boolean

Name:Copy Source;Type:Text

Name:owshiddenversion;Type:Integer

Name:Workflow Version;Type:Integer

Name:UI Version;Type:Integer

Name:Version;Type:Text

Name:Attachments;Type:Attachments

Name:Approval Status;Type:ModStat

Name:Edit;Type:Computed

Name:Name;Type:Computed

Name:Select;Type:Computed

Name:Instance ID;Type:Integer

Name:Order;Type:Number

Name:GUID;Type:Guid

Name:Workflow Instance ID;Type:Guid

Name:URL Path;Type:Lookup

Name:Path;Type:Lookup

Name:Modified;Type:Lookup

Name:Created;Type:Lookup

Name:Item Type;Type:Lookup

Name:Effective Permissions Mask;Type:Computed

Name:Name;Type:File

Name:Unique Id;Type:Lookup

Name:ProgId;Type:Lookup

Name:ScopeId;Type:Lookup

Name:HTML File Type;Type:Computed

Name:Edit Menu Table Start;Type:Computed

Name:Edit Menu Table End;Type:Computed

Name:Name;Type:Computed

Name:Name;Type:Computed

Name:Type;Type:Computed

Name:Server Relative URL;Type:Computed

Name:Encoded Absolute URL;Type:Computed

Name:File Name;Type:Computed

Name:Property Bag;Type:Lookup

Name:Level;Type:Integer

Name:Is Current Version;Type:Boolean

Name:Group;Type:Computed

Name:Edit;Type:Computed

Name:Name;Type:Computed

Name:Picture;Type:Computed

Name:Name;Type:Computed

Name:Name;Type:Computed

Name:Edit;Type:Computed

Name:Selection Checkbox;Type:Computed

Name:Content Type;Type:Computed

Answer 4

Hey THanks, I also figured out by going to MSDN that when using CAML and XML that you need to prefix the internal field name with "ows_". Once I had the internal names and then did my checks with the prefix it all worked.

Answer 5

Has anybody tried using SSIS and the SharepointList Source dataflow task for this? I think it should work more straightforward..

Just a thought..

Cheers'

Answer 6

StaticName = ContentTypeId , Title = ID do Tipo de Conteúdo, Type = ContentTypeId
StaticName = Title , Title = Nome, Type = Text
StaticName = _ModerationComments , Title = Comentários do Aprovador, Type = Note
StaticName = File_x0020_Type , Title = Tipo de Arquivo, Type = Text
StaticName = Name , Title = Conta, Type = Text
StaticName = EMail , Title = Email, Type = Text
StaticName = MobilePhone , Title = Telefone Celular, Type = Text
StaticName = Notes , Title = Sobre Mim, Type = Note
StaticName = SipAddress , Title = Endereço SIP, Type = Text
StaticName = Locale , Title = Localidade, Type = Integer
StaticName = CalendarType , Title = Tipo de Calendário, Type = Integer
StaticName = AdjustHijriDays , Title = AjustarDiasIslâmicos, Type = Integer
StaticName = TimeZone , Title = FusoHorário, Type = Integer
StaticName = Time24 , Title = Formato de Hora, Type = Boolean
StaticName = AltCalendarType , Title = Tipo de Calendário Alternativo, Type = Integer
StaticName = CalendarViewOptions , Title = Opções de Exibição de Calendário, Type = Integer
StaticName = WorkDays , Title = DiasDeTrabalho, Type = Integer
StaticName = WorkDayStartHour , Title = HoraDeInício do DiaDeTrabalho, Type = Integer
StaticName = WorkDayEndHour , Title = HoraDeTérmino do DiaDeTrabalho, Type = Integer
StaticName = IsSiteAdmin , Title = É Administrador do Site, Type = Boolean
StaticName = Deleted , Title = Excluído, Type = Boolean
StaticName = Picture , Title = Imagem, Type = URL
StaticName = Department , Title = Departamento, Type = Text
StaticName = JobTitle , Title = Cargo, Type = Text
StaticName = IsActive , Title = Está Ativo, Type = Boolean
StaticName = WorkPhone , Title = Telefone Comercial, Type = Text
StaticName = ID , Title = ID, Type = Counter
StaticName = ContentType , Title = Tipo de Conteúdo, Type = Computed
StaticName = Modified , Title = Modificado, Type = DateTime
StaticName = Created , Title = Criado, Type = DateTime
StaticName = Author , Title = Criado por, Type = User
StaticName = Editor , Title = Modificado por, Type = User
StaticName = _HasCopyDestinations , Title = Tem Destinos de Cópia, Type = Boolean
StaticName = _CopySource , Title = Origem da Cópia, Type = Text
StaticName = owshiddenversion , Title = owshiddenversion, Type = Integer
StaticName = WorkflowVersion , Title = Versão do Fluxo de Trabalho, Type = Integer
StaticName = _UIVersion , Title = Versão da UI, Type = Integer
StaticName = _UIVersionString , Title = Versão, Type = Text
StaticName = Attachments , Title = Anexos, Type = Attachments
StaticName = _ModerationStatus , Title = Status de Aprovação, Type = ModStat
StaticName = Edit , Title = Editar, Type = Computed
StaticName = LinkTitleNoMenu , Title = Nome, Type = Computed
StaticName = LinkTitle , Title = Título, Type = Computed
StaticName = LinkTitle2 , Title = Título, Type = Computed
StaticName = SelectTitle , Title = Selecionar, Type = Computed
StaticName = InstanceID , Title = ID da Instância, Type = Integer
StaticName = Order , Title = Ordem, Type = Number
StaticName = GUID , Title = GUID, Type = Guid
StaticName = WorkflowInstanceID , Title = ID da Instância do Fluxo de Trabalho, Type = Guid
StaticName = FileRef , Title = Caminho da URL, Type = Lookup
StaticName = FileDirRef , Title = Caminho, Type = Lookup
StaticName = Last_x0020_Modified , Title = Modificado, Type = Lookup
StaticName = Created_x0020_Date , Title = Criado, Type = Lookup
StaticName = FSObjType , Title = Tipo de Item, Type = Lookup
StaticName = SortBehavior , Title = Tipo de Classificação, Type = Lookup
StaticName = PermMask , Title = Máscara de Permissões Efetivas, Type = Computed
StaticName = FileLeafRef , Title = Nome, Type = File
StaticName = UniqueId , Title = Id Exclusiva, Type = Lookup
StaticName = SyncClientId , Title = ID de Cliente, Type = Lookup
StaticName = ProgId , Title = ProgId, Type = Lookup
StaticName = ScopeId , Title = ScopeId, Type = Lookup
StaticName = HTML_x0020_File_x0020_Type , Title = Tipo de Arquivo HTML, Type = Computed
StaticName = _EditMenuTableStart , Title = Início da Tabela do Menu de Edição, Type = Computed
StaticName = _EditMenuTableStart2 , Title = Início da Tabela do Menu de Edição, Type = Computed
StaticName = _EditMenuTableEnd , Title = Fim da Tabela do Menu de Edição, Type = Computed
StaticName = LinkFilenameNoMenu , Title = Nome, Type = Computed
StaticName = LinkFilename , Title = Nome, Type = Computed
StaticName = LinkFilename2 , Title = Nome, Type = Computed
StaticName = DocIcon , Title = Tipo, Type = Computed
StaticName = ServerUrl , Title = URL Relativa do Servidor, Type = Computed
StaticName = EncodedAbsUrl , Title = URL Absoluta Codificada, Type = Computed
StaticName = BaseName , Title = Nome do Arquivo, Type = Computed
StaticName = MetaInfo , Title = Conjunto de Propriedades, Type = Lookup
StaticName = _Level , Title = Nível, Type = Integer
StaticName = _IsCurrentVersion , Title = É a Versão Atual, Type = Boolean
StaticName = ItemChildCount , Title = Contagem de Itens Filhos, Type = Lookup
StaticName = FolderChildCount , Title = Contagem de Elementos Filho da Pasta, Type = Lookup
StaticName = GroupLink , Title = Grupo, Type = Computed
StaticName = GroupEdit , Title = Editar, Type = Computed
StaticName = ImnName , Title = Nome, Type = Computed
StaticName = PictureDisp , Title = Imagem, Type = Computed
StaticName = NameWithPicture , Title = Nome, Type = Computed
StaticName = NameWithPictureAndDetails , Title = Nome, Type = Computed
StaticName = EditUser , Title = Editar, Type = Computed
StaticName = UserSelection , Title = Caixa de Seleção, Type = Computed
StaticName = ContentTypeDisp , Title = Tipo de Conteúdo, Type = Computed