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 / 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

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,976 Most Valuable Professional on at

    Can't you use an outer join?

  • Taimoor Profile Picture
    Taimoor 70 on at

    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 237,976 Most Valuable Professional on at

    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

    I have reposted the existing code. Thanks

  • Martin Dráb Profile Picture
    Martin Dráb 237,976 Most Valuable Professional on at

    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

    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.

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 > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 456 Super User 2025 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 429 Most Valuable Professional

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 239 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans