Relationship Mappings: String Length Too Long

Relationship mappings are a great feature in Dataverse. If you haven’t seen them before you’re probably not alone – the interface to edit them is pretty well hidden away in the classic UI.

These mappings let you create related records with fields already filled out based on the parent record. Think creating a new contact on an account – it will automatically fill out the address of the contact as a copy of the account address. Or creating an order from a quote, or an account/contact/opportunity from a lead, …

I recently came across an error trying to use the “Create Order” button on a quote:

OK, so it looks like one of the values that the relationship mappings are trying to copy into the order is too long. But which one? There’s no helpful field name in the error message.

Reading the mappings

To find out where this is coming from, I need to be able to find all the mappings between the quote and salesorder entities and compare the metadata for the mapped attributes. The maximum length defined in the metadata should be the same in both tables. So the first question is, where are the mappings stored?

It turns out there are two entities involved here. The first, entitymap, stores the fact that there is some sort of mapping between two entities in the sourceentityname and sourceattributename fields. The mapping isn’t specific to a particular relationship – if two entities have more than one relationship between them, both relationships will use the same mapping.

The second, attributemap, stores a list of the attributes within each entity that should be mapped in the sourceattributename and targetattributename fields.

I can get a list of the mappings between the quote and order entities with a query like:

SELECT attributemap.sourceattributename,
       attributemap.targetattributename
FROM   entitymap
       INNER JOIN
       attributemap
       ON entitymap.entitymapid = attributemap.entitymapid
WHERE  entitymap.sourceentityname = 'quote'
       AND entitymap.targetentityname = 'salesorder'

or

<fetch xmlns:generator='MarkMpn.SQL4CDS'>
  <entity name='attributemap'>
    <attribute name='sourceattributename' />
    <attribute name='targetattributename' />
    <link-entity name='entitymap' to='entitymapid' from='entitymapid' alias='entitymap' link-type='inner' />
    <filter>
      <condition attribute='sourceentityname' entityname='entitymap' operator='eq' value='quote' />
      <condition attribute='targetentityname' entityname='entitymap' operator='eq' value='salesorder' />
    </filter>
  </entity>
</fetch>

Of course, I could have got this visually by opening up the relationship mapping in the classic UI, but now I can get it via a query I can quickly find the mappings across my entire instance just by getting rid of the filters.

Getting the associated metadata

Now I can see what the mapped fields are, I need to get the metadata for those fields to see which one lets me store a longer value in the quote than it does in the order. Luckily in SQL 4 CDS I can add a join onto the metadata too, so my original query now becomes:

SELECT src.entitylogicalname,
       src.logicalname,
       src.maxlength,
       dst.entitylogicalname,
       dst.logicalname,
       dst.maxlength
FROM   entitymap
       INNER JOIN
       attributemap
       ON entitymap.entitymapid = attributemap.entitymapid
       INNER JOIN
       metadata.attribute AS src
       ON entitymap.sourceentityname = src.entitylogicalname
          AND attributemap.sourceattributename = src.logicalname
       INNER JOIN
       metadata.attribute AS dst
       ON entitymap.targetentityname = dst.entitylogicalname
          AND attributemap.targetattributename = dst.logicalname
WHERE  src.maxlength > dst.maxlength
       AND src.attributeof IS NULL
       AND dst.attributeof IS NULL

I’ve removed my filter that limited my results to the quote -> order mapping so I can see if there are any other affected mappings in my instance, and added a filter to find only the mappings where the maximum length of the source attribute is longer than the target attribute. I’ve also added another two filters to remove virtual attributes which I can’t control.

This takes a few seconds to run and produces the results:

entitylogicalname	logicalname	maxlength	entitylogicalname	logicalname	maxlength
systemuser	lastname	256	externalparty	lastname	64
systemuser	firstname	256	externalparty	firstname	64
quote	data8_specificationfilename	200	salesorder	data8_specificationfilename	100

There seems to be an OOB mapping for name details between systemuser and externalparty which I’m not particularly interested in, and one between quote and salesorder which I definitely am!

This field can hold 200 characters in the quote but only 100 in the order, which would lead to the error I was seeing.

Now I know where I’m looking it’s a simple fix to go and increase the maximum length of the field on the order table!