Object Tables- Docs, Folders, Metadata

Document Table

This table contains all the documents within CAM.

Query the table by: select * from document limit 100

^Use the document Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
Id	The internal CAM id of the document row. This is auto-generated by CAM	PK
extId	Displays the row number of the entry in the document table. It is a mandatory field.
extContainerId	If the configured External System is iManage this column displays the database name, followed by the document number and version (e.g. demo!463.1), in case of NetDocuments, this displays the 12 digit document number (e.g. 4816-5122-8805). It is a mandatory field.	FK with extContainerId on externalsystem
name	Name of the document.
extContainerName	If the configured External System is iManage, this column displays the database name, followed by the document number and version (e.g. demo!463.1), in case of NetDocuments, this displays the document name.
description	Displays the description of the document.
number	Displays the document number for iManage. This does not apply for NetDocuments. It is a mandatory field.
folder	This displays the folder name
folderId	This displays the folder id This is auto-generated by CAM
workspaceId	If the configured External System is iManage this column displays the database name, followed by workspace id. (e.g. demo.463.1), in case of NetDocuments,
tabId	The id of the tab used to display the document on a sharepoint site or teams site.
type	Displays the doc type (e.g. csv, BMP, PDF etc.)
typeId	Displays the type Id. This is auto-generated by CAM (folder type id)
etag
ctag
version	Displays the document version number. It is a mandatory field.
sourceId	Displays the database or cabinet name	FK with sourceid on externalsystem
hasAttachment	Flag to indicate if document has an attachment (in case of emails for example)
isdeclared	Indicates if the document is marked as a record
DeclaredDate	Date the document was declared, if isdeclared=1
indexNumber	Number of the index
originalDocketId	If the document was part of a docket, includes that docket id.
dateSent	Displays the message sent date (in case of email)
dateReceived	Displays the message received date (in case of email)
transcriptDate	Date the document was transcripted.
FileName	Displays the file name (without extension)
FileExtension	Displays the doc type (e.g. csv, BMP, PDF etc.)
FilePath	The path of the file (e.g. if the file was stored in a fileshare)
FileSize	The size of the file uploaded. Includes kb/mb/gb designations.
dateFiled	The date the document was filed, e.g. 20/11/2022
sortorder	Allows to sort the documents in a particular order in the workspace.
SortOrderModifiedDate	Date the sort order was modified, e.g. 20/11/2022
orderedById	Id of the ordering schema
orderingDate	Date the ordering occurred, e.g. 20/11/2022
workProductId	If using iManage Work, this is the product id from there.
isDeleted	Flag to indicate if document is deleted from the system. 1/0
importReference	Displays the document ID as a reference for import.
isDeletedToken	If from a SQL based system, the metadata row could have a token as a GUID if the record was deleted.
extLongId	The external system’s long id
extShortId	The external system’s short id
StatusId	Displays the document status id associated to document. This is auto generated by CAM.
SecurityLevelId	Sets an id based on the security level set on the document. This is auto-generated by CAM. 0 - no access 1 - read 2- read-write 3- full access
checkedOutLocation	Displays the path where the file is checked out
CheckedOutPath	The path of the checked out file
checkedOutBy	Displays the user name who has checked out the file
checkedoutbyid	The id of the person who checked out the document. This is auto-generated by CAM
checkedOutDate	Displays the file checkout date
checkedoutDueDate	Displays the due date for the checked out file.
checkedoutComment	The comment left when a file is checked out.
isOffline	Is the document an offline stored document?
csJobId
securityLevel	Displays the document security (public/private/view) 1- public 2-private 3- view
emailid	Introduced new column in document table. It contains id/msg_id from iManage and NetDocuments respectively.
emailto	Displays the email recipient name
emailfrom	Displays the email sender name
emailcc	Displays the user name added in the cc field in the email
emailBcc	Displays the user name added in the Bcc field in the email
emailSubject	Displays the subject/title added in the subject field in the email.
emailMessageId	This is an existing column. It contains the conversation ID, used to group emails. Note: emailMessageid later will be renamed as emailConversationId
isExternalEmail	Is this an external email outside the firm?
checksum	A checksum of changes in sql.
extDocRefId	Id of the refiled external document
targetContainerId	The id of the target container (netDocuments)
targetContainerName	The name of the target container (netDocuments)
authorId	Displays the author Id associated to the author. This is auto-generated by CAM
author	Displays the document author name
operator	Displays the name of the user who edited the document most recently
operatorId	Displays the operator ID associated to the author. This is auto-generated by CAM
clientCodeId	Displays the client code ID associated to client code. This is auto-generated by CAM
clientCode	This value is updated based on the Auroradb column mapping in the metadata tab.
matterCodeId	Displays the matter code ID associated to matter code. This is auto-generated by CAM
matterCode	This value is updated based on the Auroradb column mapping in the metadata tab.
practiceCodeId	Displays the practice code Id associated to practice code. This is auto-generated by CAM
practiceCode	This value is updated based on the Auroradb column mapping in the metadata tab.
industryCodeId	Displays the industry code Id associated to industry code. This is auto-generated by CAM
industryCode	This value is updated based on the Auroradb column mapping in the metadata tab.
docStatusCode	The document status code from the external system
docStatusCodeid	The document status code id from the external system
jurisdictionCode	Code of the jurisdiction
jurisdictionCodeId	Id for the Code of the jurisdiction
matterType	Displays the matter type
matterTypeId	The id of the matter type
matterSubType	The subtype of the matter
matterSubTypeId	The id of the subtype
class	The class of document (document, email, etc)
classId	Displays the class Id associated to class. This is auto-generated by CAM
classCodeId	Displays the class code Id associated to class code. This is auto-generated by CAM
classCode	Code of the class. E.g. if class is document, typically the code is DOC
subClassCode	This is updated for iManage only
commentsId	Displays the comments Id associated to comments. This is auto-generated by CAM
comments	Displays the comments added to the document
isLatest	Flag to indicate if document updated recently
isCheckedOut	Flag to indicate if document is checked out
isApproved	Is the document approved?
isArchived	Is the document archived?
isAutoversioning	Is the document autoversioned, instead of manually versioned?
GUID	A guid value from sql or source system.
importedById	Id of the system imported by into CAM
importedDate	Displays the date when the file was imported to CAM
lastChangeDate	Displays the last update date
lastChangeUserId	Displays the last change user id associated to the last change user. This is auto-generated by CAM.
lastChangeUser	Displays the user name who last updated the document
c1-c45	Displays the custom metadata fields
n1-n10	Displays the custom metadata fields
d1-d10	Displays the custom metadata fields
runId	The job id of the run.
objectId	Id of the particular object reference type (matter,client etc)
subscriberId	The subscriberid from Office365
system	Displays the external system name	FK with name on externalsystem
repositoryId	Displays the repository name	FK with repositoryid on externalsystem
createdBy	Displays the user name who created the file
createdDate	Displays the file creation date
writeDate	Displays the date the document was updated in CAM
targetDatabase	The target database of a job to move a document/folder/workspace
targetId	The id of the target database
docId	Displays the auto generated document id.
docIdHash	A hash reference of the docid
isApproved	Flag to tell if the document/object is approved
isArchived	Flag to tell if the document/object is archived
isAutoversioning	Flag to show if the document has been autoversioned by the source.
isEcho
isExternal	Is an external stored document?
isExternalUser	Is for an external user?
isFavorite	Is a favorited document?
isLocked	Is the document locked?
isOfficialLocked	Is the document officially locked from the source?
isQueueforIndexing	Is the document queued for indexing?
isSigned	Is the document signed? Applies to NetDocuments only.
repositoryName	Displays the repository name

