Querying Main Database

You can query Main database to obtain meta IDs that you need when working with iService methods.

The following table lists the queries you need to run to obtain meta IDs:

To Get

Run This Query

Object Details

Select * from tblObject where tName ='<Object Name>'

This query displays the following columns:

  • aObjectType - Displays the Object type.

  • tBaseTable - Displays the base table of the Object.

  • nObjectClass - Displays the category of the Object. 1 indicates Global Object, 2 indicates Team Object, and 3 indicates Shared Object.

  • nPKColumnID - Displays the Object instance Property ID.

  • nNamePropID - Displays the Name Property ID.

  • nSisObjectType - Indicates the type of Object. 1 indicates Reference Object and any other value indicates Operational Object.

  • nConstraintID – Displays the Reference Object Constraint ID which is used for reference Object restricted text properties.

Tab Details

Select * from tblTab where tTabName = '<TabName>'

This query displays the following columns:

  • aTabID - Displays the ID of the Tab.

  • nObjectType - Displays the Object Type to which the Tab belongs.

  • nOrder - Displays the order of the Tab.

  • bExternalTab - Indicates if the tab is a Linked tab. If the value is 1 it indicates it is a Linked Tab.

  • nTabType - Indicates the type of Tab. 0 indicates Properties or RecordList tab, 16 indicates Attachment type of tab and 256 indicates Relationship tab. Additionally, if the value of the bList column is 1 it indicates a RecordList tab.

Group Details

Select * from tblFieldGroup where tGroupName ='<GroupName>' and nTabID = <TabID>

This query displays the following columns:

  • aGroupID- Displays the Group ID.

  • nTabID -Displays the Tab ID.

  • nOrder- Displays the order of the Group in the Tab

Note: For default groups, leave the Group Name blank.

Property Details

Select * from view_ColumnMain where tDisplayName = <Property Name>

This query displays the following columns:

  • aColumnID - Displays the Property ID.

  • tDisplayName - Displays the display name of the Property.

  • bExternal - Indicates if the Property is of an external type. 1 indicates External Property.

  • nJoinColumnId - Displays the Join Column ID.

  • nTalismaLinkedPropID - A value other than 0 indicates that the Property is a Linked to CampusNexus CRM Property that is mapped to the Property in the column.

  • nGroupID - Displays the Group ID of the Property.

  • bProtected - 1 Indicates that the Property is Protected.

  • nConstraintID - Displays the ID of constraint linked to Property.

  • nOrder - Displays the order of the Property in the group.

  • tDefault - Default value for the Property ( if applicable).

  • bMandatory – 1 Indicates that the Property is mandatory.

  • nObjectType - Indicates the Object Type of the Property.

  • nRelDisplayPropertyID – Displays the display Property ID in case of Relationship Property.

  • nOtherSidePropertyID – Displays the Relationship Property for the other side object involved in Relationship.

  • nRelObjectType - Related Object Type.

  • nRelationType – Indicates the type of Relationship. 1 indicates One to Many, 2 indicates Many to One, 3 indicates Many to Many, and 4 indicates One to One.

  • bAllowUpdation - 1 Indicates editable Property and 0 indicates read-only Property

  • nRelationPropertyId – Displays the ID of the Relationship Property in case of a Property on Relationship (POR) Property.

User Details

Select * from tblUser where tLoginName = '<Login Name>'

This query displays the following columns:

  • aUserID- ID Of User.

  • tName - Displays the name of CampusNexus user.

  • tLoginName - Displays the login name of the User.

  • bDeleted - Indicates if the user is deleted from CampusNexus. 1 indicates the User is deleted.

  • nPrimaryTeam - Displays the Primary Team of the User.

Team Details

Select * from tblTeam where tName = '<Team Name>'

This query displays the following columns:

  • aTeamID - Displays the ID of the Team.

  • tName - Displays the Name of the Team.

  • nParentTeamID - Displays the ID of the Parent Team.

  • nDefaultCRMUser – Displays the default CRM User ID.

  • nDefaultSISUser - Displays the default SIS User ID.

SIS Team

select * from tblSISTeam

  • nSISTeamID - SIS Team ID.

  • tSISTeamName - Name of the SIS Team.

  • nTalismaTeamID - ID of the CampusNexus CRM Team.

Enum Details

Select * from tblEnum where nConstraintID in (Select nConstraintID from tblColumnConstraintMap inner join tblConstraint on aConstraintiD = nConstraintID where nColumnID = <PropertyID>)

This query displays the following columns:

  • tDisplayName - Displays the display name of an enumerated Property.

  • nIndex - Displays the index of the enumerated value.

  • nConstraintID - Displays the constraint ID associated with enumerated value.

  • nOrder - Displays the order of the enumerated value. -1 indicates that the enumerated value is deactive.

  • bDeleted - Indicates whether the enumerated value is deleted. 1 indicates the value is deleted.

Media Details

Select * from tblMedia.

This query displays the following columns:

  • aMediaID- Displays the ID for each Medium.

  • tName- Displays the name of Media.

  • bDeleted- Indicates the state of the Media.1 indicates deleted and 0 indicates active.

Alias Details

Select * from tblGroupAlias

This query displays the following columns:

  • aGpAliasID - Displays the ID of each Alias.

  • tDisplayName - Displays the name of the Alias.

row ID (Record List)

To obtain the ID of a row in the RecordList Tab of an Object item, you must first query the database to find the name of the table which contains the details of RecordList Tabs. To do so, execute the following query:

Select tBaseTable from tblColumnMain where tDisplayName = ‘<RecordList Tab Property name>’

where, <RecordList Tab Property name> is the name of the Property in the RecordList whose rowID you want to find. Note the name of the table that contains the details of the RecordList Tabs. To obtain the rowIDs of the RecordList Tab, execute the following query: Select * from <table name> where, <table name> is the name of the table obtained by executing the previous query. The nKeyID column displays the ID of each row in the RecordList Tab.

Workspace Details

select * from view_tblDesksForLang where tName LIKE '<Workspace Name>' AND nUserID= <Talisma User ID>

From this query, retrieve the ID of the custom Workspace from the aDeskID column.

Custom Workspace Pane Details

Select tCompDataXML,* from tblDeskComponentData where nDeskID=<Workspace ID>

From this query, retrieve the custom Workspace Pane ID from the tCompDataXML column.

To obtain the name of the Workspace for a Filter, use the Pane ID obtained from the above query in the following query:

Select * from tblWorkspace where aWorkspaceID=<Pane ID>

In this query, tWorkspacename displays the name of the Workspace where the Filter is configured.

Filter ID in a Custom Workspace Pane

Select * from tblFilter where nCreateInRoom=<Pane ID> AND tName='<Filter name>

From this query, retrieve the Filter ID from the aFilterID column for a given pane of a custom Workspace.

To query Main Database Using Query Analyzer

  1. From the Start menu, point to Programs, Microsoft SQL Server, SQL Server Management Studio.

    The Connect to Server dialog box is displayed.

  2. From the Server name list, select the computer on which Main database is installed.

  3. In the Authentication field, select Windows Authentication or SQL Server Authentication to connect to Main database using your Windows login credentials.

    The Microsoft SQL Server Management Studio window is displayed.

  4. Click the New Query option, select tlMain from the Databases list and execute the required query.