- Created by Eric Schullek , last modified on Mar 16, 2023
You are viewing an old version of this page. View the current version.
Compare with Current View Page History
« Previous Version 40 Current »
To understand the Analytics queries to write, the user will need to understand the tables CAM Analytics supports and the metadata and values that can be selected from. See the below tables for the values, keys, required fields, and how to query.
Audit Table
This table contains all the audit information within CAM.
Query the table by: select * from audit limit 100
^Use the audit Query type
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | The internal CAM id for the audit table. Is a mandatory field. | PK |
docId | It is a mandatory field if a document is set. | FK on document table |
folderId | It is a mandatory field if a folder is set. | FK on folder table |
userId | Displays the user Id associated to the user. Displays the user Id associated to the user. | FK on user table |
extDocId | The external document id (filled if a document was added/changed/updated/deleted) |
|
extFolder | The external folder id (filled if a folder/workspace was added/changed/updated/deleted) |
|
extUserId | The external userid (filled if a user or security was added/changed/updated/deleted) |
|
objectType | Displays the object type. |
|
actionCodeId | The id of the particular action done on the object. Is a mandatory field. |
|
actionCodeName | The name of the particular action done on the object. Is a mandatory field. |
|
logType | The type of the log. |
|
applicationName | Name of the CAM application module making the modification/addition/deletion. |
|
ipAddress | If the ip address is filled from an external system or user. |
|
clientCode | If a client is changed, this is the client code. |
|
MatterCode | If a matter is changed, this is the matter code. |
|
actionUserName | If a action has a specific user name passed, this will be filled. |
|
ticketId |
|
|
comments | Any comments left on the audit. |
|
docNumber | If a document was added/changed/updated/deleted, the docnumber is filled. |
|
hasJournal | Set to 1 if a journal is with the object. |
|
version | Version of the object. |
|
metadata | Any metadata added/changed/updated/deleted |
|
createdDate | Displays the audit creation date. Is mandatory. |
|
createdByUserId | The user id of the person who created the record. |
|
camCreatedDate | The date the record is created in CAM |
|
camCreatedBy | The user who created in CAM |
|
extSystemId | The external system id (iManage, ND etc) | FK on externalsystem |
subscriberId | Displays the tenant subscriber Id. |
|
runId | The ID of the job run |
|
actionDate | Date the action on the record was taken |
|
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. | FK on externalsystem |
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. | FK on externalsystem |
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) |
|---|---|---|
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 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 |
|
etag |
|
|
ctag |
|
|
version | Displays the document version number. It is a mandatory field. |
|
sourceId | Displays the database or cabinet name | FK 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 |
|
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 |
|
|
securityLevelId | Displays the security level ID associated to security. This is auto-generated by CAM |
|
securityLevel | Displays the document security (public/private/view) |
|
emailid | Introduced new column in document table. It contains id/msg_id from imanage and netdocs 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 on externalsystem |
repositoryId | Displays the repository name | FK 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 |
|
External System Table
This table contains all the external system information within CAM.
Query the table by: select * from external_system_nt limit 100
^Use the other Query type
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | The internal CAM id of each external system. | PK |
name | Name of the external system. E.g. iManage, Kira, O365 etc |
|
nickname | A nickname. Typically most people will set this the same as the name. |
|
description | Description of the external system. |
|
type | The system type. Can be 0,1,2 |
|
base_url | The url of the cloud based system. |
|
icon_path | Path of where the icons are stored for the system. Is blank if not set (uses CAM default icons). |
|
libraries | If a particular library within the API is used. Most commonly is blank. |
|
active | Is actively used or not. 0 is active, 1 is not. |
|
is_deleted | Marks if the system is deleted from CAM. 0 is not deleted. 1 is deleted. |
|
created_user_id | The user id who created the external system connection. |
|
last_modified_user_id | The user id who last modified the external system connection. |
|
created_date | The date created |
|
last_modified_date | The date last modified. |
|
External System Authentication Table
This table contains all the external system authentication method information within CAM.
Query the table by: select * from external_system_auth limit 100
^Use the other Query type
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | The internal CAM id of each external system authentication row. | PK |
systemUniqueId | The external system unique id. Each tenant or instance will have a separate id. They look like: 91b692a0-c09d-00e8-9e62-0dfac7e82c00:~imanage:~Worksite2022 | FK on externalsystem |
system | Type of the external system role. E.g. iManage, HighQ, Office365 etc |
|
camrepoName | Name of the CAM instance that connects to this external system. E.g. Worksite2022 |
|
healthy | Marks if the system has errored recently. If it has, it is unhealthy (0). Healthy is =1 |
|
errorMessage | If an error occurred with the system, the last error displays here. For example, Unable to authenticate with HighQ server. Attempts(10) Consumed. Please contact system administrator.(Root Cause : 403 Forbidden) |
|
External System Role Table
This table contains all the external role information within CAM.
Query the table by: select * from external_system_role limit 100
^Use the other Query type
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | The internal CAM id of each external system role. | PK |
ext_id | The external system id from the external system table. | FK on externalsystem |
name | Name of the external system role. E.g. Read, Read/Write, VESA etc |
|
code | Code of the role. E.g. read_write, VESA, member etc |
|
is_deleted | Marks if the role is deleted from CAM. 0 is not deleted. 1 is deleted. |
|
created_user_id | The user id who created the external system role. |
|
last_modified_user_id | The user id who last modified the external system role. |
|
created_date | The date created |
|
last_modified_date | The date last modified. |
|
External System Role Mapping Table
This table contains all the mappings between external roles and CAM roles.
Query the table by: select * from external_system_role_mapping limit 100
^Use the other Query type
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | The internal CAM id of each external system role mapping. | PK |
security_role_id | Id of the security role that is to be mapped. |
|
external_system_instance_id | The external system id of each instance. For example, a firm may have two instances of iManage Work on their servers. | FK on externalsystem |
external_system_role_id | The id of the external system role from the external_system_role table. |
|
is_deleted | Marks if the role mapping is deleted from CAM. 0 is not deleted. 1 is deleted. |
|
created_user_id | The user id who created the external system role mapping. |
|
last_modified_user_id | The user id who last modified the external system role mapping. |
|
created_date | The date created |
|
last_modified_date | The date last modified. |
|
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) |
|---|---|---|
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 chart below: 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. |
|
securityLevel | Displays the folder security level (inherit/view/private etc.) |
|
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) |
|---|---|---|
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) |
|---|---|---|
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 |
|
Groups Table
This table contains all the groups within CAM.
Query the table by: select * from groups limit 100
^Use the groups Query type
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | Displays the id of the entry in the groups table in CAM | PK |
groupId | Displays the group Id |
|
groupName | Displays the group name |
|
The email of the group |
| |
office | The office location of the group |
|
groupType | Displays the group type (Internal/External) Internal=0 External=1 |
|
groupContainerType | The type of container of the group |
|
extcontainerid | The id of the container (typically = the name) |
|
extcontainername | The container name where the group came from |
|
isCAMGroup | Is a group within CAM for CAM. 0= from any other ext system |
|
extid | The external id of the group |
|
Metadata | Displays in JSON format any metadata applied to the group. Comma separated. |
|
CreatedDate | Date the group was created |
|
EditedDate | Date the group was last edited |
|
CreatedbyUserid | User id of the person who created the group |
|
CreatedByUser | The user who created the group |
|
LastChangeUserid | The userid of the person who last edited the group |
|
LastChangeUser | The user who last edited the group |
|
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 |
|
Group Members Table
This table contains all the group memberships within CAM: which users belong to which group.
Query the table by: select * from group_members limit 100
^Use the groups Query type
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | Displays the id of the entry in the group_members table in CAM | PK |
groupid | Displays the id of the group | FK on group |
userid | Displays the id of the user | FK on user |
extgroupid | Contains the id of the external system group |
|
extuserid | Contains the id of the external system user |
|
extcontainerid | Contains the id of the external system container |
|
extcontainername | Contains the name of the external system container. |
|
Metadata | Displays in JSON format any metadata applied to the group membership. Comma separated. |
|
CreatedDate | Date the group membership was created |
|
EditedDate | Date the group membership was last edited |
|
CreatedbyUserid | User id of the person who created the group membership |
|
CreatedByUser | The user who created the group membership |
|
LastChangeUserid | The userid of the person who last edited the group membership |
|
LastChangeUser | The user who last edited the group membership |
|
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) |
|---|---|---|
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 |
|
Project Table
This table contains all the project information within CAM.
Query the table by: select * from project limit 100
^Use the other Query type
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | Displays the id of the entry in the group_members table in CAM | PK |
is_deleted | Is the team deleted? |
|
CreatedDate | Date the team was created |
|
EditedDate | Date the team was last edited |
|
CreatedbyUserid | User id of the person who created the team | FK on user |
CreatedByUser | The user who created the team |
|
LastChangeUserid | The userid of the person who last edited the team | FK on user |
LastChangeUser | The user who last edited the team |
|
Project Team Table
This table contains all the project team information within CAM.
Query the table by: select * from project_team limit 100
^Use the other Query type
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | Displays the id of the entry in the group_members table in CAM | PK |
user_id | The id of the user | FK on user |
group_id | The id of the group | FK on groups |
project_id | The id of the project | FK on project |
allow_project_access | Gives the user project team access |
|
is_implicit_membership | Is this an implicit membership for the user? (Not set on Explicit Security tab). |
|
is_primary | Is this the primary project team? |
|
credit_share | If the billing or originating credit is assigned sets the percentage for the user (0-100%) |
|
import_reference | If imported, the reference from that |
|
project_role_id | Id of the project role | FK on project_role |
approved_date_user_id | Userid of the person who approved | FK on user |
additional_metadata_json | Any additional metadata |
|
is_personal_team | Is this a personal team? |
|
is_deleted | Is the team deleted? |
|
CreatedDate | Date the team was created |
|
EditedDate | Date the team was last edited |
|
CreatedbyUserid | User id of the person who created the team | FK on user |
CreatedByUser | The user who created the team |
|
LastChangeUserid | The userid of the person who last edited the team | FK on user |
LastChangeUser | The user who last edited the team |
|
join_date | The date the user joined the team |
|
leave_date | The date the user left the team |
|
Security Table
This table contains all the security information within CAM.
Query the table by: select * from security limit 100
^Use the security Query type
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | Displays the row number id of the entry in the security table. | PK |
objectid | Contains the id of the object security is being applied to. For example a document, workspace, folder, team. |
|
extuserorgroupId | Displays the user Id or groupId of the external user or group. |
|
extuserorgroupName | Displays the user name or group name of the external user or group |
|
extuserorgrouptype | The type or user or group. 1= group, 2= user |
|
roleId | The id of the role for the user in CAM |
|
isLatest | Flag to indicate if the security has been recently updated and this is the latest record |
|
objectType | Displays the object type. |
|
accesslevelid | The id of the accesslevel. 0= no access, 1= read only, 2= read/write, 3=full access. |
|
accessLevel | Displays the access level assigned to the user (full access/read-write/read only/no-access) |
|
extContainerId | Id of the external system container |
|
extContainerName | Name of the external container |
|
metadata | Any assigned metadata to go with the security application. E.g. clientid=123 |
|
repositoryId | Displays the repository name |
|
createddate | The date the security was created |
|
editedDate | The date edited |
|
createdbyUserid | The user id of the user who created the security |
|
createdbyuser | The user who created the security |
|
lastchangeuserid | The userid of the user that last changed the record |
|
lastchangeuser | The user that last changed the record |
|
camcreateddate | The date cam had created the security |
|
camcreatedby | The person in cam that created |
|
camediteddate | The date last edited in CAM |
|
cameditedby | The person that last edited in CAM |
|
subscriberId | Displays the tenant subscriber Id |
|
extsystemid | Displays the external systemid | FK on externalsystem |
docIdHash | Displays the hash from the table |
|
User Table
This table contains all the users within CAM.
Query the table by: select * from user limit 100
^Use the user Query type
Important: Don’t type users as the table name; this is an old antiquated table and you won’t get proper results.
Column | Description | Keys (Primary/ Foreign) |
|---|---|---|
id | Displays the row number of the entry in the user table | PK |
userId | Displays the user id associated to the user name. |
|
userName | Displays the user name |
|
Displays the user email Id |
| |
office | Displays the office to which the user belongs |
|
title | Displays the title currently associated with the user |
|
phone | Displays phone number currently associated with the user |
|
location | Displays the location currently associated with the user |
|
isExternal | Is this an external user? |
|
userType | Displays the user type (Internal/External) |
|
userContainerType | Displays the container type |
|
extContainerId | Displays the external container id |
|
ExtContainerName | Displays the name of the external container |
|
isCamUser | Displays whether the user is an active CAM user |
|
extId | Displays the external id associated with the user |
|
metadata | Any metadata added/changed/updated/deleted |
|
createdDate | Displays the user creation date |
|
editedDate | Displays the last date the user was edited |
|
createdByUserId | Displays the user id that created the user |
|
createdByUser | Displays the user name that created the user |
|
lastChangeUserId | Displasy the user id that last changed the user |
|
lastChangeUserId | Displasy the user id that last changed the user |
|
camCreateDate | Displays the date in which the user was created in CAM |
|
camCreatedBy | Displays the user that created the user record in CAM. |
|
camEditedDate | Displays the last date the user was edited in cam |
|
camEditedBy | Displays the user that last edited the user record in CAM |
|
extSystemId | Displays the id of the external system. | FK on externalsystem |
subscriberId | The tenant subscription id |
|
- No labels