The driver exposes SharePoint lists as relational tables. SharePoint lists are created by users, and therefore the data model of a list depends on the columns added to the list by the user. The following topics include sample tables which show the columns that may appear in SharePoint lists and the associated data type for each.

  • DEFAULTCOLUMNSLIST is a table that includes all the columns a user can specify in a list. The driver exposes all user-specified columns by default. The column names indicate the type of column created.
  • ALLCOLUMNSLIST is a table that includes internal SharePoint columns, as well as user-specified columns. The internal columns are exposed when the AuditColumns connection property is set to All.

The following sections provide information on supported column types and how the driver exposes columns.

Modern column types

The driver supports SharePoint Modern column types as described in the following table.

Column type Data type Behavior
Single line of text STRING(255) Read-write
Multiple lines of text STRING(63999) Read-write
Location COMPLEX Read-write
Number DOUBLE Read-write
Yes/No BOOLEAN Read-write
Person or Group STRING(255) Read-only
Date and Time DATETIMEOFFSET(26) Read-write
Choice STRING(255) Read-write
Hyperlink or Picture COMPLEX Read-only
Currency DECIMAL(19) Read-write

Location complex types

Location column types are complex columns that contain multiple fields. As shown here, the fields in Location columns are exposed by the driver as separate columns where colname is the name assigned to the column.

Column name Data type Behavior
colname_ADDRESS_COUNTRYORREGION STRING(255) Read-write
colname_ADDRESS_STATE STRING(255) Read-write
colname_ADDRESS_CITY STRING(255) Read-write
colname_ADDRESS_POSTALCODE STRING(255) Read-write
colname_ADDRESS_STREET STRING(255) Read-write
colname_COORDINATES_LATITUDE DOUBLE Read-write
colname_COORDINATES_LONGITUDE DOUBLE Read-write
colname_DISPLAYNAME STRING(255) Read-write

Hyperlink or Picture complex types

Hyperlink or Picture column types are complex columns that contain multiple fields. As shown in the following table, the two fields in Hyperlink or Picture columns are exposed by the driver as separate columns where colname is the name assigned to the column.

Column name Data type Behavior
colname_DESCRIPTION STRING(255) Read-write
colname_URL STRING(255) Read-write

Unfamiliar column names

When a naming conflict occurs, SharePoint generates a unique column name to replace the name specified when the column was created. In these cases, you may see an unfamiliar column name returned when performing a select operation from a table. For example, if the column name PERCOL conflicts with another column, SharePoint might create the unique column name _X0076_H65.

You can determine the original column name by searching for the unfamiliar column name in the native file. The native file is a metadata file that will include the original name and unfamiliar name. The location of the native file (as well as other internal files) is determined by the path specified for the SchemaMap connection property. The default location of the file depends on the platform you are using.

  • For Windows platforms
    • User data source: user_profile\AppData\Local\Progress\DataDirect\SharePoint_Schema\user_name.native
    • System data source: C:\Users\Default\AppData\Local\Progress\DataDirect\SharePoint_Schema\user_name.native
  • For UNIX/Linux
    • ˜/progress/datadirect/sharepoint_schema/user_name.native

The label field in the column definition will contain the original column name. For example:

<Field name="_X0076_H65" nativeType="STRING" filterable="0" label="PERCOL" po4:navigation="fields"/>