Understanding the import merge key

[Dale Lucas]

When importing/merging from Excel using a map, I've specified a Merge Key for
each table coming in; tasks, resources, and assignments. The basic concept of
this key makes sense so the correct record can be matched and updated.
However, I am trying to find out more about how Project uses this as I am
baffled by an error that I think relates to my lack of understanding this
mechanism.

I'm importing from a workbook with a tasks, resources, and assignments tab.
I've assigned a text field as the merge field for all three. This imports
without error for the Tasks and Resources, even with non-numeric text in the
field. However, for the Assignments table, if that field contains text and
not a number I get an error dialog with this message, "The number you
entered is invalid or it is outside the range for a Number field. Make sure
you are typing only numbers and no letters or other characters in this field,
or type a smaller number." Again, this only occurs for the Assignments table.

If I change the value in the Assignments merge key field to a number instead
of text, the error goes away. And it seems that the Assignments merge key is
relating to the Unique ID field in the task list, regardless of what I
specify as the merge key in the Tasks table. Is the Import mechanism
hard-wired to relate the assigments merge key to *only* the Unique ID field?

Aside from my specific problem (which I probably didn't explain clearly
enough), can someone either explain more about the merge key to me or point
me to some source for a deeper explanation than is in the basic MS Project
help?

Thanks!

- Dale

 

[Rod Gill]

Not sure there is a definitive source for help on this, but the best unique
key is always the Unique ID for Task, resource and assignments.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Dale Lucas]

The problem I'm having with using Unique ID is that from one import from
Excel to the next, the UID can change in MSP for my target task. I know that
sounds strange since the UID stays unchanged for the life of the record in
Project. Let me explain briefly...

Excel Assignment fields:
Unique_ID
My_Unique_WBS_ID
Resouce_Name
Work

Project Assignment fields:
Unique_ID (Merge Key)
My_Unique_WBS_ID
Resouce_Name
Work

Import 1:
I import tasks, resources and assignements with the Unique_IDs in Excel
set to the sequential numbers I know Project will assign when importing this
into a new file.

Import 2:
This comes after the Project file has been edited. The rub comes if some
task imported in Import #1 has been cut/deleted and recreated for whatever
reason. When it's recreated, functionally, it's still the task identified
with My_Unique_WBS_ID. But the Unique ID has changed. Now when I import,
the Excel file knows nothing of the new Unique ID for that task and the
assignment changes don't import properly.

I was hoping to use My_Unique_WBS_ID as the merge key since that is the
visual key that will be maintained by the user in both Project and Excel.

Clear as mud? Thoughts?

Thanks - Dale