What’s New with P6-Loader v14

Emerald just released P6-Loader v14 that features additional enhancements/functionality based on feedback from one of our clients.

The client wanted to add duplicate resources to activities using P6-Loader and also control what type of action to perform on a row in the P6-Loader sheet.

Duplicate Resource Loading

The solution we implemented for duplicate resources involves using a User-Defined Field (UDF) to create a unique ID for loading the duplicate resources. P6-Loader can be configured to generate unique IDs for resource assignments using a pre-configured integer UDF type, which can be enabled in the P6-Loader configurator. The IDs are unique just within the activity the resource assignment belongs to.

P6-Loader uses the following rules:

• Resource assignments are sorted based on their internal ID in P6. The first UDF receives the value 1, the second UDF 2, and so on.
• If a UDF already has a value, that value is used, as long as it is bigger than the value assigned to the previous resource assignment, following the logic above.

Suppose there are three resource assignments in P6 on the activity, in this order:



When P6-Loader runs the first extraction, the following values are assigned to the UDF “RA Unique ID”:



Now if resource “B” is deleted and resource “D” is added in P6, the following values will be assigned to the UDF upon extraction:



If someone changes the UDF value assigned to resource “C” to 100 in P6, D will be given an ID of 101 instead:



The value of the UDF for resource “D” is also updated since it must be given a larger value than the value assigned to resource “C”.

When our data is extracted from P6, the Excel file looks like this:

Loading Files

If the logic to match the UDFs is based on the UDF being used when a P6-Loader file is loaded, the following takes place:

• Unique UDF values are assigned to the resource assignments in each project being processed following the rules defined above.
• Resource assignments are matched using the Project ID, Activity ID, and the UDF value in each row.

The “Unique ID” column name can be defined in either of these formats:

• If using the UDF(): prefix for the column, the value is assigned to the UDF. This can be useful during INSERT options but is redundant during UPDATE operations. (More on UPDATE and INSERT options later in the article.)
  • You can use this to make sure new resource assignments receive a predetermined value for the UDF.
  • You can ensure that a record can only be inserted or updated using the [Record Action] column.
• If not using the UDF():prefix, the value is only used to match the resource assignment but is never assigned to it.
• By default, the column is exported with the UDF(): prefix as shown in the example above.

Examples:



In this example, the records will be inserted into the database. If a match is found, then nothing will take place because records were already found in the database.

In the example above, I can force the records to be added by assigning a higher value to the UDF, even if the example already exists in P6. In this case, I will assign unique values starting with 100 for the resource assignments of activity “11354465-0”:



With this done, six new resource assignments will be added.

Extracting the data will show us the records that were inserted, as well as the value that is assigned to the UDF:



These are the same UDF values that were loaded to the database.

Record Action Column

The [Record Action] column allows you to define which action you want to be done for a given row in the sheet being loaded. When the file is loaded, if the action defined is not what the P6-Loader should do for that record, then the record is ignored.

Use this column when you want to ensure that a given record in the Excel file can only be inserted, updated, or deleted in the database. For example:

• You are updating certain user fields but you want to make sure no new users are added:
  • Define the “Record Action” column with the UPDATE value.
  • Existing users will be updated, but no new users will be added.
• You want to add new resources from another database, but you want to make sure the data on existing resources is not changed:
  • Define the “Record Action” column with the INSERT value.
  • New resources will be added, but no existing resources will be changed.
  • This also applies to UDFs and codes - these will not be updated in this scenario.

The [Record Action] column with the DELETE value can be used as an additional fail-safe, but since you still have to set the [Delete Record] column to DELETE, our recommendation is not to use the “Record Action” column when deleting - just leave it blank.

If the column is left blank or if the column doesn’t exist at all, then no action is enforced during the extraction.

This is what the column looks like in an Excel file:



In our example, all those records exist in P6 already.

Trying to load the file produces the following warnings:

• [Activity] Row #1: Needed action for row is Update, but configured action in Excel file is Insert, row will not be processed
• [Activity] Row #2: Needed action for row is Update, but configured action in Excel file is Insert, row will not be processed
• [Activity] Row #6: Needed action for row is Update, but configured action in Excel file is Delete, row will not be processed
• [Activity] Row #7: Needed action for row is Update, but configured action in Excel file is Delete, row will not be processed

Using the combination of these two enhancements allowed our client to load their duplicate resources properly, without compromising any of the data already in their database. This feature is now available in new P6-Loader installations. If you are a current client with P6-Loader and would like to add this functionality, reach out to us to upgrade your P6-Loader.