Is it a multiselect optionset? Nope… it’s an N:N lookup

By | January 30, 2020

If you ever wanted to have your own multiselect optionset which would be utilizing an N:N relationship behind the scene, here you go:

ntonmultiselect

It works and behaves similarly to the out-of-the-box multiselect optionset, but it’s not an option set. It’s a custom PCF control that’s relying on the N:N relationship to display those dropdown values and to store the selections.

Turned out it was not even that difficult to build this – all that was needed is to combine Select2 with PCF

The sources (and the solution file) are on github: https://github.com/ashlega/ITAintBoring.PCFControls

This is the first version, so it might not be “final”, but, so far, here is how this control is supposed to be configured:

  • You can use it for any single line text control
  • There are a few properties to set:
  • image

Linked Entity Name: “another” side of the N:N

Linked Entity Name Attribute: usually, it would be “name” attribute of the linked entity

Linked Entity ID Attribute: and this is the “Id” attribute

Relationship Name: this is the name of the N:N relationship (from the N:N properties page)

Relationship Entity Name: this is the name of the N:N relationship entity name (from the N:N properties page)

Some of those properties could probably be retrieved through the metadata requests, but, for now, you’ll just need to set them manually when configuring the control.

PS. There is more to it now (Jan 31): https://www.itaintboring.com/dynamics-crm/nn-lookup-on-the-new-record-form-lets-do-it/

