Share via

EnterpriseCustomFieldValueUpdate Method

  • Article

The Finance WBS module for ERP Connector uses EnterpriseCustomFieldValueUpdate to add and update task information such as the work breakdown structure (WBS) to enterprise project text fields in Microsoft Office Project Server 2003. The EnterpriseCustomFieldValueUpdate PDS extension is implemented in a separate Microsoft Visual Studio project, EnterpriseCustomFieldPDSExtender, so that you can use or adapt it for applications other than ERP Connector.

EnterpriseCustomFieldValueUpdate updates the custom enterprise text field if it already exists for the task, or creates the text field if it does not exist.

Syntax

<Request>    
   <EnterpriseCustomFieldValueUpdate>      
      <ProjectID></ProjectID>
      <ProjectName></ProjectName>
      <CustomField>
         <DestinationID></DestinationID>
         <FieldID></FieldID>
         <FieldType></FieldType>
         <Value></Value>
      </CustomField>
      -- Additional CustomField elements --     
   </EnterpriseCustomFieldValueUpdate>
</Request>

Parameters

The following table describes the parameters for the ProjectCreateSAP method. If the tag occurrence includes 0, then it is an optional parameter. For more information about the Project database, see pjdb.htm in the Help directory of your Project Server installation.

Table 1. ProjectCreateSAP parameters

Tag Description Type / format Occurrence
ProjectID Valid project ID in MSP_PROJECTS, in the Project Server database. If ProjectID is not included, ProjectName is required. Integer 0 - 1
ProjectName Project name. Ignored if ProjectID is included. Text 0 - 1
CustomField Parent element for custom field values to be changed.   0 - n
DestinationID This is the value in MSP_TEXT_FIELDS.TEXT_REF_UID. Refers to a valid TASK UID (TEXT_CATEGORY = 0). Integer 1
FieldID Unique enterprise custom field ID. This is the value in MSP_TEXT_FIELDS.TEXT_FIELD_ID in the Project Server database. Integer 1
FieldType The type of custom field.
FieldType Description
1 MSP_DATE_FIELDS are custom start and finish dates.
2 MSP_DURATION_FIELDS are custom durations.
3 MSP_FLAG_FIELDS contain custom flag data. The flag value is a bit field (0 or 1).
4 MSP_NUMBER_FIELDS contain custom cost and number values.
5 MSP_TEXT_FIELDS contain custom text information.
Integer:
1 - 5		 1
Value The new custom field value. Text 1

Note  EnterpriseCustomFieldValueUpdate is implemented only for the custom enterprise text fields with the category of task. That is, it can create or update only records in the table MSP_TEXT_FIELDS where TEXT_CATEGORY = 0. You can modify the EnterpriseCustomFieldPDSExtender project to handle custom date, duration, and number fields for tasks.

Return Value

If the request succeeds, EnterpriseCustomFieldValueUpdate returns a successful HRESULT and STATUS (0 = success), and a specific result for each custom field in the request (where 1 = success). One custom field result failure does not invalidate the entire call.

<Reply>
     <HRESULT></HRESULT>
     <STATUS></STATUS>
     <EnterpriseCustomFieldValueUpdate>
          <CustomFields>
               <CustomField>
                    <DestinationID></DestinationID>
                    <result></result>
               </CustomField>
               . . .             
          </CustomFields>
     </EnterpriseCustomFieldValueUpdate>
</Reply>

Example Request and Reply

The following example uses the Windows Server Upgrade.Published project (ProjectID = 16) in the Project Server sample database. The text field for DestinationID = 1 and FieldID = 188743774 exists in that project, so EnterpriseCustomFieldValueUpdate replaces the value with Replaced value. The text field for DestinationID = 6, with the same FieldID in the same project, does not exist, so EnterpriseCustomFieldValueUpdate creates it. The result in both cases is 1, for success.

<Request>
    <EnterpriseCustomFieldValueUpdate>
        <ProjectID>16</ProjectID>
        <CustomField>
            <DestinationID>1</DestinationID>
            <FieldID>188743774</FieldID>
            <FieldType>5</FieldType>
            <Value>Replaced value</Value>
        </CustomField>
        <CustomField>
            <DestinationID>6</DestinationID>
            <FieldID>188743774</FieldID>
            <FieldType>5</FieldType>
            <Value>New value</Value>
        </CustomField>
    </EnterpriseCustomFieldValueUpdate>
</Request>

<Reply>
     <HRESULT>0</HRESULT>
     <STATUS>0</STATUS>
     <EnterpriseCustomFieldValueUpdate>
          <CustomFields>
               <CustomField>
                    <DestinationID>1</DestinationID>
                    <result>1</result>
               </CustomField>
               <CustomField>
                    <DestinationID>6</DestinationID>
                    <result>1</result>
               </CustomField>
          </CustomFields>
     </EnterpriseCustomFieldValueUpdate>
</Reply>

Implementation

