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
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
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.
attributemap, stores a list of the attributes within each entity that should be mapped in the
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'
<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:
There seems to be an OOB mapping for name details between
externalparty which I’m not particularly interested in, and one between
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!