Howto
Note
Project management requires either a Sales Premium, Service Premium, or Marketing essentials license. For details, see the list of user plans.
The project table contains the name and IDs of some of the other items.
The relations are one-to-one mostly. At this level, the database structure is not very complicated. So putting together a project display is just a matter of reading the right record, and then following the relations arrows out to the right tables.
Project type
The project type comes from the ProjType table. The project table contains a type_idx field, which contains the foreign key of the ProjType record we want. The name and tooltip are stored in the ProjType table.
To find the project type:
SELECT * FROM projtype WHERE projtype_id = 1234
Replace 1234 with the type_idx in your project.
Here, we get one record with a text and a tooltip description. The tooltip is displayed when you hover the mouse over the text, and when you are editing the record.
Project status
The project status is similar – the project table contains the foreign key in the status_idx field, and the actual name of the status is held in the ProjStatus table.
Both type and status fields are examples of list items.
Project manager/owner
The employees are stored in the associate table. The project table has an associate_id field that we use to look up the associate record. The associate record refers to a person record through its person_id.
Note
The name in the associate table is not the full name – it's the login name, the user name. The full name is stored in the person record.
Custom fields
Any user-defined field values are stored in udprojectSmall or udprojectLarge, while the labels and default values for these fields are stored in a table called UdefField.
URL records
The UI field called Website can contain zero or more web addresses. There is no URL field in the project record, but there is a table called URL that contains a many-to-one relation through its project_id field.
If you want to put together a list of the URLs that belong to this project, the SQL is this:
SELECT * FROM url WHERE project_id = 1234 ORDER BY rank
There may be several URLs all referencing the same project_id. This is OK. The URLs will be presented in rank order. The first rank will always be 1.