How do I identify database tables for applications and items?

In certain circumstances you may wish, or be asked by support, to identify where a certain application stores its data. To do so, you will need to identify the original name of the application as well as the appropriate IDs.

Browser URL: (Versions 9.5 and later)

Your original application name can be identified in the browser URL of the site.  Open the application in question (within the site) and check the following area of your browser:

appnames1.png

Email Page: (Versions 9.0 and Earlier)

The first step is to generate the link which will contain the appropriate information. Navigate to the application, category or item you are interested in. Press the ‘email page’ link in the top right hand corner of your screen.

appnames2.png

This will generate an email with a direct link to the page you are viewing. The example below is generated when viewing a item level entry in the documents application.

http://intranet/documents/detail_view.cfm?MenuID=6andID=51andCategoryID=7

Several useful parameters are present in the link:

Original Application Name: In this case it is documents.

Content IDs:   Each application, category and item on the site has its own unique IDs. In this link the relevant IDs are MenuID=6, CategoryID=7, ID=51

Other URL Parameters are also passed in this fashion but are not discussed in this document.
 

Identifying your Application (original vs clone):

To identify the location of your content within the database, you will need to know both the Original Application Name and the MenuID.

MenuID’s for applications fall into two groups.

MenuID = 1- 99:      These are the original applications. For example, the MenuID of the original documents application is 6.

MenuID > 5000:       Cloned Applications. All copies of the original applications have MenuID’s greater than 5000. When looking for the application tables of a cloned application you will note that they have the MenuID appended to the table name.

Locate Your Application’s Tables:

Open your Intranet Connections database. Applications tables are labeled according to the original application name. In this example, we will look at the associated tables for two links:

http://intranet/documents/detail_view.cfm?MenuID=6&ID=51&CategoryID=7

Tables:                  

Documents
DocumentsApproval
DocumentCategories
DocumentSubCategories
DocumentVersions
DocumentFields
DocumentOrder

http://intranet/documents/detail_view.cfm?MenuID=5123&ID=254&CategoryID=2

Tables:                  

Documents5123
DocumentsApproval5123
DocumentCategories5123
DocumentSubCategories5123
DocumentVersions5123
DocumentFields5123
DocumentOrder5123

Note that when dealing with a cloned application, each table has the cloned MenuID appended to its name.

 

Locate application tables via the MenuCategories table:

The examples above are listed only to provide an example of schema. When working with your application, it is always more reliable to query the database directly. This can be done by looking at the MenuCategories table. Within you will find a row corresponding to each application in the product. Simply cross reference the MenuID of your application (as identified by your URL variables) with the values of the following columns for the row sharing this MenuID. The columns of interest are Menucategories.Tablename1 through MenuCategories.Tablename1 and MenuCategories.FileFolderName.

The following query below can return you this information directly from your site admin. Simply replace the highlighted MenuID value in the code example below with your menuID and then go to your intranet site. Navigate to Admin --> Site Settings --> Global Settings --> Execute Custom Code and run the following query

<!--- start code --->

<cfquery name="getdata" datasource="#application.config.DSN#">
Select Tablename1, Tablename2, Tablename3, Tablename4, Tablename5, Tablename6, Tablename7, Tablename8, Tablename9, Tablename10, FilefolderName from MenuCategories where MenuID = 6
</cfquery>

<cfdump var="#getdata#">
<cfabort>

<!---end code ---> 

Tablename1 contains the primary items table for the application.
Tablename2 contains the folder or category values. 

 

Locate Your Application’s Files:

Files are also stored by menuID. To locate your files you will again need your Original Application Name and your MenuID. Files are stored on the web server. You can identify the location of your site files on the server by going to Admin --> Site Settings and checking the Server Drive & Path Location value.

appnames3.png

Once you have identified the location of the root intranet folder, run the query above (see: Locate application tables via the MenuCategories table). This will give you the filefoldername value for your application. The path referenced here should be appended to the Server Drive value noted on your site settings page. 

Have more questions? Submit a request

0 Comments

Article is closed for comments.