Import and Delete problem due to Primary key

  • 29Views
  • Last Post 28 January 2019
  • Topic Is Solved
0
votes
Eva Patrick posted this 24 January 2019

Hi there,

I have a csv file that I would like to import into Dynamics 365. I would like to set a task to delete previous data and upload new data everyday. The problem I have is that the delete task wants me to map the dynamics id with my csv id. I of course do not know the dynamics id so I am unable to match it with any fields in my csv file. 

Is there a way to select the dynamics id that skyvia can use so that I can map it to the id I have in my csv file?

 

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 24 January 2019

Yes, DELETE operation is performed based on the ID/primary key field. If you do not have corresponding ID values in your CSV file, you can perform DELETE even without knowing them. For this, Lookup Mapping can be used. In this case you can get the IDs or PK values from the target object itself by some other field that uniquely identifies a record. To use the Lookup mapping, you need to have a column (or a set of columns) that uniquely identifies a record. Refer to: https://skyvia.com/resources/docs/index.html?performing_update_and_delete.htm

https://skyvia.com/resources/docs/index.html?lookup_mapping.htm

 

If you encounter any issues with this or have any further questions, feel free to contact us. We will be glad to answer all your further questions.

 

0
votes
Eva Patrick posted this 24 January 2019

Thank you for your quick response. I am still struggling to map the fields. Here is the situation. I have gone ahead and used the Lookup mapping. I have a csv file with a unique column called ce_seller which is the seller ID. In dynamics I have the same field in my entitiy and I also have a foreign key in my contact entity called ce_seller.

When i select target lookup and select the dynamic's entity (merchantaccount), on the results column I need to select the ce_seller column but when i do i get an invalid type error. 

I think the problem lies here. If you can explain to me the following;

1. on the target lookup; this needs to be an entity in dynamics?

2. on the results column what am I expected to put here? 

 

Looking forward to your quick response.

 

Regards

 

Patrick

1
votes
Mariia Zaharova posted this 25 January 2019

1. on the target lookup; this needs to be an entity in dynamics?

Yes. This is a target object to get the value from.

 

 2. on the results column what am I expected to put here? 

This is a column from the Lookup Object (the above one) from which we get the result value that will be assigned to the mapped target column.

 

For example: you want to delete some records from your 'merchantaccount' entity, you have 'ce_seller' column in CSV file and corresponding 'ce_seller_Dynamics' column in your 'merchantaccount' entity. Lookup will look like this:

Lookup Object: merchantaccount

Result Column: merchantaccount_id

Lookup Key Column: ce_seller

Column: ce_seller_Dynamics

 

This lookup will try to find record in the 'merchantaccount' entity and return its Id where column 'ce_seller' equals 'ce_seller_Dynamics'. If record is found Skyvia will delete record with the corresponding Id from the 'merchantaccount' entity. If record is not found, you will get the "No record found..." error.

The "invalid data type" error means that Lookup Key Column and CSV column have different data types, e.g. string and number. Currently, there is no way to use Expression mapping in Lookup and it is impossible to convert data types. You can either try changing CSV column data type at first step of Task Editor ( http://prntscr.com/mbz8bq ) or use another set of columns (with compatible data types). 

 

Best regards,

Mariia

  • Supported by
  • Eva Patrick
0
votes
Eva Patrick posted this 25 January 2019

Thank you Marria, that has helped me a great deal. However, I now get an error shown below.

 

An exception has occurred during delete operation, the message returned from the provider is: ExecuteMultiple Request batch size exceeds the maximum batch size allowed!

 

Do you know what I can do to eliminate the error? I only have 2400 records on the csv file and I am trying to delete whatever matches the 2400 in dynamics. Dynamics has about 2000 records.

 

Regards

 

Patrick

 

0
votes
Eva Patrick posted this 25 January 2019

Hi I think i know the problem. Its the batch fuile size. Crm is 1000. How can i increase this in dynamics?

 

Regards

 

Patrick

1
votes
Mariia Zaharova posted this 28 January 2019

Hi Patrick!

Batch size can be changed in your Dynamics CRM connection: open your connection, click Edit and then click Advanced button:

  • Supported by
  • Eva Patrick
0
votes
Eva Patrick posted this 28 January 2019

Thank you that worked a treat.

 

Regards

 

Patrick

Close