Mapping fields in Microsoft Project to Clarity

Document ID : KB000025388
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

This document describes the required steps for mapping fields in Microsoft Project (MSP) to CA Clarity PPM.

Solution:

In order to map fields between Clarity and Microsoft Project, a table in the Clarity database called ‘MSPFIELD’ will need to be updated. Do not do this yourself on the Server, and do not do it if you don't understand SQL.

To create a nemapping, add a new row into the table MSPFIELD, following these rules for each column on that table:

PRID: This must be a unique integer.

PRNAME: This is the ID of the attribute in Clarity.

MSPNAME: This is the field name in MSP.

 

PRTYPE: This needs to be an integer value. The only acceptable values are 1,2, or 3.

1 means the Clarity attribute is on the Project Object.

2 means the Clarity attribute is on the Resource Object.

3 means the Clarity attribute is on the Task Object.

 

PRFLAGS: This needs to be an integer value. The only acceptable values are 1,2, or 3.

1 means to Import the field into Clarity only.

2 means to Export the the field from Clarity only.

3 means to Import and Export to and from Clarity.  

 

Steps to map fields in MSP to Clarity:

Create the custom attribute (i.e. "prfootext") in Studio for the appropriate object type (i.e. task)

Find the next numerical value for the PRID column in the MSPFIELD table:  

select max(prid) from MSPFIELD  

Add one to the query results 'prid' value to get the next number in the sequence.

Select a canned mapping field in MSP. These are fields that have numbers in them like Cost1, Finish3, Number 8, Text29, etc.

Check to ensure it is not already mapped: 

select mspname from MSPFIELD  

For this example, to insert the field mapping in the MSPFIELD table, we would use the following values:

PRID = 21
PRNAME = 'prfootext'
MSPNAME = 'TEXT12'

PRTYPE = 3
PRFLAGS=3

Then, run this query on the database:

INSERT INTO MSPFIELD VALUES(21, 'prfootext','TEXT12',3,3);  

To remove a field mapping from the MSPFIELD table, using the same PRNAME example 'prfootext', run the following query:

DELETE FROM MSPFIELD WHERE PRID = (SELECT PRID FROM MSPFIELD WHERE PRNAME = 'prfootext' );  

Note: String values are case sensitive.

Be sure to commit the changes after inserting or updating the records in this table.

When adding or removing entries from the MSPField table you must restart MSP for the changes to be reflected.

Important Notes:

The "PRNAME" field in the MSPField table must be entered in all lower case and be set to the attribute ID value used in Clarity Studio.
Clarity does not map custom assignments or team attributes to Microsoft Project.
Mapping of lookup value type fields is not supported.

Reserved MSP attributes that you should NOT use for mapping:

  • Text1 - Used for Task ID from Clarity. (PRTASK PREXTERNALID)
  • Text2- Used to hold the Clarity PPM Guidelines ULP passed to MSP from Clarity.
  • Text3 - The Text3 field is for Resource, Project, Task, and Assignment. This field is used for information required by Schedule Connect. If Text3 is used for another purpose in your organization, change the mapping.
  • Text4 - Used for the work breakdown structure (WBS). Schedule Connect uses this field internally to order the WBS when opening the project in Microsoft Project. You cannot remap this field.
  • Text5 - Used to store the Charge Code.
  • Text6 - Used to store Category dataat the task level.
  • Flag1 – Used to indicate whether a task is a Key Task.

 

Please refer to the CA PPM Project Management User guide for more information on all the mapped fields between MSP and Clarity.