locked
Dynamics CRM : Column Mapping for Currency Field Type RRS feed

  • Question

  • I am working on Dynamics CRM 2013 Online version. I have created code to import Price List items using Import Map.

    Below is the CSV file created from Data Table to be imported.

    PriceList,Product,Unit,QuantitySellingOption,PricingMethod,Amount
    
    PriceList March 2014,Access 2013 Sngl OLP C,Primary Unit,Whole,Currency Amount,₹ 9837.99
    PriceList March 2014,Access 2013 Sngl OLP NL,Primary Unit,Whole,Currency Amount,₹ 10041.84
    PriceList March 2014,Access 2013 SNGL OLP NL Acdmc,Primary Unit,Whole,Currency Amount,₹ 1502.50

    Please note that amount contains Rupee symbol here.

    Below is my code which is mapping the CSV file column to Amount Field on CRM Form of Price Level Entity.

    #region Column6 - Amount, Field Type : Currency
    
                    // Create a column mapping for a 'Currency' type field.
                    ColumnMapping colMapping6 = new ColumnMapping()
                    {
                        // Set source properties.
                        SourceAttributeName = "Amount",
                        SourceEntityName = "PriceList",
    
                        // Set target properties.
                        TargetAttributeName = "amount",
                        TargetEntityName = ProductPriceLevel.EntityLogicalName,
    
                        // Relate this column mapping with the data map.
                        ImportMapId =
                            new EntityReference(ImportMap.EntityLogicalName, importMapId),
    
                        // Force this column to be processed.
                        ProcessCode =
                            new OptionSetValue((int)ColumnMappingProcessCode.Process)
                    };
    
                    // Create the mapping.
                    Guid colMappingId6 = _serviceProxy.Create(colMapping6);
    
    
    
                    #endregion

    Code successfully imports the Price List items to the Price List, but Amount is coming as blank.

    Please suggest what is missing here in Column mapping, or Do I need to change any format in CSV file ?

    Thank you, -Mittal.


    Wednesday, March 19, 2014 4:34 AM

Answers

  • Helo Mittal ,

    Yet your problem not solved , I tried to test the issue

    is seems no error for me

    i used sdk sample

    SDK\SampleCode\CS\DataManagement\DataImport

    and add one new column for revenue

       ColumnMapping colMappingx = new ColumnMapping()
                {
                    // Set source properties.
                    SourceAttributeName = "src_revenue",
                    SourceEntityName = "Account_1",

                    // Set target properties.
                    TargetAttributeName = "revenue",
                    TargetEntityName = Account.EntityLogicalName,

                    // Relate this column mapping with the data map.
                    ImportMapId =
                        new EntityReference(ImportMap.EntityLogicalName, importMapId),

                    // Force this column to be processed.
                    ProcessCode =
                        new OptionSetValue((int)ColumnMappingProcessCode.Process)
                };

    and one column in .csv file as

    src_name,src_parent,src_addresstype,src_revenue
    A. Datum Corporation,Adventure Works,bill,12.0
    Adventure Works,,ship,12.0
    Blue Yonder Airlines,,bill,12.0
    City Power & Light,A. Datum Corporation,bill,12.0
    "Fabrikam, Inc.",Adventure Works,ship,12.0

    It works fine as i see.

    You must test via sdk sample and double check your .csv file .Is their any space you might left.


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"


    Wednesday, March 19, 2014 10:51 AM