The PDS extension EnterpriseCustomFieldValueUpdate is implemented in the Visual Studio project EnterpriseCustomFieldPDSExtender.sln, included in the download. The EnterpriseCustomFieldPDSExtender class is a subclass of the Extender base class, and provides the public XMLRequestEx method required for PDS extensions. The EnterpriseCustomFieldValueUpdate method creates an instance of the Database class. For more information about the Database and Extender classes, see Creating a Managed Code PDS Extension for Project Server 2003.

EnterpriseCustomFieldValueUpdate Method

The EnterpriseCustomFieldValueUpdate method is overloaded. XmlRequestEx calls EnterpriseCustomFieldValueUpdate with the XML request string and the PDS connection string. Following is the method signature for that overload:

private string EnterpriseCustomFieldValueUpdate(
   string sXML, 
   string Connect)

EnterpriseCustomFieldValueUpdate sets the category variable to 0 (tasks), checks for the correct parameters in the request, sets up the result string for custom text fields, and then calls the second overload, which has the following signature:

private string EnterpriseCustomTextFieldValueUpdate(
   Database data, 
   string destinationUID, 
   string fieldID, 
   string projectID, 
   string fieldValue, 
   string category)

The second overload of EnterpriseCustomFieldValueUpdate makes the following two queries:

  1. The first query determines if the MSP_TEXT_FIELDS table has the requested task enterprise text field. If so, the query performs an update; if not, the query inserts a row in the table.

    string query =    "IF (SELECT COUNT(Text_ref_uid)FROM msp_text_fields ";
   query = query + "WHERE (TEXT_REF_UID = " + destinationUID + ") AND (TEXT_FIELD_ID = " 
      + fieldID + ") AND (PROJ_ID = " + projectID + ")) > 0 BEGIN ";
   query = query + "UPDATE MSP_TEXT_FIELDS SET TEXT_VALUE = " + fieldValue + " ";
   query = query + "WHERE (TEXT_REF_UID = " + destinationUID + ") AND (TEXT_FIELD_ID = " 
      + fieldID + ") AND (PROJ_ID = " + projectID + ") ";
   query = query + "END ELSE ";
   query = query + "insert into MSP_TEXT_FIELDS "
      + "(PROJ_ID, TEXT_CATEGORY, TEXT_REF_UID, TEXT_FIELD_ID, TEXT_VALUE)";
   query = query + "VALUES(" + projectID + ", " + category + ", " + destinationUID + ", " 
      + fieldID + ", " + fieldValue + ")";

   result = "" + data.ExecuteCommandQuery(query);

    If you replace the variables by their values from the first request in the example, you can see the result when you run the following query on the sample database using SQL Server Query Analyzer.

    IF (SELECT COUNT(Text_ref_uid) FROM msp_text_fields
   WHERE (TEXT_REF_UID = 1) 
   AND (TEXT_FIELD_ID = 188743774) 
   AND (PROJ_ID = 16)) > 0 
BEGIN 
   UPDATE MSP_TEXT_FIELDS SET TEXT_VALUE = 'Replaced value' 
      WHERE (TEXT_REF_UID = 1) 
      AND (TEXT_FIELD_ID = 188743774) 
      AND (PROJ_ID = 16) 
END 
ELSE
   insert into MSP_TEXT_FIELDS (PROJ_ID, TEXT_CATEGORY, TEXT_REF_UID, TEXT_FIELD_ID, TEXT_VALUE)
      VALUES(16, 0, 1, 188743774, 'Replaced value')

  2. The second query sets the flags that notify Microsoft Office Project Professional that the project and text fields have been edited outside of Project. That allows Project to correctly query and use the updated information.

    query = "UPDATE    MSP_PROJECTS ";
   query = query + "SET PROJ_EXT_EDITED = 1, PROJ_EXT_EDITED_TEXT = 1 ";
   query = query + "WHERE     (PROJ_ID = " + projectID + ") ";

   data.ExecuteCommandQuery(query);
   return result;

The second EnterpriseCustomFieldValueUpdate overload returns the result to the first method overload, which returns the XML reply you see in the example. The essential code of the first EnterpriseCustomFieldValueUpdate overload follows, where the call to the second overload is shown in bold font:

 for(int i = 1; 
     i <= this.RequestPropertyCount("descendant::*[name(.) = 'CustomField']");
     i++)
   {
      switch (xn.SelectSingleNode("descendant::*[name(.) = 'FieldType']").InnerText)
      {
         . . .
         case "5":
            //MSP_TEXT_FIELDS contains custom text information. 
            errorMessage = "Error in write custom field to database";
            
            result = result + "<CustomField>" + 
               "<DestinationID>" + destinationID + 
               "</DestinationID>" + "<result>" + 
               this.EnterpriseCustomTextFieldValueUpdate(data, 
                  taskUID, fieldID, projectID, fieldValue, category) + 
                  "</result>" + "</CustomField>";
            break;

         default:
            return ReplyError(9000, xn.SelectSingleNode("FieldType").InnerText + 
               " is not a valid field type!");
      }//switch
   }//for

   //Close connection and reply
   errorMessage = "Error close Connection";
   data.CloseConnection();

   return Reply(result + "</CustomFields>");
}

The Reply method is inherited from the Extender class.