I am trying to create functionality where we copy all sorts of data from a chosen productvariant.
I am also trying to find the BOMVersions of a single productvariant, but I cannot see how it is queried in the application.
On the form EcoResProductVariantsPerCompany you can click the BOM button in the top of the screen, which will take you to the relevant BOMs for that productvariant.
The query for this screen seems to show that the correct data is obtained by INNER JOINING the productVariants to their inventDims to filter them based on the inventDims' product dimensions, which I was trying to reproduce in SQL:
select EcoResProductVariantDimensionValueConfig.CONFIGURATION , * from EcoResProduct
inner join inventDimCombination on InventDimCombination.DistinctProductVariant = EcoResProduct.RecId
left outer join EcoResProductVariantDimensionValue EcoResProductVariantDimensionValueConfig ON EcoResProduct.RecId = EcoResProductVariantDimensionValueConfig.DistinctProductVariant
AND ((EcoResProductVariantDimensionValueConfig.ProductDimensionAttribute = 5637144579))
left outer join ECORESCONFIGURATION ON ECORESCONFIGURATION.RecId = EcoResProductVariantDimensionValueConfig.CONFIGURATION
AND ((EcoResProductVariantDimensionValueConfig.ProductDimensionAttribute = 5637144579))
left outer join EcoResProductVariantDimensionValue EcoResProductVariantDimensionValueSize ON EcoResProduct.RecId = EcoResProductVariantDimensionValueSize.DistinctProductVariant
AND ((EcoResProductVariantDimensionValueSize.ProductDimensionAttribute = 5637144578))
left outer join EcoResProductVariantDimensionValue EcoResProductVariantDimensionValueColor ON EcoResProduct.RecId = EcoResProductVariantDimensionValueColor.DistinctProductVariant
AND ((EcoResProductVariantDimensionValueColor.ProductDimensionAttribute = 5637144576))
left outer join EcoResProductVariantDimensionValue EcoResProductVariantDimensionValueStyle ON EcoResProduct.RecId = EcoResProductVariantDimensionValueStyle.DistinctProductVariant
AND ((EcoResProductVariantDimensionValueStyle.ProductDimensionAttribute = 5637144580))
left outer join EcoResProductVariantDimensionValue EcoResProductVariantDimensionValueVersion ON EcoResProduct.RecId = EcoResProductVariantDimensionValueVersion.DistinctProductVariant
AND ((EcoResProductVariantDimensionValueVersion.ProductDimensionAttribute = 5637144577))
inner join InventDim
on inventDim.CONFIGID = ECORESCONFIGURATION.NAME
where ItemId = '10624-10' and 8375 = EcoResProduct.instancerelationtype
This is a sample of me trying to get the productvariants and their related BOMVersions from one itemId.
So far I keep getting wrong or too much data from every query that I try to create. And there must be an easier way to query this data in X++.
Does anyone know how I can obtain this data?
The microsoft query for the view (taken from the form when clicking BOMVersions from the EcoResProductVariantsPerCompany form):
SELECT FIRSTFAST FORUPDATE *
FROM BOMVersion(BOMVersion) USING INDEX ItemFromToIdx
WHERE ((PmfTypeId = 0))
AND InventDimCombination.ItemId = BOMVersion.ItemId
OUTER JOIN FORUPDATE PersonnelNumber
FROM HcmWorker(Ref_HcmWorker_HcmWorker) ON BOMVersion.Approver = HcmWorker.RecId
AND BOMVersion.Approver = HcmWorker.RecId
JOIN FORUPDATE *
FROM InventDim(InventDimBOMVersion) ON BOMVersion.InventDimId = InventDim.inventDimId
AND (
(configId = N'27-02-2019')
OR (configId = '')
)
AND (
(InventSizeId = '')
OR (InventSizeId = '')
)
AND (
(InventColorId = '')
OR (InventColorId = '')
)
AND (
(InventStyleId = '')
OR (InventStyleId = '')
)
AND (
(InventVersionId = N'B')
OR (InventVersionId = '')
)