32 thoughts on “Is it a multiselect optionset? Nope… it’s an N:N lookup

  1. Pasha Pilgrim

    Alex, what would be the scenarios to use it instead of the out of the box multiselect option set?

    Reply
    1. Joergen

      One of the first scenarios I think about using n:n lookup instead of option sets – if your list of items can change over time – the N:N lookup may be the correct choice for you. Then you can add items and you can deactivate items without loosing historical data and without having an “ugly” list of items. An option set will be OK, when you have a static list of items without many changes.

      In addition, the N:N relationship can also store additional information, e.g. when you have added a specific item as you can see in the video above.

      Reply
      1. Alex Shlega Post author

        That’s exactly it. Although, I still need to do some work on the component to make it work with active/inactive/historical/etc (and, possibly, with “filters” in general). Besides, multiselect optionset has some limitations (I think we can’t work with those fields in SSRS, for example). Although, N:N may have some limitations, too (workflow triggering, for instance).

        Reply
  2. Raghu

    I’m afraid to say that Both the solutions fail to import. I’ve spent significant amount of time to rebuild the library but with no luck as I don’t know typescript works. Would you be able to just give us the NToNMultiselect control and the plugin solution pls. thanks in advance.

    Reply
  3. Malcolm

    I have tried to install the solution but I get an alert saying the ita_checkitem entity is needed what am I doing wrong?

    Reply
  4. Joanna

    Hi Alex,

    Just started using this control, however I’m experiencing a significant lag when the data set is too large. I linked this control with the CRM account entity which has 10k+ records. Users have to wait for a really long time upon clicking the control, or upon typing on the control. The lag creates the impression that the browser has crashed.

    Any ideas how to improve this? Would it be possible to not load all records in the beginning, and only conduct the webapi retrieve only on demand, i.e. 3 seconds after user stops entering the search word?

    Thanks so much!

    Reply
  5. Bas

    Hi Alex,

    Great post!
    I used an XML webresource with a Fetch to filter the records. That works great however, is it also possible to filter the records dynamically (on the fly)?
    I want to only get records which belong to related account and with a specific customertypecode.
    Thanks!

    Reply
    1. Bas

      I managed to create a workaround: I created a plugin which fires on the Retrieve message (i used the context.depth to prevent a loop).
      The plugin creates a fetch, based on the fieldvalues of the record.
      The XML webresource, which contains the (static) fetch for filtering the PCF field, will be updated with the calculated new fetch and published.
      For me, this works but it’s not 100% save because if multiple users open records simultaneously or if a user opens more than one record for instance in multiple tabs): The filter of the last opened record is leading.

      Reply
      1. Alex Shlega Post author

        Hi Bas, I ran into exactly this problem just recently. What if you could parameterize FetchXml with an attribute value from the current record (by adding something like #typecode# to the fetch… and, then, that tag would be replaced with the actual value)?

        Reply
        1. Tyler Sand

          It’s not elegant, but when I was customizing this control last week to use it for bridge entities, I added two additional parameters to specify the filter fields for this entity (the one with the control) and the foreign entity that I was linking to. Then all I have to do is put in the two field names, such as ts_producttype and ts_typeid to filter. The example below shows my change, which assumes that the related fields are entity references.

          if(thisFilterField != null && foreignFilterField != null &&
          Xrm.Page.getAttribute(thisFilterField) != null && Xrm.Page.getAttribute(thisFilterField).getValue() != null)
          {
          this.contextObj.webAPI.retrieveMultipleRecords(
          (this.contextObj).parameters.foreignEntityName.raw,
          “?$filter=” + foreignFilterField + ” eq ” + Xrm.Page.getAttribute(thisFilterField).getValue()[0].id +
          “&$orderby=” + (this.contextObj).parameters.foreignEntityDisplayNameAttribute.raw + ” asc”, 5000
          ).then(this._successCallback, this.errorCallback);
          }
          else
          this.contextObj.webAPI.retrieveMultipleRecords(
          (this.contextObj).parameters.foreignEntityName.raw,
          “?$orderby=” + (this.contextObj).parameters.foreignEntityDisplayNameAttribute.raw + ” asc”, 5000
          ).then(this._successCallback, this.errorCallback);

          Reply
  6. Tyler Sand

    Alex, thanks for building and releasing this! I customized it last week to use for a custom bridge entity. It was a quick hack job, but if you are interested I’m happy to share my code, and perhaps it is something that we could more cleanly integrate into the solution so people can use real N:N or custom bridge entities (1:N + N:1). Let me know if you are interested!

    Reply
    1. CEPJC

      Hi Tyler, I would be super interested to try your customized version. It is exactly what I need. I just posted below about it. Would it be possible for you to share the .zip of the solution to try it out/use it?

      Best, CEPJC

      Reply
  7. Brandon Franke

    This is awesome, but Is there anyway to actually store the values in the field as a delimited list?

    Reply
  8. Vivian Voss

    Hi,
    Just found this today and it sounds great! I would although also use the values in advanced find. How could that be done?

    Reply
  9. Anonymous

    Hi Alex,
    Great to see this control
    However I’m getting Bad Request – Error in query syntax, while removing any
    and all the records are there in textbox and i need to have only selected records.
    Thankyou.

    Reply
    1. Alex Shlega Post author

      Not really. There is an oob multiselect, but that’s an optionset. In this case, it’s also a multiselect, but it works off the relationship.

      Reply
  10. CEPJC

    Hi Axel

    Thank you for this contribution! It is a great PFC control/component!! It works without any issue with an ‘native’ N:N relationship.

    Would it be possible to have something like this but for a ‘Manually’ N:N relationship, via creating a custom entity? I mean by using a manually created entity with lookups to Entity A and Entity B of the N:N relationship. I tried, but it did not worked for me.
    *The reason I use a ‘manual’ N:N relationship via a custom entity is that, the ‘native’ N:N relationship does not expose the relationship entity and I can’t access the information of the N:N relationship, for example to create charts -or- even connect via PowerBI.

    My use case is adding ‘Keywords’ to ‘Account’ records. N:Keywords-to-N:Accounts.

    Your support would be highly appreciated!

    Best
    CEPJC

    Reply
    1. Donal McCarthy

      Hey CEPJC – did you manage to get this working with a manual N:N?

      Reply
      1. CEPJC

        I did not. It seems Tyler Sand already developed the customization to make it work. It would be amazing if he shares or published the PCF Control (@ https://pcf.gallery/).

        Tyler, would you be up for that??

        Reply
  11. Donal McCarthy

    Alex,
    Would you be able to point me at an example of the code I should use for the FetchXML Web Resource?
    I tried few things and I’m not getting anywhere.
    I have added the field to an entity that has an N:N with System Users.
    I am trying to limit the field to return users that have Yes selected in a Yes/No field in the user table.
    Thanks

    Reply
  12. Antonio Lozada

    Has anybody tried this control in the Dynamics Outlook App? It renders correctly but it fails to save/edit/delete records in the N:N.

    Reply
  13. Rusty Cisney

    This is some pretty nifty work; I’d love to know how to “clear” the selected N:N values the next time the form loads.

    Reply
  14. Rusty Cisney

    With the N:NMultiselect, I’d like to be able to “clear” the text field the next time the form is opened, rather than have the values persist (I’m actually firing a plugin on the ‘Associate/Disassociate’ messages to write records in a separate entity, so I’d like to have a “clean” field the next time the page loads… is that possible?

    Reply
  15. Thomas

    I will gvie this a try. We will use Dataverse API to write new “multiselect” values to a row. Will the values written via the API show up in the form?

    Reply
  16. Dynamics

    I have tried to use this PCF, but once save and after coming back to same Record the value doesn’t appear on the text box. While in database value do appear. So can you please help me in resolving this.

    Reply
  17. Zeph Thabiso Lekota

    Hello Alex,

    This functionality is awesome I have tested it on the sandbox.
    The only thing I noticed is that when you refresh the form/onload the lookup field will be cleared/empty although the N: N relationships have already created associated records in the background.

    My question is can this multi-select lookup field always have the N: N records in all form states and can business logic be applied to this field to get a collection of N: N records.

    That is just my question.

    Kind regards,
    Zeph

    Reply

Leave a Reply to CEPJC Cancel reply

Your email address will not be published. Required fields are marked *