Folder Table

This table contains all the workspaces and folders within CAM.

Query the table by: select * from folder limit 100

^Use the folder Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The unique internal id of the CAM folders/workspaces.	PK
extid	Displays the row number of the entry in the folder table. It is a mandatory field. For fileshare, this is the path
name	Name of the folder.
description	Description on the folder.
extParentId	The id externally of the parent folder/workspace.
extContainerId	If the configured External System is iManage this column displays the database name, followed by the folder number (e.g. demo!1234), in case of NetDocuments, this displays the 12 digit folder number (e.g. 4816-5122-8805). It is a mandatory field. Other systems, this is the path of the folder/workspace.	FK on externalsystem
extContainerName	If the configured External System is iManage this column displays the database name, followed by the folder name (e.g. demo!folder), in case of NetDocuments, this displays the folder name. It is a mandatory field. Other systems, this is the path of the folder/workspace.	FK on externalsystem
folderType	Displays the folder type (e.g. folder/saved search/workspace etc). See linked reference : FolderTypeids. It is a mandatory field.
parentId	Displays the parent folder Id associated to the parent folder. This is auto-generated by CAM.
alternateparentid	The id of the alternate parent folder.
extworkspaceId	Displays the workspace id associated to the workspace. If the configured External System is iManage this column displays the database name, followed by the workspace number (e.g. demo!1234), in case of NetDocuments, this displays the 12 digit workspace number (e.g. 4816-5122-8805)
ctag
etag
documentNumber	The document number
version	The document version
indexNumber	The number of the index
orderedById	Id the record is ordered by
orderingDate	Date the record was ordered
isDeleted	Is the folder/workspace deleted?
importReference	Displays the folder Id from the Data Import
isDeletedToken	If marked for deletion, this token is filled out
extLongId	The long id version from the external system. For fileshare, this is the path.
extShortId	The shorter id from the external system. For fileshare, this is the path.
folderStatusId	Id of the folder status
folderStatus	Status of the folder
owner	Displays the folder owner name
ownerId	Displays the folder Id associated to the folder. This is auto-generated by CAM.
author	Displays the author of the folder/workspace.
authorId	Displays the authorid of the folder/workspace.
securityLevelId	Displays the security level Id associated to the security level. This is auto-generated by CAM. 0 - no access 1 - read 2- read-write 3- full access
securityLevel	Displays the folder security level (inherit/view/private etc.) 1- public 2-private 3- view
documentClass	This value is updated based on the Auroradb column mapping in the metadata tab.
documentClassId	Displays the document class Id associated to the document class. This is auto-generated by CAM.
clientCodeId	Displays the client code ID associated to client code. This is auto-generated by CAM
clientCode	This value is updated based on the Auroradb column mapping in the metadata tab.
matterCodeId	Displays the matter code ID associated to matter code. This is auto-generated by CAM
matterCode	This value is updated based on the Auroradb column mapping in the metadata tab.
practiceCodeId	Displays the practice code ID associated to practice code. This is auto-generated by CAM
practiceCode	This value is updated based on the Auroradb column mapping in the metadata tab.
industryCodeId	Displays the industry code ID associated to industry code. This is auto-generated by CAM
industryCode	This value is updated based on the Auroradb column mapping in the metadata tab.
matterType	Displays the matter type.
matterTypeId	Displays the matter type id.
matterSubType	Displays the matter subtype.
mattersubtypeid	Displays the matter subtype id.
areaOfLaw	The area of law categorization. For example, Intellectual Property
areaOfLawId	The area of law id
classCode	The fee earner class code
classcodeid	The class code id
deptCode	The department code
deptCodeid	The department code id
subClassCode	If the class has a subclass, this is the code for it.
SubclassCodeid	The subclasscodeid
comments	Comments on the folder/workspace
matterStatusId	Id of the matter status
MatterStatus	The status of the matter. For e.g. Open, Closed, Inactive, Opportunity
sourceId	Id of the source record
nblsNumber
matteropenDate	Date the matter started
mattercloseDate	Date the matter ended
filedDate	Date the folder was filed
hasUnorderedDocuments	If the folder/workspace has unordered documents (blank documentorder), this is 1=Yes.
templateId	The id of the template used.
matterlocation	Location of the matter
matterlocationid	Id of the matter location
responsiblelawyer	The responsible lawyer of the matter.
responsibleLawyerId	Id of the responsible lawyer
originatinglawyer	The lawyer that originated the matter.
originatingLawyerId	The id of the originating lawyer
officeCode	The code of the office
officecodeid	The id of the office
countryCode	The code of the country
countrycodeid	The id of the country
jurisdictionCodeId	Id of the jurisdiction
jurisdictionCode	The Jurisdiction of the matter
courtCode	Code of the court
courtcodeid	Id of the court
judgename	Name of the judge
onLegalHold	If the workspace or document is under a legal hold (in CAM or other system)
legalHoldStartDate	Start of the legal hold
legalHoldEndDate	End of the legal hold
guid	A guid identifier for the record (unique)
isLatest	Flag to indicate if the folder was recently updated
isHipaa	Is the document or workspace dealing with a Hipaa procedure in medicine
isCheckedOut	Is the folder/workspacechecked out?
isDeclared	Is the folder/workspace declared?
isApproved	Is the folder/workspace approved?
isArchived	Is the folder/workspace archived?
isAutoversioning	Is the folder/workspace auto versioned?
isEcho
isExternal	Is the folder/workspace external to the firm?
isExternalUser	Is there an external user to the folder/workspace?
isFavorite	Is the folder/workspace a favorite?
isLocked	Is the folder/workspace locked?
isOfficalLocked	Is the folder/workspace officially locked?
isQueueforIndexing	Is the folder/workspace queued for indexing?
isSigned	Is the folder/workspace signed?
isOfficialVersion	Is the folder/workspace officially versioned (default)
isShortcut	Is the folder/workspace a shortcut?
importedById	Id of the person or system who imported the folder/workspace
importedDate	Displays the date the folder was imported in CAM
lastChangeDate	Displays the last update date
lastChangeUserId	Displays the last change user id associated to the last change user. This is auto-generated by CAM.
lastChangeUser	Displays the user name who last updated the document
metadata	A JSON formatted row of all the metadata passed on the folder/workspace. Comma separated.
c1-c45	Displays the custom metadata fields
n1-n10	Displays the custom metadata fields
d1-d10	Displays the custom metadata fields
createdDate	Displays the folder creation date
createdBy	Displays the user name who created the folder
EditedDate	The date last edited
subscriberId	Displays the tenant subscriber Id
extsystemid	Displays the external system id	FK on externalsystem
projectId	Displays the id of the project. From CAM’s Project import function in Directory tab	FK on project
ProfileEditDate	Date the profile was edited
ProfileEditedUserId	The userid of the person who edited the profile
profileEditedUser	The user that edited the profile
camCreatedDate	If created by CAM in CAM, the date created.
camCreatedBy	If created by CAM in CAM, the person that created (if applicable)
camEditedDate	If using CAM, the date last edited
camEditedBy	If using CAM, the person who last edited

