Customer Engagement & Dynamics CRM Forum

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

You can use my SQL 4 CDS tool in XrmToolBox to do this. If your custom entity is new_customentity with a lookup field of new_accountid, and your new N:N intersect entity is new_account_new_customentity, you can run:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity

This will extract the existing values from your lookup field and insert them into the N:N relationship.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester
------------------------------

Original Message:
Sent: Dec 09, 2020 09:12 PM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

Mark,

This is awesome and I've got it working, the issue is it runs for one record then i get "Object reference not set to an instance of an object."  If i exclude that ID and run it again, it inserts 1 record then i get the object reference error again.

Is there something i'm doing wrong?

Thank you for help,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

Original Message:
Sent: Dec 10, 2020 03:59 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

You can use my SQL 4 CDS tool in XrmToolBox to do this. If your custom entity is new_customentity with a lookup field of new_accountid, and your new N:N intersect entity is new_account_new_customentity, you can run:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity

This will extract the existing values from your lookup field and insert them into the N:N relationship.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 09, 2020 09:12 PM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

I haven't seen that before, but my best guess is it might be due to records that don't have the lookup field populated. Try changing my example to:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity
WHERE new_accountid IS NOT NULL

Let me know how you get on!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester
------------------------------

Original Message:
Sent: Dec 10, 2020 10:16 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

This is awesome and I've got it working, the issue is it runs for one record then i get "Object reference not set to an instance of an object."  If i exclude that ID and run it again, it inserts 1 record then i get the object reference error again.

Is there something i'm doing wrong?

Thank you for help,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 03:59 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

You can use my SQL 4 CDS tool in XrmToolBox to do this. If your custom entity is new_customentity with a lookup field of new_accountid, and your new N:N intersect entity is new_account_new_customentity, you can run:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity

This will extract the existing values from your lookup field and insert them into the N:N relationship.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 09, 2020 09:12 PM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

Mark,

Same error.  There was one record with a null new_accountid that was excluded on the select, but that did not change the result.  Worst case is I copy out the 2000 key set values and create individual insert statements, that is still way faster than manually updating the records through the UI.

If you have something else you want me to try, please let me know.

Thanks again for all your help,


------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

Original Message:
Sent: Dec 10, 2020 10:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

I haven't seen that before, but my best guess is it might be due to records that don't have the lookup field populated. Try changing my example to:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity
WHERE new_accountid IS NOT NULL

Let me know how you get on!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 10:16 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

This is awesome and I've got it working, the issue is it runs for one record then i get "Object reference not set to an instance of an object."  If i exclude that ID and run it again, it inserts 1 record then i get the object reference error again.

Is there something i'm doing wrong?

Thank you for help,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 03:59 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

You can use my SQL 4 CDS tool in XrmToolBox to do this. If your custom entity is new_customentity with a lookup field of new_accountid, and your new N:N intersect entity is new_account_new_customentity, you can run:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity

This will extract the existing values from your lookup field and insert them into the N:N relationship.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 09, 2020 09:12 PM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

Could you send me the exact SQL you're running please? If there is an error there I'd like to get it fixed for anyone else using it too.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester
------------------------------

Original Message:
Sent: Dec 10, 2020 11:19 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

Same error.  There was one record with a null new_accountid that was excluded on the select, but that did not change the result.  Worst case is I copy out the 2000 key set values and create individual insert statements, that is still way faster than manually updating the records through the UI.

If you have something else you want me to try, please let me know.

Thanks again for all your help,


------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 10:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

I haven't seen that before, but my best guess is it might be due to records that don't have the lookup field populated. Try changing my example to:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity
WHERE new_accountid IS NOT NULL

Let me know how you get on!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 10:16 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

This is awesome and I've got it working, the issue is it runs for one record then i get "Object reference not set to an instance of an object."  If i exclude that ID and run it again, it inserts 1 record then i get the object reference error again.

Is there something i'm doing wrong?

Thank you for help,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 03:59 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

You can use my SQL 4 CDS tool in XrmToolBox to do this. If your custom entity is new_customentity with a lookup field of new_accountid, and your new N:N intersect entity is new_account_new_customentity, you can run:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity

This will extract the existing values from your lookup field and insert them into the N:N relationship.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 09, 2020 09:12 PM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

Mark,

v3.1.0 Works Great! Thank you!  This is going to save a ton of time.

