aListColumns statement

The aListColumns statement returns a schema for one or multiple lists.

   <aListColumns tableName="">
      <from type=""></from>
      <fields></fields>
      <where></where>
   </aListColumns>

aListColumns elements

from

Specify one or more lists from which to retrieve the columns. The type attribute will indicate how lists are specified.

Type	Description
Url	Specify one or more list urls separated by a comma. A list url is composed of a relative site url and a list name separated by a semi-colon. A report’s parameter may be specified instead of a literal value.
ResultSet	Specify the tableName value of another statement. The resulting data from the statement must contain a column named “relativeSiteUrl” and a column named “Title”.

The following statement will return all the column properties for the list “Products” located in the web site “/sites/demo/” and for the list “Tasks” located in the web site “/sites/ersdetest”.

Sample

    <aListColumns tableName="ListColumns">
       <from type="Url">/sites/demo/;Products,/sites/ersdetest/;Tasks</from>
       <fields>*</fields>
       <where></where>
    </aListColumns>

In the following example, the query will return all the column properties for the lists specified in another result set called “aListCollection1”. The “aListCollection1” result set contains data returned by an aListCollection statement that queries the “/sites/demo/” site and gets properties for the “Products” SharePoint list.

The return of the aListCollection statement contains a column called “RelativeSiteUrl” that contains relative urls that point to SharePoint sites and a column called “Title” that contains the name of a list.

The aListColumns statement will retrieve column properties for each item in the “aListCollection1” result set based on the “RelativeSiteUrl” and “Title” column’s values.

We then filter the results to only get the columns that were manually created on these lists.

Sample

    <root xmlns="http://enesyssoftware.com/schemas">
     <aListCollection tableName="aListCollection1">
       <from type="Url">/sites/demo/</from>
       <fields>Title,ID</fields>
       <where>Title = "Products"</where>
     </aListCollection>
     <aListColumns tableName="aListColumns1">
       <from type="ResultSet">aListCollection1</from>
       <fields></fields>
       <where></where>
     </aListColumns>
     <sqlQuery tableName="sqlQuery1">
       <![CDATA[SELECT *
       FROM aListCollection1, aListColumns1
       WHERE UPPER(aListCollection1.ID) = UPPER(aListColumns1.SourceID)]]>
     </sqlQuery>
     <resultSet>sqlQuery1</resultSet>
    </root>

fields

Specify the fields to be returned by the statement.

Field	Description
ID	The GUID of the field.
RowOrdinal	The database location for the field.
Type	The type of the field.
Sealed	Boolean value that indicates whether other fields can be derived from the field.
ReadOnly	Boolean value that specifies whether values in the field can be modified.
Hidden	Boolean value that specifies whether the field is displayed in the list.
DisplayName	The displayed name for a field.
Name	The name of a field.
DisplaceOnUpgrade	Boolean value that indicates whether to force updates to field properties with the values that are specified in this field definition.
SourceID	The namespace that defines the field or the GUID of the list in which the custom field has been created through the user interface.
StaticName	The internal name of the field.
ColName	An internal attribute that defines the mapping of the field to the physical storage name for this field.
FromBaseType	Boolean value that indicates whether the field derives from a base field type.
Required	Boolean value that determines whether the field requires values.
MaxLength	The maximum number of characters that can be typed in the field.
Version	The version of the field.
CanToggleHidden	Boolean value that indicates whether the column can be hidden through the user interface.
Filterable	Boolean value that indicates whether the field can be filtered.
Sortable	Boolean value that determines whether the field can be sorted.
Dir	The direction of the reading order for the field.
DisplayNameSrcField	Refers to the name of the other field.
AuthoringInfo	The descriptive string that is used in pages for editing fields to identify the field and its purpose.
EnableLookup	Boolean value that specifies whether to allow lookup fields to display values from this computed field.
ClassInfo	The cascading style sheet (CSS) class to use for the field in the standard table view.
NumLines	The number of lines to display in the field.
RichText	Boolean value that specifies whether rich text formatting can be used in the field.
RichTextMode	The rich text mode for the field.
AllowHyperlink	Boolean value that specifies whether hyperlinks can be used in the field.
IsolateStyles	For a Text field whose RichText attribute is TRUE and whose RichTextMode attribute is FullHtml, this attribute specifies that a server will rewrite the HTML of the field to ensure that it will not interfere with the rendering of the surrounding page.
AppendOnly	Boolean value that specifies whether to append changes to the existing text.
Percentage	Boolean value that determines whether values in the field are displayed as percentages.
Format	The type of date and time format that is used in the field.
FillInChoice	Boolean value that determines whether a text box for typing an alternative value is provided for the multichoice field.
LCID	The country/region whose currency format is being used.
CalType	The id for the type of calendar that is used to display the field.
List	The parent list that contains the field.
ShowField	The ShowField attribute can be set to the field name to be displayed.
UserSelectionMode	A value that specifies whether only individuals (PeopleOnly) or both individuals and groups (PeopleAndGroups) can be selected as field values in forms for creating or editing list items.
UserSelectionScope	A scope for selecting user names in a user field on an item form. If the value is 0, there is no restriction to a SharePoint group. If the value is greater than 0, user selection is restricted to members of the SharePoint group whose ID equals the value that is specified.
Group	The column group to which the field belongs.
Mult	Boolean value that specifies whether to use a scale factor in multiplication before displaying a number.
ColName2	An internal attribute that defines the mapping of the field to the second physical storage name for this field.
RowOrdinal2	The second database location for the field.
ResultType	The data type of values that are returned from the field.
UnlimitedLengthInDocumentLibrary	Boolean value that specifies whether to allow values with unlimited text in document libraries.
PrimaryKey	Boolean value that specifies whether the field is the primary key in the relationship between the list and another list.
PITarget	The document processing instruction in which the column value is stored in documents of the specified content type, which is used for property promotion and demotion in XML documents.
PIAttribute	The document processing instruction in which the column value is stored in documents of the specified content type, which is used for property promotion and demotion in XML documents.
StorageTZ	Specifies how date/time values are stored.
SetAs
HeaderImage	The image file name to use as a header in the field.
Min	A minimum value for the field.
Max	A maximum value for the field.
FieldRef	The name of another field to which the field refers, such as a Lookup field.
JoinColName	This attribute is defined only for Lookup fields. It is very similar to ColName, except it denotes the name of the column that is used in the SQL JOIN between the local and external lists.
JoinRowOrdinal	The database location for the referenced field.
JoinType	The type of JOIN used on the field. Possible values include INNER, LEFT OUTER, and RIGHT OUTER.
ShowInFileDlg	Boolean value that specifies whether the field shows up in the property dialog box for saving forms that appears when saving from client applications.
RenderXmlUsingPattern	Boolean value that specifies whether to render value on the display pattern of the computed field
ShowInVersionHistory	Boolean value that specifies whether the field is displayed in the page for viewing list item versions.
TextOnly	Boolean value that specifies whether the field can contain only Text values.
Choices	The comma separated choices that are used in the multichoice field.
DefaultValue	The default value for a field.
Formula	The default formula for a calculated field.

where

SQL Where clause used to filter the resulting data.

The following query returns all the “Lookup” columns that are not hidden in the “Products” list:

Sample

    <root xmlns="http://enesyssoftware.com/schemas">
     <aListColumns tableName="aListColumns1">
       <from type="Url">/sites/demo/;Products</from>
       <fields></fields>
       <where>Type = "Lookup" And (Hidden IS NULL OR NOT Hidden)</where>
     </aListColumns>
     <resultSet>aListColumns1</resultSet>
    </root>