Hi,
I'm having problems adding a data source to the EcoResProductDetails form. When I add my custom view to the Data Sources, set the JoinSource to be "EcoResProduct" and LinkType to "OuterJoin" I am getting duplicate rows and SQL Trace is showing me that the generated SQL has a CROSS JOIN to my table, hence the duplicates.
My view is returning one row per Product (Product RecId, Attribute1, Attribute2).
Is there something missing in my View definition (or underlying Query) that prevents AX from knowing which field to use to join my view to EcoResProduct?
SELECT T1.PRODUCTMASTER,T1.RETAITOTALWEIGHT,T1.VARIANTCONFIGURATIONTECHNOLOGY,T1.RETAILCOLORGROUPID,T1.RETAILSIZEGROUPID,T1.RETAILSTYLEGROUPID,T1.INSTANCERELATIONTYPE,T1.DISPLAYPRODUCTNUMBER,T1.SEARCHNAME,T1.PRODUCTTYPE,T1.PDSCWPRODUCT,T1.MODIFIEDBY,T1.RECVERSION,T1.RELATIONTYPE,T1.PARTITION,T1.RECID,T2.PRODUCTDIMENSIONGROUP,T2.PRODUCT,T2.MODIFIEDBY,T2.RECVERSION,T2.PARTITION,T2.RECID,T3.PRODUCTMASTER,T3.ISVARIANTGENERATIONENABLED,T3.ISREUSECONFIGURATIONENABLED,T3.RECVERSION,T3.PARTITION,T3.RECID,T4.PRODUCT,T4.STORAGEDIMENSIONGROUP,T4.RECVERSION,T4.PARTITION,T4.RECID,T5.PRODUCT,T5.TRACKINGDIMENSIONGROUP,T5.RECVERSION,T5.PARTITION,T5.RECID,T6.DESCRIPTION,T6.NAME,T6.PRODUCT,T6.LANGUAGEID,T6.MODIFIEDBY,T6.RECVERSION,T6.PARTITION,T6.RECID,T7.HARMONIZEDCODE,T7.NMFCCODE,T7.PRODUCT,T7.STCCCODE,T7.RECVERSION,T7.PARTITION,T7.RECID,T8.OEM,T8.PRODUCTRANGE,T8.PARTITION,T8.RECID,T9.NAME,T9.RECVERSION,T9.RECID
FROM ECORESPRODUCT T1
LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUPPRODUCT T2 ON ((T2.PARTITION=@P1) AND (T1.RECID=T2.PRODUCT))
LEFT OUTER JOIN ECORESPRODUCTMASTERMODELINGPOLICY T3 ON ((T3.PARTITION=@P2) AND (T1.RECID=T3.PRODUCTMASTER))
LEFT OUTER JOIN ECORESSTORAGEDIMENSIONGROUPPRODUCT T4 ON ((T4.PARTITION=@P3) AND (T1.RECID=T4.PRODUCT))
LEFT OUTER JOIN ECORESTRACKINGDIMENSIONGROUPPRODUCT T5 ON ((T5.PARTITION=@P4) AND (T1.RECID=T5.PRODUCT))
LEFT OUTER JOIN ECORESPRODUCTTRANSLATION T6 ON ((T6.PARTITION=@P5) AND ((T6.LANGUAGEID=@P6) AND (T1.RECID=T6.PRODUCT)))
LEFT OUTER JOIN WHSECORESPRODUCTTRANSPORTATIONCODES T7 ON ((T7.PARTITION=@P7) AND (T1.RECID=T7.PRODUCT))
CROSS JOIN HYDPRODUCTATTRIBUTEVALUEVIEW T8
LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUP T9 ON ((T9.PARTITION=@P8) AND (T2.PRODUCTDIMENSIONGROUP=T9.RECID))
WHERE ((T1.PARTITION=@P9) AND ((T1.INSTANCERELATIONTYPE=@P10) OR (T1.INSTANCERELATIONTYPE=@P11)))
AND ((((((T8.PARTITION=@P12) AND ((T8.PARTITION#2=@P13) OR (T8.PARTITION#2 IS NULL))) AND ((T8.PARTITION#3=@P14) OR (T8.PARTITION#3 IS NULL))) AND ((T8.PARTITION#4=@P15) OR (T8.PARTITION#4 IS NULL))) AND ((T8.PARTITION#5=@P16) OR (T8.PARTITION#5 IS NULL))) AND ((T8.PARTITION#6=@P17) OR (T8.PARTITION#6 IS NULL))) AND EXISTS (SELECT 'x' FROM ECORESPRODUCTCATEGORY T10 WHERE ((T10.PARTITION=@P18) AND ((T10.CATEGORYHIERARCHY=@P19) AND (T10.PRODUCT=T1.RECID)))) ORDER BY T1.DISPLAYPRODUCTNUMBER OPTION(FAST 4)
SELECT T1.PRODUCTMASTER,T1.RETAITOTALWEIGHT,T1.VARIANTCONFIGURATIONTECHNOLOGY,T1.RETAILCOLORGROUPID,T1.RETAILSIZEGROUPID,T1.RETAILSTYLEGROUPID,T1.INSTANCERELATIONTYPE,T1.DISPLAYPRODUCTNUMBER,T1.SEARCHNAME,T1.PRODUCTTYPE,T1.PDSCWPRODUCT,T1.MODIFIEDBY,T1.RECVERSION,T1.RELATIONTYPE,T1.PARTITION,T1.RECID,T2.PRODUCTDIMENSIONGROUP,T2.PRODUCT,T2.MODIFIEDBY,T2.RECVERSION,T2.PARTITION,T2.RECID,T3.PRODUCTMASTER,T3.ISVARIANTGENERATIONENABLED,T3.ISREUSECONFIGURATIONENABLED,T3.RECVERSION,T3.PARTITION,T3.RECID,T4.PRODUCT,T4.STORAGEDIMENSIONGROUP,T4.RECVERSION,T4.PARTITION,T4.RECID,T5.PRODUCT,T5.TRACKINGDIMENSIONGROUP,T5.RECVERSION,T5.PARTITION,T5.RECID,T6.DESCRIPTION,T6.NAME,T6.PRODUCT,T6.LANGUAGEID,T6.MODIFIEDBY,T6.RECVERSION,T6.PARTITION,T6.RECID,T7.HARMONIZEDCODE,T7.NMFCCODE,T7.PRODUCT,T7.STCCCODE,T7.RECVERSION,T7.PARTITION,T7.RECID,T8.OEM,T8.PRODUCTRANGE,T8.PARTITION,T8.RECID,T9.NAME,T9.RECVERSION,T9.RECID FROM ECORESPRODUCT T1 LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUPPRODUCT T2 ON ((T2.PARTITION=@P1) AND (T1.RECID=T2.PRODUCT)) LEFT OUTER JOIN ECORESPRODUCTMASTERMODELINGPOLICY T3 ON ((T3.PARTITION=@P2) AND (T1.RECID=T3.PRODUCTMASTER)) LEFT OUTER JOIN ECORESSTORAGEDIMENSIONGROUPPRODUCT T4 ON ((T4.PARTITION=@P3) AND (T1.RECID=T4.PRODUCT)) LEFT OUTER JOIN ECORESTRACKINGDIMENSIONGROUPPRODUCT T5 ON ((T5.PARTITION=@P4) AND (T1.RECID=T5.PRODUCT)) LEFT OUTER JOIN ECORESPRODUCTTRANSLATION T6 ON ((T6.PARTITION=@P5) AND ((T6.LANGUAGEID=@P6) AND (T1.RECID=T6.PRODUCT))) LEFT OUTER JOIN WHSECORESPRODUCTTRANSPORTATIONCODES T7 ON ((T7.PARTITION=@P7) AND (T1.RECID=T7.PRODUCT)) CROSS JOIN HYDPRODUCTATTRIBUTEVALUEVIEW T8 LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUP T9 ON ((T9.PARTITION=@P8) AND (T2.PRODUCTDIMENSIONGROUP=T9.RECID)) WHERE ((T1.PARTITION=@P9) AND ((T1.INSTANCERELATIONTYPE=@P10) OR (T1.INSTANCERELATIONTYPE=@P11))) AND ((((((T8.PARTITION=@P12) AND ((T8.PARTITION#2=@P13) OR (T8.PARTITION#2 IS NULL))) AND ((T8.PARTITION#3=@P14) OR (T8.PARTITION#3 IS NULL))) AND ((T8.PARTITION#4=@P15) OR (T8.PARTITION#4 IS NULL))) AND ((T8.PARTITION#5=@P16) OR (T8.PARTITION#5 IS NULL))) AND ((T8.PARTITION#6=@P17) OR (T8.PARTITION#6 IS NULL))) AND EXISTS (SELECT 'x' FROM ECORESPRODUCTCATEGORY T10 WHERE ((T10.PARTITION=@P18) AND ((T10.CATEGORYHIERARCHY=@P19) AND (T10.PRODUCT=T1.RECID)))) ORDER BY T1.DISPLAYPRODUCTNUMBER OPTION(FAST 4)