Thanks again,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

Original Message:
Sent: Dec 10, 2020 11:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

Could you send me the exact SQL you're running please? If there is an error there I'd like to get it fixed for anyone else using it too.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 11:19 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

Same error.  There was one record with a null new_accountid that was excluded on the select, but that did not change the result.  Worst case is I copy out the 2000 key set values and create individual insert statements, that is still way faster than manually updating the records through the UI.

If you have something else you want me to try, please let me know.

Thanks again for all your help,


------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 10:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

I haven't seen that before, but my best guess is it might be due to records that don't have the lookup field populated. Try changing my example to:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity
WHERE new_accountid IS NOT NULL

Let me know how you get on!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 10:16 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

This is awesome and I've got it working, the issue is it runs for one record then i get "Object reference not set to an instance of an object."  If i exclude that ID and run it again, it inserts 1 record then i get the object reference error again.

Is there something i'm doing wrong?

Thank you for help,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 03:59 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

You can use my SQL 4 CDS tool in XrmToolBox to do this. If your custom entity is new_customentity with a lookup field of new_accountid, and your new N:N intersect entity is new_account_new_customentity, you can run:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity

This will extract the existing values from your lookup field and insert them into the N:N relationship.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 09, 2020 09:12 PM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

That's great, glad it's working for you!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester
------------------------------

Original Message:
Sent: Dec 14, 2020 11:55 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

v3.1.0 Works Great! Thank you!  This is going to save a ton of time.

Thanks again,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 11:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

Could you send me the exact SQL you're running please? If there is an error there I'd like to get it fixed for anyone else using it too.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 11:19 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

Same error.  There was one record with a null new_accountid that was excluded on the select, but that did not change the result.  Worst case is I copy out the 2000 key set values and create individual insert statements, that is still way faster than manually updating the records through the UI.

If you have something else you want me to try, please let me know.

Thanks again for all your help,


------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 10:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

I haven't seen that before, but my best guess is it might be due to records that don't have the lookup field populated. Try changing my example to:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity
WHERE new_accountid IS NOT NULL

Let me know how you get on!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 10:16 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

This is awesome and I've got it working, the issue is it runs for one record then i get "Object reference not set to an instance of an object."  If i exclude that ID and run it again, it inserts 1 record then i get the object reference error again.

Is there something i'm doing wrong?

Thank you for help,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 03:59 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

You can use my SQL 4 CDS tool in XrmToolBox to do this. If your custom entity is new_customentity with a lookup field of new_accountid, and your new N:N intersect entity is new_account_new_customentity, you can run:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity

This will extract the existing values from your lookup field and insert them into the N:N relationship.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 09, 2020 09:12 PM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

Hi Mark,
I just learnt this SQL 4 CDS from this post. What a wonderful tool, I wish I have known its existence before as I came from SQL background and I don't really like fetch XML. I think this tool is going to make my life so much easier. I know it might not have full SQL function support as I tried one scenario and it didn't work.

Scenario

I want to find all the contacts (contact) which have same emailaddress1, and remove emailaddress1 for the ones which do not have application (application custom entity).

