web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Product attributes &am...
Microsoft Dynamics AX (Archived)

Product attributes & hierarchy in view

(0) ShareShare
ReportReport
Posted on by Erol Celebic 420

Is there any ready to use view with all the product related attributes and hierarchies? The purpose of this is to be able to search based on attributes and hierarchy affiliation to the item. Ideally, we should be able to export this view as perspective for BI usage.

*This post is locked for comments

I have the same question (0)
  • Mariano Gracia Profile Picture
    Mariano Gracia on at

    I have done a view in AX in which you can query product attributes, it is quite complex, as an attribute can be a string, boolean, integer, real or date, you have to customize a view method to cast all possible values to string, here you have the sql code you have to put in the view method:

    CASE INSTANCERELATIONTYPE
        WHEN 4348 --EcoResIntValue
            THEN (SELECT TOP 1 CAST(INTVALUE AS VARCHAR(20)) FROM ECORESINTVALUE AS VALUE WHERE VALUE.RECID = ECORESVALUERECID)
        WHEN 4350 --EcoResTextValue
            THEN (SELECT TOP 1 TEXTVALUE FROM ECORESTEXTVALUE AS VALUE WHERE VALUE.RECID = ECORESVALUERECID)
        WHEN 4337 --EcoResBooleanValue
            THEN (SELECT TOP 1 CAST(CASE BOOLEANVALUE WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '\' END AS VARCHAR(20)) FROM ECORESBOOLEANVALUE AS VALUE WHERE VALUE.RECID = ECORESVALUERECID)
        WHEN 4346 --EcoResFloatValue
            THEN (SELECT TOP 1 CONVERT(VARCHAR(20), CAST(FLOATVALUE as DECIMAL(17, 2))) FROM ECORESINTVALUE AS VALUE WHERE VALUE.RECID = ECORESVALUERECID)
        ELSE
            ''

  • Erol Celebic Profile Picture
    Erol Celebic 420 on at

    Thank you for your fast reply!

    How about attributes which are connected directly to the hierarchy node? For example in the retail hierarchy you can define such attributes.

  • Verified answer
    Mariano Gracia Profile Picture
    Mariano Gracia on at

    My view works with attributes related to items, because once you have link an item to a hierarchy, AX creates new records to relate attributes and product, which is easier than try to query the attributes related to a hierarchy node, because attributes can be inherited!. I have also made a process to create templates from a hierarchy in order to import the attributes with a csv file, here you have the code to see how to retrieve the attributes related to a hierarchy:

    private str header()
{
    str                         ret;

    EcoResCategoryAttribute     ecoResCategoryAttribute_Product;

    Query                       query = new query();
    QueryRun                    qr;
    QueryBuildDataSource        qbdsEcoResCategoryAttribute;
    QueryBuildDataSource        qbdsEcoResCategory;
    QueryBuildDataSource        qbdsEcoResAttribute;
    QueryBuildDataSource        qbdsEcoResAttributeType;

    EcoResCategoryAttributeGroup    ecoResCategoryAttributeGroup;
    EcoResAttributeGroup            ecoResAttributeGroup;
    EcoResAttributeGroupAttribute   ecoResAttributeGroupAttribute;

    qbdsEcoResCategoryAttribute = query.addDataSource(tablenum(EcoResCategoryAttribute));
    qbdsEcoResCategoryAttribute.addRange(fieldNum(EcoResCategoryAttribute, Modifier)).value(queryValue(EcoResCategoryAttributeModifier::Category));

    qbdsEcoResAttribute = qbdsEcoResCategoryAttribute.addDataSource(tableNum(EcoResAttribute));
    qbdsEcoResAttribute.relations(true);
    qbdsEcoResAttribute.fetchMode(QueryFetchMode::One2One);
    qbdsEcoResAttribute.joinMode(JoinMode::ExistsJoin);

    qbdsEcoResAttributeType = qbdsEcoResAttribute.addDataSource(tableNum(EcoResAttributeType));
    qbdsEcoResAttributeType.relations(true);
    qbdsEcoResAttributeType.fetchMode(QueryFetchMode::One2One);
    qbdsEcoResAttributeType.joinMode(JoinMode::ExistsJoin);
    SysQuery::findOrCreateRange(qbdsEcoResAttributeType, fieldNum(EcoResAttributeType, IsHidden)).value(queryValue(NoYes::No));

    qbdsEcoResCategory = qbdsEcoResCategoryAttribute.addDataSource(tableNum(EcoResCategory));
    qbdsEcoResCategory.relations(true);
    qbdsEcoResCategory.fetchMode(QueryFetchMode::One2One);

    if (NoYes::No == ecoResCategory.IsCategoryAttributesInherited)
    {
        qbdsEcoResCategory.addRange(fieldNum(EcoResCategory,RecId)).value(queryValue(ecoResCategory.RecId));
    }
    else
    {
        EcoResCategory::setCategoryRangeBasedOnInheritanceQBDS(qbdsEcoResCategory, ecoResCategory, fieldNum(EcoResCategory, IsCategoryAttributesInherited) );
    }

    qr = new QueryRun(query);

    while (qr.next())
    {
        if (qr.changed(tableNum(EcoResCategoryAttribute)))
        {
            ecoResCategoryAttribute_Product = qr.get(tableNum(EcoResCategoryAttribute));
            ret += strFmt("%1;", ecoResCategoryAttribute_Product.getAttributeName());
            totalAtributes++;
        }
    }

    while select ecoResCategoryAttributeGroup
        index hint EcoResCategoryAttributeGroupIdx
        where ecoResCategoryAttributeGroup.Category == ecoResCategory.RecId
        join ecoResAttributeGroup
            index hint RecId
            where ecoResAttributeGroup.RecId    == ecoResCategoryAttributeGroup.AttributeGroup
            join ecoResAttributeGroupAttribute
                index hint AttributeGroupAttributeIdx
                where ecoResAttributeGroupAttribute.AttributeGroup == ecoResAttributeGroup.RecId
    {
        ret += strFmt("%1;", EcoResAttribute::find(ecoResAttributeGroupAttribute.Attribute).Name);
        totalAtributes++;
    }

    return ret;
}


Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans