Files
Update, delete, and query the available Files in Box.
Table Specific Information
Select
If you search for files without specifying any condition in the WHERE Clause, by default only the files that are at most 5 levels deep from the root folder will be returned. You can change the default depth value in the connection string; e.g. DirectoryRetrievalDepth=10.
SELECT * FROM Files
To search all the Files in your enterprise, query the Files table with the column SearchTerms.
SELECT * FROM Files WHERE SearchTerms LIKE '%untitled%'
To search all the Files within a specific folder, query the Files table with a filter on the relevant folder's Id in the ParentId column.
SELECT * FROM Files WHERE ParentId = '12'
Insert
You must at least specify a value for Path, which represents the local file path.
INSERT INTO Files (Path) VALUES ("c:/file.txt")
Update
Any column where ReadOnly=False can be updated.
UPDATE Files SET Description = 'example description', Sharedlink='http://sharedlink.com', OwnedbyId='321', ParentId='12', Name = 'updated file name' WHERE Id = '123'
Delete
Files can be deleted by providing an Id and issuing a DELETE statement. This file is then moved to TrashedItems
DELETE FROM Files WHERE Id = '100'
Columns
| Name | Type | ReadOnly | Description |
| SearchTerms | String | True |
Query to search the files database. |
| Id [KEY] | String | True |
The Id of the file. |
| Name | String | False |
The name of the file. |
| Sha1 | String | False |
The SHA-1 encryption of the file. |
| Etag | String | False |
The Etag of the file |
| SequenceId | String | False |
The sequence Id of the file. |
| Description | String | False |
The description of the file. |
| Size | Integer | True |
The size of the file. |
| CreatedAt | Datetime | True |
The date the file was created at. |
| ModifiedAt | Datetime | True |
The date the file was modified at. |
| TrashedAt | Datetime | True |
The date the file was trashed at. |
| PurgedAt | Datetime | True |
The date the file was purged at. |
| ContentCreatedAt | Datetime | True |
The date the content of the file was created at. |
| ContentModifiedAt | Datetime | True |
The date the content of the file was modified at. |
| CreatedById | String | True |
The Id of the user that created the file. |
| CreatedByName | String | True |
The Name of the user that created the file. |
| CreatedByLogin | String | True |
The Login of the user that created the file. |
| SharedLink | String | False |
The shared url of the file. |
| ModifiedById | String | True |
The Id of the user that last modified the file. |
| ModifiedByName | String | True |
The Name of the user that last modified the file. |
| ModifiedByLogin | String | True |
The Login of the user that last modified the file. |
| OwnedById | String | False |
The Id of the user that owns the file. |
| OwnedByName | String | False |
The Name of the user that owns the file. |
| OwnedByLogin | String | False |
The Login of the user that owns the file. |
| ParentId | String | False |
The Id of the folder that contains the file. |
| ItemStatus | String | False |
The status of the file. |
| Path | String | True |
The full path of the file. |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| Scope | String |
The scope of the search. |
| FileExtension | String |
The extension of the file. |
| ContentTypes | String |
The content to search the query at, separated by commas. This can contain a mixture of the following: name, file_content, description, comments, tags. |
| OwnerUserIDs | String |
The Ids of the owners to limit the search to, separated by a comma. |
| AncestorfolderIDs | String |
The Ids of the folders to limit the search to, separated by a comma. |
| AsUserId | String |
The Id of the user you want to impersonate. Only works with Admin, Co-Admin and Service Accounts. |