Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

How to populate an N:N with data?

  • 1.  How to populate an N:N with data?

    TOP CONTRIBUTOR
    Posted Dec 09, 2020 09:12 PM
    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
    ------------------------------
    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 2.  RE: How to populate an N:N with data?

    TOP CONTRIBUTOR
    Posted Dec 10, 2020 03:59 AM
    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
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 3.  RE: How to populate an N:N with data?

    TOP CONTRIBUTOR
    Posted Dec 10, 2020 10:16 AM
    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
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 4.  RE: How to populate an N:N with data?

    TOP CONTRIBUTOR
    Posted Dec 10, 2020 10:33 AM
    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
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 5.  RE: How to populate an N:N with data?

    TOP CONTRIBUTOR
    Posted Dec 10, 2020 11:19 AM
    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
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 6.  RE: How to populate an N:N with data?

    TOP CONTRIBUTOR
    Posted Dec 10, 2020 11:32 AM
    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
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 7.  RE: How to populate an N:N with data?

    TOP CONTRIBUTOR
    Posted Dec 14, 2020 11:56 AM
    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
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 8.  RE: How to populate an N:N with data?

    TOP CONTRIBUTOR
    Posted Dec 14, 2020 12:05 PM
    That's great, glad it's working for you!

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

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 9.  RE: How to populate an N:N with data?

    Posted Dec 16, 2020 10:16 AM
    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
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 10.  RE: How to populate an N:N with data?

    TOP CONTRIBUTOR
    Posted Dec 16, 2020 11:13 AM
    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
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 11.  RE: How to populate an N:N with data?

    Posted Dec 16, 2020 04:58 PM

    Hi Mark,

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

    Danielle



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

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


If you've found this thread useful, dive deeper into User Group community content by role