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/

21 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

Leave a Reply

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