Folder Content Table

This table contains all the content within the folders (excluding the physical documents stored in the document table).

Query the table by: select * from folder_content limit 100

^Use the folder Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id of the folder content table records.	PK
docid	The id of the document	FK on document
folderid	The id of the folder	FK on folder
extDocid	The external system document id	FK on document
extFolderid	The external system folder id	FK on folder
extContainerid	The external system container id	FK on externalsystem
extContainerName	The external system container name	FK on externalsystem
camCreatedDate	If created in CAM, the created date
camCreatedBy	If created in CAM, the user who created
camEditedDate	If edited in CAM, the date edited
camEditedBy	If edited in CAM, the user who edited
extSystemId	The external system id	FK on externalsystem
subscriberid	The tenant subscriber id

Folder Version Table

This table contains all the versions of folder, as well as any changes to the folder, or content within the folders (excludes storing the physical documents stored in the document table).

Query the table by: select * from folder_version limit 100

^Use the folder Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id of the folder content table records.	PK
folderid	The folder id	FK on folder
extid	Displays the row number of the entry in the folder table. It is a mandatory field. For fileshare, this is the path	FK on folder
name	Name of the folder.	FK on folder
description	Description on the folder	FK on folder
extparentid	The id externally of the parent folder/workspace.	FK on folder
parentid	The parent id of the folder/workspace in CAM	FK on folder
extworkspaceid	The external system workspace id
extcontainerid	If the configured External System is iManage this column displays the database name, followed by the folder number (e.g. demo!1234), in case of NetDocuments, this displays the 12 digit folder number (e.g. 4816-5122-8805). It is a mandatory field. Other systems, this is the path of the folder/workspace.
extcontainername	If the configured External System is iManage this column displays the database name, followed by the folder name (e.g. demo!folder), in case of NetDocuments, this displays the folder name. It is a mandatory field. Other systems, this is the path of the folder/workspace.
foldertype	Displays the folder type (e.g. folder/saved search/workspace etc). See chart below: FolderTypeids. It is a mandatory field.
documentnumber	The document number	FK on folder
version	The document version
sortorder	The order the document is sorted in the folder. Also the folder order.
sortordermodifieddate	The date the sort order was changed.
isdeleted	Is the folder/workspace deleted?
deletedtoken	If marked for deletion, this token is filled out
extLongid	The long id version from the external system. For fileshare, this is the path.
extShortid	The shorter id from the external system. For fileshare, this is the path.
statusid	Id of the folder status
extfolderrefid	Id of the external system refiling
targetcontainerid	The id of the target container
targetcontainername	The name of the target container
securitylevelid	Displays the security level Id associated to the security level. This is auto-generated by CAM.
owner	Displays the folder owner name
ownerid	Displays the folder Id associated to the folder. This is auto-generated by CAM.
author	Displays the author of the folder/workspace.
authorid	Displays the authorid of the folder/workspace.
clientcode	Displays the client code ID associated to client code. This is auto-generated by CAM
clientcodeid	This value is updated based on the client code column mapping in the metadata tab.
mattercode	Displays the matter code ID associated to matter code. This is auto-generated by CAM
mattercodeid	This value is updated based on the Auroradb column mapping in the metadata tab.
practicecode	Displays the practice code ID associated to practice code. This is auto-generated by CAM
practicecodeid	This value is updated based on the practice code column mapping in the metadata tab.
industrycode	Displays the industry code ID associated to industry code. This is auto-generated by CAM
industrycodeid	This value is updated based on the industry code column mapping in the metadata tab.
mattertype	Displays the matter type.
mattertypeid	Displays the matter type id.
mattersubtype	Displays the matter subtype.
mattersubtypeid	Displays the matter subtype id.
areaOfLaw	The area of law categorization. For example, Intellectual Property
areaOfLawId	The area of law id
classCode	The fee earner class code
classcodeid	The class code id
deptCode	The department code
deptCodeid	The department code id
subClassCode	If the class has a subclass, this is the code for it.
SubclassCodeid	The subclasscodeid
comments	Comments on the folder/workspace
matterStatusId	Id of the matter status
MatterStatus	The status of the matter. For e.g. Open, Closed, Inactive, Opportunity
nblsNumber
matteropenDate	Date the matter started
mattercloseDate	Date the matter ended
filedDate	NDate the folder was filed
hasUnorderedDocuments	If the folder/workspace has unordered documents (blank documentorder), this is 1=Yes.
templateId	The id of the template used.
matterlocation	Location of the matter
matterlocationid	Id of the matter location
responsiblelawyer	The responsible lawyer of the matter.
responsibleLawyerId	Id of the responsible lawyer
originatinglawyer	The lawyer that originated the matter.
originatingLawyerId	The id of the originating lawyer
officeCode	The code of the office
officecodeid	The id of the office
countryCode	The code of the country
countrycodeid	The id of the country
jurisdictionCodeId	Id of the jurisdiction
jurisdictionCode	The Jurisdiction of the matter
courtCode	Code of the court
courtcodeid	Id of the court
judgename	Name of the judge
onLegalHold	If the workspace or document is under a legal hold (in CAM or other system)
legalHoldStartDate	Start of the legal hold
legalHoldEndDate	End of the legal hold
isLatest	Flag to indicate if the folder was recently updated
isHipaa	Is the document or workspace dealing with a Hipaa procedure in medicine
isCheckedOut	Is the folder/workspacechecked out?
isDeclared	Is the folder/workspace declared?
isApproved	Is the folder/workspace approved?
isArchived	Is the folder/workspace archived?
isAutoversioning	Is the folder/workspace auto versioned?
isEcho
isExternal	Is the folder/workspace external to the firm?
isExternalUser	Is there an external user to the folder/workspace?
isFavorite	Is the folder/workspace a favorite?
isLocked	Is the folder/workspace locked?
isOfficalLocked	Is the folder/workspace officially locked?
camCreatedDate	If created in CAM, the created date
camCreatedBy	If created in CAM, the user who created
camEditedDate	If edited in CAM, the date edited
camEditedBy	If edited in CAM, the user who edited
extSystemId	The external system id	FK on externalsystem
subscriberid	The tenant subscriber id

Metadata Table

This table contains all the metadata within CAM.

Query the table by: select * from metadata limit 100

^Use the metadata Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	Displays the row number of the entry in the metadata table.	PK
columnName	Displays the column name mapped to the metadata in CAM
name	Displays the metadata ID
value	Displays the metadata desc
parent	Displays the parent Id for the metadata
attributes	The attributes of the metadata values
docSystemKey	Displays the metadata name
objectId	The id of the object of the metadata (matter, client are examples)
subscriberId	Displays the tenant subscriber Id
system	Displays the external system name	FK on externalsystem
repositoryId	Displays the repository name in the external system	FK on externalsystem
sourceId	Displays the cabinet/database name
lastEdited	Displays the last edited date and time
createdDate	Displays the metadata created date
createdBy	Displays the user name who created the metadata
modifiedBy	Displays the user name who last modified the metadata
writeDate	Displays the date the metadata was written to the system
uidCustomMetadata	Shows any custom metadata inputted within CAM to the field.
repositoryName	Displays the repository name