Products and attachments in D365 Enterprise

Some newbies in the Dynamics world struggle to clearly understand the difference between products, product masters, product variants and released products and how these are saved in the data model. This post attempts to explain these differences.

The Product Information Management (PIM) module shows a list of menu items with different list pages to see products. Some are actually using the same list page EcoResProductListPage with different queries because they use the same tables.

The following table summarizes the main differences. Important to note that the main product information (not company based) is stored in EcoResProduct. Release products are in the InventTable per company (DataAreaId).

Products Menu Item

Description

Per Company

Data Model

All products and product masters

Distinct and master products

No

EcoResProduct where field ‘INSTANCERELATIONTYPE’ = DictTable::getRelationTypeFromTableName(tableStr(EcoResProductMaster)) OR DictTable::getRelationTypeFromTableName(tableStr(EcoResDistinctProduct))

Products

Distinct products that have no variants

No

EcoResProduct where field ‘INSTANCERELATIONTYPE’ = DictTable::getRelationTypeFromTableName(tableStr(EcoResDistinctProduct))

Product masters

Master products that have variants e.g. Women top (colour, size, style)

No

EcoResProduct where field ‘INSTANCERELATIONTYPE’ = DictTable::getRelationTypeFromTableName(tableStr(EcoResProductMaster))

Product variants

The different variants of the Product Master e.g. Women top Blue Medium

No

EcoResProduct where ProductMaster field is not empty

Released products

Released products per company (legal entity)

Yes

InventTable

You can see the actual query ranges when opening the interaction class of the EcoResProductListPage list page. The field “InstanceRelationType” is filtered to distinguish between distinct and master products.

EcoResProductMaster, EcoResDistinctProduct and EcoResDistinctProductVariant are all extending the EcoResProduct table. Therefore if you had to look up for this information directly from SQL in a development environment you will find all the fields in the EcoResProduct table as shown in the example below. Note the differences in the ProductMaster and InstanceRelationType fields/columns. I added the first column Product_Type to make it more clear.

File attachments

I included this section because its interesting to see how attachments are linked to Products using the DocuRef table. The DocuRef table uses the fields RefRecId and RefTableId to link to the respective product table with the attachment (file, image etc) . In the example below you can see the end result after uploading the same file ‘Speaker010720.jpg‘ to different product types.

Note that images were attached whilst the user was in the USMF company so all images have the ActualCompanyId field as ‘USMF’, but only the released product and released product variant have ‘USMF’ in the RefCompanyId field. The product master and distinct product both have ‘DAT’ company since they are not saved per legal entity.