Import Assignments

This feature allows for the importing of  Project or Task Assignment information into the Unanet system.  

Within one assignment import file, an Administrator can import project or task assignment data for one to many projects and tasks, across multiple organizations if desired.  The Resource Assigner version of the import restricts the importing of project and task assignment data to the specific org/project the Resource Assigner is currently operating on.

Roles

This screen is available to users having any of the following roles: Administrator, P&R Administrator (and Resource Assigner has access to project level version).

License

This feature is available with any Unanet edition.

Performance Tip Note:  Depending on the number of users in your installation and other factors such as the overall performance of your platform, the activity of importing one or many files may take up to several minutes.  Should this be the case with your installation, you may want to consider running these processes at non-peak times to minimize database contention.

 

Topics covered on this help page include:


Import File Record Layout

 

 

Field Header Name

Required/Comments

1

A

Project_Org_Code

ALWAYS REQUIRED.  This code uniquely identifies the Organization to which the project belongs.  This value must match an existing Project Organization Code value defined in your system.  If the Organization you are trying to import does not already exist in the database, the entire record will be rejected.

2

B

Project_Code

ALWAYS REQUIRED.  This project code identifies the project to which this Assignment belongs.  The project code is unique within an Organization.   This value must match an existing Project Code value defined in your system.  If the Project you are trying to import does not already exist in the database,  the entire record will be rejected.

3

C

Task_Name

CONDITIONALLY REQUIRED. This field is required if the project you are working on supports task level assignments.   Otherwise, this field is not allowed (e.g. if the project is managed at the project level -- import records will be rejected if this field is populated).  

The Task Name should be unique at the same indentation level under a project.

This value must match an existing Task Name value in your system.  If the Task Name you are trying to import does not already exist in the database,  the entire record will be rejected.

If a task is not a top-level task (e.g. it has sub-tasks), you must include a comma separated list of each task starting at the top level down.  The following is an example of a valid task tree and how you would reflect each task using this import.

Task Tree Access String

1.   Car Repair

"Car Repair"

1.1     Tires

"Car Repair,Tires"

1.2     Paint

"Car Repair,Paint"

1.2.1      Prep work

"Car Repair,Paint,Prep work"

1.2.2      Patching

"Car Repair,Paint,Patching"

1.3     Engine

"Car Repair,Engine"

1.3.1      Tune-up

"Car Repair,Engine,Tune-up"

1.3.1      Rebuild

"Car Repair,Engine,Rebuild"

See the Excel Tips regarding the use of double quotes and more.

4

D

Username

ALWAYS REQUIRED. Unique username (user id), for example JDoe or jdoe (it does not need to be capitalized.)  This value must match an existing Username value in your system.  If the Username you are trying to import does not already exist in the database,  the entire record will be rejected.