All replies

  • remove
    from your csv file

    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    Wednesday, March 19, 2014 5:38 AM
  • Hello Santosh,

    Thank you for your response.

    I have tried removing that so my CSV file looks like,

    PriceList,Product,Unit,QuantitySellingOption,PricingMethod,Amount
    Microsoft March 2014,Access 2013 Sngl OLP C,Primary Unit,Whole,Currency Amount,9837.99
    Microsoft March 2014,Access 2013 Sngl OLP NL,Primary Unit,Whole,Currency Amount,10041.84
    Microsoft March 2014,Access 2013 SNGL OLP NL Acdmc,Primary Unit,Whole,Currency Amount,1502.5
    
    Still it is not inserting the amount in the Price list item.

    Please suggest here.

    Thank you,

    -Mittal.

    Wednesday, March 19, 2014 5:46 AM
  • If not mandatory by code to do so use below link and click on import data

    https://XXX.crm5.dynamics.com/main.aspx?etc=1026&extraqs=%3fpagemode%3diframe&pagetype=entitylist&viewid=%7b2261E01B-B7E6-433A-8D6D-F1FD6AC9F70F%7d&viewtype=1039#977946226

    Then prepare your data file and import it.


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    Wednesday, March 19, 2014 6:14 AM
  • Thank you Santosh.

    It is needed to import it using Code, because Price list is going to update periodically by client. And there are thousands of Products, so we can't manually

    update their price.

    When we write a code to insert Price List Item individually, we do below conversion to type Money.

    	   decimal myMoney = 300.5m;
        myMoney = Convert.ToDecimal(dr["Amount"].ToString().Remove(0,1));
                            Money m1 = new Money(myMoney);
    
                            priceListItem.Attributes["amount"] = m1;
    	
    

    But , in this import Map Code, I am not sure where to do this  conversion.

    Not even I find any forum where they have done Import for Currency type of Field.

    Any other idea on this ?

    Wednesday, March 19, 2014 6:25 AM
  • Decimal decimalMoney = Convert.ToDecimal(inputData[5]);
                    priceListItem["amount"] = new Money(decimalMoney);


    read this:

    http://social.microsoft.com/Forums/en-US/dc4ad6d3-b75b-449f-9df5-6b972d0abd43/creating-currency-fields-programatically?forum=crm

    and http://msdn.microsoft.com/en-us/library/hh547396.aspx
    Wednesday, March 19, 2014 7:44 AM
  • Hello Santosh,

    Where should I try this ?

    I tried this while creating the CSV file.

     sw.Write(new Money(Convert.ToDecimal(dr[i])));

    But still not working.

    I feel that there should be something while mapping the Columns in Import Map.

    In my Question, the columnmapping code you are seeing is for 'Text' type of Field. I have implemented the same for Currency type of field. I am not sure what to change here to differentiate the field as Currency type.

    Thank you very much for your quick responses.

    Wednesday, March 19, 2014 8:31 AM
  • Helo Mittal ,

    Yet your problem not solved , I tried to test the issue

    is seems no error for me

    i used sdk sample

    SDK\SampleCode\CS\DataManagement\DataImport

    and add one new column for revenue

       ColumnMapping colMappingx = new ColumnMapping()
                {
                    // Set source properties.
                    SourceAttributeName = "src_revenue",
                    SourceEntityName = "Account_1",

                    // Set target properties.
                    TargetAttributeName = "revenue",
                    TargetEntityName = Account.EntityLogicalName,

                    // Relate this column mapping with the data map.
                    ImportMapId =
                        new EntityReference(ImportMap.EntityLogicalName, importMapId),

                    // Force this column to be processed.
                    ProcessCode =
                        new OptionSetValue((int)ColumnMappingProcessCode.Process)
                };

    and one column in .csv file as

    src_name,src_parent,src_addresstype,src_revenue
    A. Datum Corporation,Adventure Works,bill,12.0
    Adventure Works,,ship,12.0
    Blue Yonder Airlines,,bill,12.0
    City Power & Light,A. Datum Corporation,bill,12.0
    "Fabrikam, Inc.",Adventure Works,ship,12.0

    It works fine as i see.

    You must test via sdk sample and double check your .csv file .Is their any space you might left.


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"


    Wednesday, March 19, 2014 10:51 AM
  • Thank you Santosh. This worked for me.

    I created my CSV file again using different code and also I removed the Rupee sign from Amounts. So then it worked fine.

    One more problem I am facing is, sometimes I am getting 'Invalid Argument error' at below line of code from the BulkImport Coding Function.

                     //</snippetImportWithCreate2>
                    // BulkImportHelper.WaitForAsyncJobCompletion(_serviceProxy, parseImportResponse.AsyncOperationId);
                    // BulkImportHelper.ReportErrors(_serviceProxy, importFileId);
    
                    //<snippetImportWithCreate3>
                    // Retrieve the first two distinct values for column 1 from the parse table.
                    // NOTE: You must create the parse table first using the ParseImport message.
                    // The parse table is not accessible after ImportRecordsImportResponse is called.
                    GetDistinctValuesImportFileRequest distinctValuesRequest = new GetDistinctValuesImportFileRequest()
                    {
                        columnNumber = 1,
                        ImportFileId = importFileId,
                        pageNumber = 1,
                        recordsPerPage = 2,
                    };
                    GetDistinctValuesImportFileResponse distinctValuesResponse =
                        (GetDistinctValuesImportFileResponse)_serviceProxy.Execute(distinctValuesRequest);

    When I debug through this code, I see error comes when getting this Above Response using CRM service.

    This doesn't happen always. But yes most of the time.

    When I check Stacktrace, it says something about exception at Runtime Assemblies. 

    Any configuration Setting I am missing on Service Level in App.Config ? Any idea about this ?

    Thank you for your help. :)

    Thursday, March 20, 2014 10:11 AM
  • At the time of error test your import file.

    This error mainly occur when file is blank.


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    Thursday, March 20, 2014 11:13 AM