Microsoft Project Server has a couple of places to view the
entire schema of the databases. In the SDK, if you look for projdb.htm and
servrdb.htm, these documents contain a lot of schema information. In this article
we will do a deep dive on 3 tables.
For the setup of this report, we will be using tables
msp_projects, msp_tasks and msp_text_fields. If you are familiar with the
backend of Project Server you know that msp_projects and msp_tasks contain the
field proj_id. MSP_tasks include tasks_id and tasks_uid. Tasks_uid is the
unique identifier that, when combined with proj_id, creates the primary key for
the msp_tasks table. Task_id is the number in the plan of the task; it is the
number the user sees in the Project Client.
The The msp_text_Fields table is not a straight 3rd form normalized
table. It is more a key value type of table that contains values for custom
fields for both the msp_projects and msp_tasks fields. The structure contains
5 fields. Proj_id relates directly to proj_id in both msp_projects and
msp_Task. Text_ref_uid relates to task_uid in msp_task (if we were using the
resource table, then the ref_uid in msp_resources would map to text_ref_uid).
For msp_projects, the Text_Ref_uid is 0. Text_value is of course, the value
for the key.
Finally, Text_Field_ID is the unique ID of 9 digits that
signifies what custom field in project server this value is for. For instance,
I have created a custom field for a task called the element number. This field
has a task_field_id of 188744479. If you need to find this number, you need to
know what enterprise field this is and use the Visual Basic editor in the
Project client. This explanation is similar for the other key-value tables,
msp_date_fields, msp_flag_fields and msp_number_fields.
In this instance, the element number field is Enterprise Text
1. First, open the Visual Basic Editor then click on the object browser
icon. Under the classes section, find the PjCustomField entry. With that highlighted,
you can find pjCustomTaskEnterpriseText1. With pjCustomTaskEnterpriseText1
highlighted (see Figure 1), the field id displays as 188744479.
Figure 1