Financial Org Restriction  When the Use Financial Organizations (unanet.financials.use_financial_orgs) property is enabled, valid users to assign will be limited to those user's having a Person Organization with the same Legal Entity as that of the Project's Owning Organization (unless the user's Person Org is not a Financial Organization).

5

E

Begin_Date

This date is used to indicate a date bound assignment.  If no date is supplied, then BOT (beginning of time) is assumed.  If this is an update to, or a deletion of an existing assignment, then this value must match the value on the existing assignment.

When updating an existing assignment, if one of the fields you are attempting to update is the Begin Date, you'll need to supply the following syntax in this field.

"!UPDATE!,old_begin_date,new_begin_date"  -- this entire string must be enclosed in double quotes (note that if you are working in Excel, Excel will provide the double quotes and thus you do not need to add them in manually).

Using BOT  (Beginning of Time)

If the existing assignment does not have a Begin Date (and you are attempting to update the assignment and supply one), you'll need to supply the value BOT in the  'old_begin_date' parameter, for example:
"!UPDATE!,BOT,new_begin_date"

If you are attempting to remove an existing Begin Date, effectively leaving the assignment unbounded, you can supply the Beginning of Time (BOT) tag, for example:

"!UPDATE!,2005-01-15,BOT"

Recommended date format:   yyyy-MM-dd    See Allowable Date Formats for more options.

See the Excel Tips regarding the use of double quotes and more.

See date overlap restriction note below.

6

F

End_Date

This date is used to indicate a date bound assignment.  If no date is supplied, then EOT (end of time) is assumed.

Using EOT  (End of Time)

If you are attempting to remove an existing End Date, effectively leaving the assignment unbounded, you can supply the End of Time (EOT) tag, for example:

EOT

Recommended date format:   yyyy-MM-dd    See Allowable Date Formats for more options.

See date overlap restriction note below.

7

G

Delete

This field can be used to trigger the system to delete an Assignment.  In order for the delete to happen, you must include the following text string !DELETE! in this field.

8

H

Budget_Hours

Restricts or suggests a limitation on the number of hours that may be charged for this assignment.  (See also Exceed_Budget.)  The default (a blank entry) means there will be no limit imposed.  

You can use the special tag !BLANK! to remove an existing entry in this field.

This field accepts a numeric value up to 13 positions to the left of the decimal and 2 positions to the right.

9

I

Exceed_Budget

Indicates whether the Budget Hours field is a suggestion or a hard limit. The Budget_Hours number will be validated against as users enter time and can prevent the submittal of a timesheet unless this Exceeds value is set.   A value of "Y" indicates that the Budget Hours may be exceeded, and a value of "N" indicates that they cannot .  

If no value is supplied, the default for this field when creating a new entry is controlled by the Allow Budget to be Exceeded (unanet.default.allow_budget_to_exceed) property.

10

J

Bill_Rate

This field is used to specify whether the bill rate for this assignment should use:

  • the person's rate - enter a "P"

  • the labor category rate - enter an "L"

  • a specific dollar amount - enter the hourly bill rate (e.g. 125.00)

 

If no value is provided, then this field will be set to either P or L -- depending on the project's bill rate source setting.

This field accepts a numeric value up to 10 positions to the left of the decimal and 5 positions to the right.

Note about Resource Assigners importing the Bill Rate field: This field will only be recognized for Resource Assigners that also have the 'Bill Rate Manager' role.  If a Resource Assigner does not have this role, the Assignment will use the person's default person bill rate - and the amount in this field will be ignored.  Further, if a resource assigner is running this import, the Allow Bill Rate Edit (unasource.allow_bill_rate_edit) property will determine if this value may be manipulated.  No such restrictions apply to administrative users running this import.

11

K

Cost_Rate

This field is used to specify whether the cost rate for this assignment should use:

  • the person's rate - enter a "P"

  • the labor category rate - enter an "L"

  • a specific dollar amount - enter the hourly cost rate (e.g. 125.00)

 

If no value is provided, then this field will be set to either P or L -- depending on the project's cost rate source setting.

This field accepts a numeric value up to 10 positions to the left of the decimal and 5 positions to the right.

Note about Resource Assigner importing the Cost Rate field: This field will only be recognized for Resource Assigners that also have the 'Cost Rate Manager' role.  If a Resource Assigner does not have this role, the Assignment will use the person's default person cost rate - and the amount in this field will be ignored.  Further, if a resource assigner is running this import, the  Allow Cost Rate Edit (unasource.allow_cost_rate_edit) property will determine if this value may be manipulated.   No such restrictions apply to administrative users running this import.

If Cost Dilution is enabled, the contents of this field will be ignored during import.

12

L

Project_Org_Override

This field allows you to specify a project organization to use for this assignment instead of the project's default organization.  This value must match an existing Organization Code value in your system.  If the Organization you are trying to import does not already exist in the database, the entire record will be rejected.

You can use the special tag !BLANK! to remove an existing entry in this field.

13

M

Person_Org_Override

This field allows you to specify a person organization to use for this assignment instead of the person's default organization. This value must match an existing Organization Code value in your system.  If the Organization you are trying to import does not already exist in the database, the entire record will be rejected.

You can use the special tag !BLANK! to remove an existing entry in this field.

14

N

Labor_Category

This field allows you to specify a Labor Category to use for this Assignment work entry.  If you do not specify one, the person's default Labor Category, if present,  will be associated with reported time.  

This value must match an existing Labor Category value in your system.  If the Labor Category you are trying to import does not already exist in the database, the entire record will be rejected.

You can use the special tag !BLANK! to remove an existing entry in this field.

15

O

Location

This field allows you to specify a Location to use for this assignment.  If you do not specify one, no Location will be used by default.  This value must match an existing Location value in your system.  If the Location you are trying to import does not already exist in the database, the entire record will be rejected.   You can use the special tag !BLANK! to remove an existing entry in this field.

16

P

ETC_Hours

This field allows you to supply a value for the ETC (Estimate to Complete) field.  If you do not supply a value, the field will remain empty.  You can use the special tag !BLANK! to remove an existing entry in this field.

This field accepts a numeric value up to 13 positions to the left of the decimal and 2 positions to the right.

Note that the ETC/EDC Last Updated Date value will be updated with the system's current date/time stamp - if either the ETC_Hours or EDC fields reflect a change from what is currently stored in the database.

17

Q

Use_Wbs_Dates

This field allows you to indicate that the Assignment dates will be linked with the dates specified on the Project (or Task -- depending on the Project/Task Assignment level for the project) rather than explicitly defining dates for the assignment.  When used, any dates supplied in the Begin_Date and End_Date fields will be ignored on a new add.

A value of "Y" indicates that the dates will be linked to the project/task dates, and a value of "N" indicates that they will not be linked.  The default is "N".

If the run time "Allow for Assignments to be linked to Project/Task dates" check box is not checked (see example  import screen option below) and this value is set to "Y", an error message will be displayed.  Unlinking an existing linked assignment is allowed with or without the run time option checked.

18

R

Cost_Structure

CONDITIONALLY REQUIRED.  This field is used in conjunction with the Cost_Element field.  Both of these fields must be supplied if the intention is to provide values for Cost_Structure/Cost_Element in the import file. When supplied on an assignment, this value will override the user's default Cost Structure / Cost Element defined on their person profile (when charging to this project/task).

This field can be blank.  To remove an existing value, you'll need to supply the special tag !BLANK! in the Cost_Element field.

This value must match an existing Cost Structure code in your system.  If the Cost Structure you are trying to import does not already exist in the database, the entire record will be rejected.

19

S

Cost_Element

CONDITIONALLY REQUIRED.  This field is used in conjunction with the Cost_Structure field.  Both of these fields must be supplied if the intention is to provide values for Cost_Structure/Cost_Element in the import file.  When supplied on an assignment, this value will override the user's default Cost Structure / Cost Element defined on their person profile (when charging to this project/task).

This field can be blank.  To remove an existing value, you'll need to supply the special tag !BLANK! in this field. Note that blanking out the value in this field will also remove any existing value in the Cost_Structure field.

This value must match an existing Cost Element code in your system.  If the Cost Element you are trying to import does not already exist in the database, the entire record will be rejected.

20

T

EDC

EDC is Estimated Date of Completion.  This is an optional field that can be used to capture when a particular project or task assignment is expected to be completed.

This field can be blank.  You can supply the special tag !BLANK! in this field to remove an existing entry from the database.

Note that the ETC/EDC Last Updated Date value will be updated with the system's current date/time stamp - if either the ETC_Hours or EDC fields reflect a change from what is currently stored in the database.

 

Note: Check out the Unanet Data model for specific field data types, lengths, and other attributes.


Import File Format

The file to import must be saved in a comma delimited format.   The fields can be enclosed in double quotes -- which would be particularly necessary should the data being imported contain commas.

Importing a Sub-Set of Columns using a Field Header Record

If you are not using the default column layout sequence as defined above, you must include a header record (prefixed with an asterisk *), containing the column Header Names for those columns you are including in the import file (to indicated what data is contained in each column).  The specific Header Names for each column are listed in the table above.

Example:

*Organization_Code,Project_Code,Username,Begin_Date,Delete
org1,proj1,jsmith,1/1/2005,!DELETE!


Date Overlap Restriction

When inserting Assignment entries for a particular user, you cannot import entries for the same user, organization, project, task and labor category that have date ranges that overlap.  Entries that would result in an overlap situation will not be imported, an error message will be written and the import will continue with the next record.


Excel Template

You can create the comma delimited import file with any number of tools.  For those interested in using an Excel spreadsheet to create the file, you can download an Excel Template with predefined headers and required fields noted.  

See the Excel Tips regarding the use of double quotes and more.


Import Screen

Depending on your property settings,the Import Assignments screen may look like:

Field Descriptions:

Field

Description

Type of Import

Select the appropriate import type from the list of available types of imports.

File to Import

Enter (or browse for) the file that you would like to import.

Output Options

Specify the level of detail included in the output.

Assignments

When linking Assignment dates to the Project/Task dates, any existing Assignment dates supplied will be overridden.  Also, when using this feature only one Assignment per Project / Task / User is allowed and thus existing Assignment records may be removed.  Because of this possibility of losing existing data, this check box is present to make sure any resulting data removal is intentional.

Email Notification

Allows you to optionally send out email notifications for new and changed project and task assignments.  Note that users do have the ability (via the user profile screen), to choose to not receive assignment email notifications.

 

The import process is triggered by pressing the Import button.  All Warnings and Error messages are written to the screen and can then be saved if desired.  While some line items may be found invalid and rejected, the remaining line items will be successfully imported and saved in the database.  The rejected line items will be written out to a file on the Unanet server as well as displayed at the bottom of the user feedback screen.  The rejected record file name will be UnanetAssignmentsImportErrorxxx, where xxx is a randomly generated number.  The file will be placed in the Unanet temporary directory (which is defined by the Temp Directory (Fully Qualified Directory Name) (unanet.temp_directory) Unanet property).

Related Topics