SQL statement (haven't done it for a long time, maybe the statement may not be that efficient)

update contact set emailaddress1=''  where contactid not in (select contactid  from contact inner join   (select contactid, emailaddress1 from contact inner join application on contact.contactid=application.applicant) as appliants on contact.contact.emailaddress1=applicants.emailaddress1  where contact.id<>a.contact.id)

How can I accomplish the above with SQL 4 CDS? Thank you so much for your help.
Danielle

------------------------------
Danielle Wang
CMRAO
------------------------------

Original Message:
Sent: Dec 14, 2020 12:05 PM
From: Mark Carrington
Subject: How to populate an N:N with data?

That's great, glad it's working for you!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 14, 2020 11:55 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

v3.1.0 Works Great! Thank you!  This is going to save a ton of time.

Thanks again,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 11:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

Could you send me the exact SQL you're running please? If there is an error there I'd like to get it fixed for anyone else using it too.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 11:19 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

Same error.  There was one record with a null new_accountid that was excluded on the select, but that did not change the result.  Worst case is I copy out the 2000 key set values and create individual insert statements, that is still way faster than manually updating the records through the UI.

If you have something else you want me to try, please let me know.

Thanks again for all your help,


------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 10:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

I haven't seen that before, but my best guess is it might be due to records that don't have the lookup field populated. Try changing my example to:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity
WHERE new_accountid IS NOT NULL

Let me know how you get on!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 10:16 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

This is awesome and I've got it working, the issue is it runs for one record then i get "Object reference not set to an instance of an object."  If i exclude that ID and run it again, it inserts 1 record then i get the object reference error again.

Is there something i'm doing wrong?

Thank you for help,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 03:59 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

You can use my SQL 4 CDS tool in XrmToolBox to do this. If your custom entity is new_customentity with a lookup field of new_accountid, and your new N:N intersect entity is new_account_new_customentity, you can run:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity

This will extract the existing values from your lookup field and insert them into the N:N relationship.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 09, 2020 09:12 PM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

Hi Danielle,

Sub-queries are not supported in SQL 4 CDS. You can probably re-write your query to use a LEFT OUTER JOIN instead of the NOT IN:

UPDATE contact
SET emailaddress1 = NULL
FROM contact
LEFT OUTER JOIN
application
ON contact.contactid = application.applicant
INNER JOIN
contact AS applicant
ON contact.emailaddress1 = applicant.emailaddress1
INNER JOIN
application AS application2
ON applicant.contactid = application2.applicant
WHERE application.applicationid IS NULL;

This should find contacts that do not themselves have an application but do have the same email address as another contact that does have an application, and set the email address of the first contact to null.

HOWEVER!!! Please don't just run this (or any) query on your live data without testing!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester
------------------------------

Original Message:
Sent: Dec 16, 2020 10:16 AM
From: Danielle Wang
Subject: How to populate an N:N with data?

Hi Mark,
I just learnt this SQL 4 CDS from this post. What a wonderful tool, I wish I have known its existence before as I came from SQL background and I don't really like fetch XML. I think this tool is going to make my life so much easier. I know it might not have full SQL function support as I tried one scenario and it didn't work.

Scenario

I want to find all the contacts (contact) which have same emailaddress1, and remove emailaddress1 for the ones which do not have application (application custom entity).

SQL statement (haven't done it for a long time, maybe the statement may not be that efficient)

update contact set emailaddress1=''  where contactid not in (select contactid  from contact inner join   (select contactid, emailaddress1 from contact inner join application on contact.contactid=application.applicant) as appliants on contact.contact.emailaddress1=applicants.emailaddress1  where contact.id<>a.contact.id)

How can I accomplish the above with SQL 4 CDS? Thank you so much for your help.
Danielle

------------------------------
Danielle Wang
CMRAO

Original Message:
Sent: Dec 14, 2020 12:05 PM
From: Mark Carrington
Subject: How to populate an N:N with data?

That's great, glad it's working for you!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 14, 2020 11:55 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

v3.1.0 Works Great! Thank you!  This is going to save a ton of time.

Thanks again,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 11:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

Could you send me the exact SQL you're running please? If there is an error there I'd like to get it fixed for anyone else using it too.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 11:19 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

Same error.  There was one record with a null new_accountid that was excluded on the select, but that did not change the result.  Worst case is I copy out the 2000 key set values and create individual insert statements, that is still way faster than manually updating the records through the UI.

If you have something else you want me to try, please let me know.

Thanks again for all your help,


------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 10:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

I haven't seen that before, but my best guess is it might be due to records that don't have the lookup field populated. Try changing my example to:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity
WHERE new_accountid IS NOT NULL

Let me know how you get on!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 10:16 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

This is awesome and I've got it working, the issue is it runs for one record then i get "Object reference not set to an instance of an object."  If i exclude that ID and run it again, it inserts 1 record then i get the object reference error again.

Is there something i'm doing wrong?

Thank you for help,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 03:59 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

You can use my SQL 4 CDS tool in XrmToolBox to do this. If your custom entity is new_customentity with a lookup field of new_accountid, and your new N:N intersect entity is new_account_new_customentity, you can run:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity

This will extract the existing values from your lookup field and insert them into the N:N relationship.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 09, 2020 09:12 PM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------

Hi Mark,

It works like a charm, thank you so much Mark!!

Danielle

------------------------------
Danielle Wang
CMRAO
------------------------------

Original Message:
Sent: Dec 16, 2020 11:13 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

Hi Danielle,

Sub-queries are not supported in SQL 4 CDS. You can probably re-write your query to use a LEFT OUTER JOIN instead of the NOT IN:

UPDATE contact
SET emailaddress1 = NULL
FROM contact
LEFT OUTER JOIN
application
ON contact.contactid = application.applicant
INNER JOIN
contact AS applicant
ON contact.emailaddress1 = applicant.emailaddress1
INNER JOIN
application AS application2
ON applicant.contactid = application2.applicant
WHERE application.applicationid IS NULL;

This should find contacts that do not themselves have an application but do have the same email address as another contact that does have an application, and set the email address of the first contact to null.

HOWEVER!!! Please don't just run this (or any) query on your live data without testing!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 16, 2020 10:16 AM
From: Danielle Wang
Subject: How to populate an N:N with data?

Hi Mark,
I just learnt this SQL 4 CDS from this post. What a wonderful tool, I wish I have known its existence before as I came from SQL background and I don't really like fetch XML. I think this tool is going to make my life so much easier. I know it might not have full SQL function support as I tried one scenario and it didn't work.

Scenario

I want to find all the contacts (contact) which have same emailaddress1, and remove emailaddress1 for the ones which do not have application (application custom entity).

SQL statement (haven't done it for a long time, maybe the statement may not be that efficient)

update contact set emailaddress1=''  where contactid not in (select contactid  from contact inner join   (select contactid, emailaddress1 from contact inner join application on contact.contactid=application.applicant) as appliants on contact.contact.emailaddress1=applicants.emailaddress1  where contact.id<>a.contact.id)

How can I accomplish the above with SQL 4 CDS? Thank you so much for your help.
Danielle

------------------------------
Danielle Wang
CMRAO

Original Message:
Sent: Dec 14, 2020 12:05 PM
From: Mark Carrington
Subject: How to populate an N:N with data?

That's great, glad it's working for you!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 14, 2020 11:55 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

v3.1.0 Works Great! Thank you!  This is going to save a ton of time.

Thanks again,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 11:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

Could you send me the exact SQL you're running please? If there is an error there I'd like to get it fixed for anyone else using it too.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 11:19 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

Same error.  There was one record with a null new_accountid that was excluded on the select, but that did not change the result.  Worst case is I copy out the 2000 key set values and create individual insert statements, that is still way faster than manually updating the records through the UI.

If you have something else you want me to try, please let me know.

Thanks again for all your help,


------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 10:32 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

I haven't seen that before, but my best guess is it might be due to records that don't have the lookup field populated. Try changing my example to:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity
WHERE new_accountid IS NOT NULL

Let me know how you get on!

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 10, 2020 10:16 AM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Mark,

This is awesome and I've got it working, the issue is it runs for one record then i get "Object reference not set to an instance of an object."  If i exclude that ID and run it again, it inserts 1 record then i get the object reference error again.

Is there something i'm doing wrong?

Thank you for help,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA

Original Message:
Sent: Dec 10, 2020 03:59 AM
From: Mark Carrington
Subject: How to populate an N:N with data?

You can use my SQL 4 CDS tool in XrmToolBox to do this. If your custom entity is new_customentity with a lookup field of new_accountid, and your new N:N intersect entity is new_account_new_customentity, you can run:

INSERT INTO new_account_new_customentity (new_customentityid, accountid)
SELECT new_customentityid, new_accountid
FROM new_customentity

This will extract the existing values from your lookup field and insert them into the N:N relationship.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Dec 09, 2020 09:12 PM
From: Jim Corriveau
Subject: How to populate an N:N with data?

Hi,

I currently have a lookup from a custom entity to the Account entity, which has allowed the Users to associate a single custom entity record with any Account.  As time has passed and requirements have changed the Users need the ability to associate a single custom entity record with many Account records.

Not a big deal, I created an N:N between the entitles, added a sub grid to the custom entity, now the Users can associate as many Accounts as they need to the custom entity.  This is great for new records, but I'm stuck on how to bring the existing records forward into this new relationship.

I need to move or copy the associated Account in the 'old' single lookup field to the 'new' sub grid.  I've looked at workflows, msdyn workflow tools, xrm toolbox, export/import, and flow/automate and can't seem to find a way to take the Account value in the lookup and 'copy' it to the intersect entity/sub grid.

Has anyone been able to migrate data between these types of relationships?

Any help is appreciated.

Thanks you,

------------------------------
Jim Corriveau
A.W. Chesterton Company
Groveland MA
------------------------------