This article provides a detailed overview of the technical specifications required to import User Defined Field (UDF) values using the Excel Import functionality.
Note: For Browse Type UDFs, you can now provide the object Name instead of its unique ID value, simplifying the import process compared to the previous version behavior.
Leveraging API Approach:
The Excel Import process for UDFs follows the same method used in APIs, utilizing the CustomProperties property, which is of type Dictionary<string, object> to handle UDF data.
UDF Column Naming Convention:
UDF columns in the Excel Import must be prefixed with "UDF_" in uppercase, followed by the UDF's Field Name (e.g., "UDF_BugBrowser", "UDF_LeaveStartDate").
Note: The Excel column name for UDFs should reflect the UDF's actual Field Name value, not the Display Name value, ensuring accurate mapping during the import process.
Consistent Error Handling:
Error handling for Excel Import mirrors the REST API behavior. Any error scenarios that occur in the API will also trigger similar failures in the Excel import process, maintaining consistency.
Handling Multi-Value UDFs:
For UDFs that accept multiple values, these values must be provided as a comma-separated list without spaces (e.g., "Value1,Value2,Value3") in the respective UDF column.
Data Type Validation:
When adding a Numeric UDF column (e.g., Percentage or Integer UDF), ensure that values are provided in the correct data type format. If no value is entered, a data type error will be displayed.
Clearing UDF Values:
When adding a Non-Numeric UDF column (e.g., Text or Memo UDF) with no value provided, any existing value in the UDF will be cleared without displaying an error.
Preserving Existing UDF Values:
To leave the value of a UDF unchanged in PSA, simply remove the corresponding UDF column from the Excel Import Template. If the UDF already contains a value, this approach will prevent any modifications to it during the import process.
Handling Required UDFs:
If a required UDF is missing in the Excel template, an error message will display stating, "The required property UDF [Text 1] is missing," ensuring that all mandatory fields are included.
Time-Based UDF Values:
For UDFs that store time or timespan data, the values must be provided in milliseconds to maintain consistency with the system's time format.
Date-Based UDF Values:
For UDFs that store date data, enter values in MM/DD/YYYY format and set the column format to Date in Excel.
To indicate an open-ended date, use 11/27/2737.
History Tracking for UDFs:
When History tracking is enabled for a UDF (e.g., Project, User, Client), any update made via Excel Import will create a History record, including the User Name of the User who performed the import.
Case Sensitivity for Health UDFs:
The value for Health UDFs is case-sensitive and must be provided in uppercase (e.g., "RED"). Any value provided in a different case, such as "Red" or "rED," will be considered invalid.
System Table UDFs:
For System Table UDFs, the value provided should be the Key of the respective object, not its Description, ensuring proper reference to system-defined objects.
Handling Disassociated UDFs:
If a UDF has been disassociated from an object but the Excel Import Template still includes the UDF column, no error will be displayed, and the column will simply be ignored.
Permission Errors for Create/Update:
If the logged-in user lacks the necessary Create or Modify permissions for a particular object in PSA, upon attempting to perform these actions, an error message will appear: "The current user does not have rights to perform this action."
Calculated and advanced UDFs:
Calculated and Advanced User Defined Fields are not supported in the Excel Import functionality.