Customer experience | Sales, Customer Insights,...

Import to update or create account records having self join fields

(0) Share

Report

Posted on by Vinay Hosamane

By default in Account entity, accountid and parentaccountid are two fields which is self join. I have a custom field clientid. I need help in 2 things.

1. I want to setup clientid (custom field) be a self joining field with parentclientid. This is a parent child relationship in accounts. So need help how I can setup this scenario. I looked up accountid and was not able to figure out. This is similar to employee and manager relationship in an employee table.

2. Now that I have clientid and parentclientid, i want to import data to create or update existing client records. When working with Account, for the Parent Account, I suppied Account Name. But name could be duplicate, I am having hard time associating 2 fields in excel/csv to map to a single field (parentaccountid) in Accounts. Can you please help or provide me directions to resolve my 2 issues?

(Combination of Client Name and Client ID is unique, because I could have similar client names. Where I want to associate the Parent, I need a combination of Client Name and Client ID which makes it unique.) How can I achieve that when I am importing for creating new accounts or updating?

I have the same question (0)

All responses (9)

Answers (0)

Aditya 260 on at

Like (0)

Report

RE: Import to update or create account records having self join fields

First of all in order to create the self join for the custom entity say Client where Clientid is the primary key, you will have to set the lookup of client as parentclient.

For importing, you must ensure that the client records which comes under parentclient column must be imported before (or present in CRM before) the import of all other client records.

Was this reply helpful? Yes No
Vinay Hosamane 90 on at

Like (0)

Report

RE: Import to update or create account records having self join fields

Thanks Aditya.

In Account entity in Dynamics 365, there is a default fields "accountId" and "parentaccountid". Similarly I want to create a new field called clientid and parentclientid. I want the relationship to be very much similar to that of accountid and parentaccountid.

Q1. How do I create clientid and parentclientid to function similar to that of accountid and parentaccountid?
(I was unable to make the 1:N relationship between clientid and parentclientid when i went into mappings for 1:N)

Q2. When importing how can I map two fields which are in excel/csv to a parentclientid? In the display for parentclientid, I want the Name of the Client to show rather than the id.

Any help is greatly appreciated. Thanks again.

Was this reply helpful? Yes No
LuHao 40,890 on at

Like (0)

Report

RE: Import to update or create account records having self join fields

Hi Vinay,

There are two situations, which one are you?

1) An Account corresponds to a Client and a ParentClient. In this case, both clientid and parentclientid are on the Account form.

2) An Account corresponds to a Client and its ParentAccount corresponds to ParentClient. In this case, only one clientid is on the Account form.

Was this reply helpful? Yes No
Vinay Hosamane 90 on at

Like (0)

Report

RE: Import to update or create account records having self join fields

Hello Hao,

I am in situation #1, both clientid and parentclientid are on the Account form. clientid is an internal 5 digit number which is unique. So I am not using the default accountid which is an auto generated guid.

Thanks,

Vinay

Was this reply helpful? Yes No
Suggested answer

LuHao 40,890 on at

Like (0)

Report

RE: Import to update or create account records having self join fields

Hi Vinay,

Are fields clientid and parentclientid the lookup fields?

If so, we can use a Quick View Form to show the parentclientid value on the Account form.

First of all, there is a 1:N relationship between Client and Client, it is in the Parental behavior.

Then new a quick view form for Client entity, add the Parent Client to the quick view form. Save and publish.



Open the Account form editor, insert the qucik view form, select the Client quick view form. Save and publish.



You can also add the fields Client and its ParentClient to the Account view, publish all customizations. This makes it easy to import the Parent Client of the Account.



In this way, we can see Client and its ParentClient on the Account form and view.



Was this reply helpful? Yes No
LuHao 40,890 on at

Like (0)

Report

RE: Import to update or create account records having self join fields

Hi Vinay,

If fields clientid and parentclientid are not the lookup fields and they are Whole Number fields, the Account still need a lookup field of Client to relate the Account and the Client.

Then create a calculated field for Client to show its parentclientid, add clientid and the calculated field to the Client Quick View Form, insert the Client Quick View Form to the Account form.

Was this reply helpful? Yes No
Vinay Hosamane 90 on at

Like (0)

Report

RE: Import to update or create account records having self join fields

Hello Lu Hao,

I was able to create Client entity and build a Quick View form. But I was not able to proceed further than ...Open the Account form editor, insert the qucik view form, select the Client quick view form. When I went to Account form and click on Quick View, I was not able to get the Client entity show up in the drop-down. Do you know what I have done wrong and why my client entity is not showing up? I did save and publish as you have mentioned.

I really appreciate your detailed explanation.

Thanks,
Vinay

Was this reply helpful? Yes No
LuHao 40,890 on at

Like (0)

Report

RE: Import to update or create account records having self join fields

Hi Vinay,

1. When I went to Account form and click on Quick View, I was not able to get the Client entity show up in the drop-down.

- This is because there is no 1:N relationship between Client entity and Account entity.

Please note that for Client and Account to be associated, we have to create a relationship for them.

2. clientid is an internal 5 digit number which is unique.

- This means that clientid is not a lookup field, but a Whole Number field, so how do you connect Client and Account?

3, In general, we associate a Client record with an Account record through the Client's lookup field, and then we can use the Client's Quick View Form to display the Client's fields, including clientid and parentclientid.

Therefore, we should first create a Client lookup field for the Account entity.

In this case, we can select the Client on the Account form through the Client lookup field, and then the Quick View Form will automatically display this Client's fields (clientid and parentclientid).

Was this reply helpful? Yes No
Vinay Hosamane 90 on at

Like (0)

Report

RE: Import to update or create account records having self join fields

Hello Hao,

I may have explained the core issue incorrectly here. So I have tried to explain in more clearly in my other question.

Please ref. community.dynamics.com/.../account-entity-duplicate-of-current-record-already-exists

I believe I may have explained my core issue correctly there.

Thanks in advance.

Regards,

Vinay

Was this reply helpful? Yes No