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 / Finance | Project Operations, Human Resources, ... / Enterprise portal disp...
Finance | Project Operations, Human Resources, ...
Unanswered

Enterprise portal display method filter

(0) ShareShare
ReportReport
Posted on by Taimoor 70

Hi, In ax2012 how can i implement this in query, particularly in EP dataset - I have some items with dimension Color and some dont have Color. I want to join inventTable with CustVendExternalItem to get external item field value. CustVendExternalItem table has records with Color and some with blank Color. I want to retreive the records which perfectly match with the item color-color and if for any item Color record in CustVendExternalItem doesnt exist it should get the record where color is blank.

For the items which are not color enabled it should always retrieve the record with blank color.

I can use display method to achieve this but i need to show this field on enterprise portal grid and user should be able to filter external item Id.

 question.jpg

  • Taimoor Profile Picture
    Taimoor 70 on at
    RE: Enterprise portal display method filter

    Hi Martin, our system has just one company and no partitions so no need to mention. Above sql query i posted to only show that i want to achieve it in AX query build datasource. My original question and the result i want to see is in the snapshot i posted first.

  • Martin Dráb Profile Picture
    Martin Dráb 232,000 Most Valuable Professional on at
    RE: Enterprise portal display method filter

    Let us know when you also reduce your code just to the thing you want to ask about and format your code correctly, e.g. by removing the duplication spacing. Also, you're clearly not aware of the fact that Insert > Insert Code allows you to set syntax highlighting for SQL code.

    Therefore your code might look somehow like this:

    select distinct it.itemid, erc.Name Color, cevi.ExternalItemId
from InventTable it
left outer join ecoResProduct erp on erp.RecId = it.Product
...

    By the way, your SQL code is incorrect, because you forgot to take companies (and partitions) into account.

  • Taimoor Profile Picture
    Taimoor 70 on at
    RE: Enterprise portal display method filter

    I have reposted the existing code. Thanks

  • Martin Dráb Profile Picture
    Martin Dráb 232,000 Most Valuable Professional on at
    RE: Enterprise portal display method filter

    I assume that you don't have a problem with this complete code, but only with a part of it. Could you please throw away everything unrelated to your question, format your code and paste it again through Insert > Insert Code (in the rich-formatting view)? Thank you.

  • Taimoor Profile Picture
    Taimoor 70 on at
    RE: Enterprise portal display method filter

    No, i dont think so. Below SQL query does the job but i cant implement it in AX.

    with CTE_All (itemid, COLOR, EXTERNALITEMID )

as

(

select distinct it.itemid,erc.NAME COLOR, CEVI.EXTERNALITEMID

from INVENTTABLE it

left outer join ecoresproduct ERP on ERP.RECID = it.Product

   left outer join EcoResProductMasterDimensionValue EPMDV on EPMDV.ColorProductMaster = ERP.RecId

   left outer join ecoResColor erc on erc.RecId = EPMDV.Color

left join  CustVendExternalItem CEVI on CEVI.ItemId = IT.ItemId

and CEVI.CustVendRelation = IT.PrimaryVendorId

and CEVI.ModuleType = 3

and CEVI.InventDimId = 'AllBlank'

),CTE_Col (itemid, COLOR, EXTERNALITEMID )

as

(

select distinct it.itemid,erc.NAME COLOR, CEVI2.EXTERNALITEMID

from INVENTTABLE it

left outer join ecoresproduct ERP on ERP.RECID = it.Product

   left outer join EcoResProductMasterDimensionValue EPMDV on EPMDV.ColorProductMaster = ERP.RecId

   left outer join ecoResColor erc on erc.RecId = EPMDV.Color

left join  CustVendExternalItem CEVI2 on CEVI2.ItemId = IT.ItemId

and CEVI2.CustVendRelation = IT.PrimaryVendorId

and CEVI2.ModuleType = 3

left join INVENTDIM id on id.INVENTCOLORID = erc.NAME and CEVI2.InventDimId = id.INVENTDIMID

and INVENTBATCHID = '' and id.INVENTLOCATIONID = '' and id.INVENTSITEid = '' and id.INVENTSTATUSID = ''

where(id.INVENTCOLORID = erc.NAME )

)

select CTE_All.itemid, CTE_All.COLOR,cte_col.EXTERNALITEMID EXTERNALITEMID1, CTE_All.EXTERNALITEMID EXTERNALITEMID2

, isnull(cte_col.EXTERNALITEMID, CTE_All.EXTERNALITEMID) EXTERNALITEMID

from CTE_All left join CTE_COL on CTE_All.itemid = CTE_Col.itemid and CTE_All.COLOR = CTE_Col.COLOR

where CTE_All.ItemId = '1207647'

  • Martin Dráb Profile Picture
    Martin Dráb 232,000 Most Valuable Professional on at
    RE: Enterprise portal display method filter

    Can't you use an outer join?

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,278 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,000 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans