Re: Can Lookup Attributes be Updated using CRM 4.0 Data Import? by Todd
Todd
Fri May 09 09:27:55 CDT 2008
I should also point out that I have also tried using a TerritoryID
column in the .csv file in lieu of TerritoryName, which I thought
would allow me to put the GUID value of the correct territory in file
and no longer need to configure the lookupmapping in the importmap.
With this approach I tried both types of formatting shown below but
both do nothing when modecode is set for UPDATE:
"ID","LeaseID","LesseeAccountID","LeaseEndDate","TerritoryID"
"{1C0544A7-791C-DD11-
A4B1-000C29DD6BE7}","123456789","JDOE123","2012-11-12
00:00:00","{C84942D2-3BF0-DC11-907F-000C29DD6BE7}"
"ID","LeaseID","LesseeAccountID","LeaseEndDate","TerritoryID"
"{1C0544A7-791C-DD11-
A4B1-000C29DD6BE7}","123456789","JDOE123","2012-11-12
00:00:00","territory,{C84942D2-3BF0-DC11-907F-000C29DD6BE7}"
Again, other attributes like LeaseEndDate seem to update just fine -
my problem appears isolated to all Lookup columns when in UPDATE mode.
Thanks,
Todd
On May 9, 8:22 am, Todd Langdon <todd.lang...@gmail.com> wrote:
> Hi Koushik,
>
> Thanks for your reply. I'm very curious - why do you say it may work
> for create but not update even if I define the lookups? Is there
> something in the docs I missed that says that is the case?
>
> The import file is a comma-separated, double-quote delimited data file
> that did not come from CRM. I am using SSIS to create the file based
> on the results of a query of leases from a lease record-keeping system
> this client has. I am importing one file per run of my code and, yes,
> I am specifying it is for import by setting the import.modecode = 1
> (for Update). Here's what it looks like with one lease (I've cut it
> down to simplify this example):
>
> "ID","LeaseID","LesseeAccountID","LeaseEndDate","TerritoryName"
> "{1C0544A7-791C-DD11-
> A4B1-000C29DD6BE7}","123456789","JDOE123","2012-11-12
> 00:00:00","Northeast"
>
> ID=Primary Key GUID for CRM new_lease record to update
> LeaseID=Lease Contract Number as new_lease attribute
> LesseeAccountID=Lookup to the Lessee's CRM account record using
> accountnumber
> LeaseEndDate=End Date as new_lease attribute
> TerritoryName=Lookup to CRM territory record using name
>
> I am creating new import, importfile and importmap objects and running
> the import programmatically on each run. The importmap code that
> creates the columnmappings and lookupmappings is based on the
> datamigration complexmapping.cs sample code in the SDK. So, for
> example, here is how I'm doing the TerritoryName column mapping:
>
> columnmapping colmap = new columnmapping();
>
> // Set source properties
> colmap.sourceattributename = "TerritoryName";
> colmap.sourceentityname = "new_lease";
> // Set target properties
> colmap.targetattributename = "new_territoryid";
> colmap.targetentityname = "new_lease";
> // Relate this column mapping with the data map
> colmap.importmapid = new Lookup();
> colmap.importmapid.type = EntityName.importmap.ToString();
> colmap.importmapid.Value = importMapId;
> // Force this column to be processed
> colmap.processcode = new Picklist();
> colmap.processcode.Value = ImportProcessCode;
>
> // Create the mapping
> Guid colMappingId;
> try
> {
> colMappingId = service.Create(colmap);
> }
> catch (SoapException ex)
> {
> throw new ApplicationException(
> String.Format("Unable to Create Import Data Map
> Column Mapping for '{0}.{1}' Target due to error: {2}",
> targetEntityName, targetAttributeName,
> ex.Detail.InnerText));
> }
> colmap.columnmappingid = new Key();
> colmap.columnmappingid.Value = colMappingId;
>
> Here's the code that then does the Lookup Mapping for the
> TerritoryName column:
>
> // Create a lookup mapping.
> lookupmapping lookupMap = new lookupmapping();
>
> // Relate this mapping with its parent column mapping
> lookupMap.columnmappingid = new Lookup();
> lookupMap.columnmappingid.type =
> EntityName.columnmapping.ToString();
> lookupMap.columnmappingid.Value =
> colmap.columnmappingid.Value;
>
> // Force this column to be processed
> lookupMap.processcode = new Picklist();
> lookupMap.processcode.Value = 1; //1=Process
>
> // Set lookup for an account entity by its name attribute
> lookupMap.lookupentityname = "territory";
> lookupMap.lookupattributename = "name";
> lookupMap.lookupsourcecode = new Picklist();
> lookupMap.lookupsourcecode.Value = 1; //1=System
>
> // Create the lookup mapping
> Guid lookupMappingId;
> try
> {
> lookupMappingId = service.Create(lookupMap);
> }
> catch (SoapException ex)
> {
> throw new ApplicationException(
> String.Format("Unable to Create Import Data Map
> Lookup Mapping for Column Mapping '{0}' to '{1}.{2}' Target due to
> error: {3}",
> columnMappingId, lookupEntityName,
> lookupAttributeName, ex.Detail.InnerText));
> }
> lookupMap.lookupmappingid = new Key();
> lookupMap.lookupmappingid.Value = lookupMappingId;
>
> On May 9, 1:52 am, "Koushik" <kobha...@hotmail.com> wrote:
>
> > I am assuming that you have written code using Import SDK to import lease
> > entity. The territory that you want to a look up to, is present in CRM
> > system already. Can you elaborate little more on the map you are using and
> > how did you create the map. In order to import lookup values properly, you
> > need to have the lookup mappings defined properly.
>
> > Specifically I would like to know...
>
> > 1. The map.
> > 2. Is it a single file import for update?
> > 3. During import for update, how did you get the csv file? did you export
> > from crm with the guids? Can you provide sample data that you are using for
> > create and update?
>
> > It may be possible that, even if you defined lookup mappings, it may work
> > for create and not update. If I see the map and sample csv file, I can tell
> > better.
>
> > Thanks,
> > Koushik
>
> > "Todd Langdon" <todd.lang...@gmail.com> wrote in message
>
> >news:55c68bc9-9807-493b-bf47-48725f2fbdea@d1g2000hsg.googlegroups.com...
>
> > >I wrote some code that uses the CRM 4.0 Data Import functionality to
> > > process files that create and update data for custom entities I have
> > > created. It was confusing to figure out because of the sparse
> > > documentation but it seemed to be working nicely until I noticed that
> > > when ImportModeCode is Update the entity attributes that are Lookups
> > > don't seem to be getting updated.
>
> > > For example, I created a custom entity named new_lease that has a
> > > new_territoryid lookup attribute that looks up it's data from the
> > > territory entity. The code populates the new_territoryid value when
> > > importing leases and ImportModeCode is CREATED; however, it does not
> > > change the territory when importing leases when ImportModeCode is
> > > UPDATE. It's not just territory-related lookups that are failing -
> > > others such as those to Contacts and Accounts don't work either. No
> > > error is thrown and the rows show as "Leases Updated" under the System
> > > Job. Other non-lookup-type columns on the entities are being updated
> > > with values in the files.
>
> > > Is there some reason that Lookup Attributes cannot be UPDATED using
> > > Data Import functionality as is documented in the SDK?
>
> > > Any advice is greatly appreciated....
>
> > > Thanks,
> > > Todd Langdon