Finance | Project Operations, Human Resources, ...

How to create a view with SELECT DISTINCT TOP (100) PERCENT in D365FO

(0) Share

Report

Posted on by CU03040925-0

For a customer I need to convert a old AX4 view into D365FO

This is the SQL to create the view

SELECT DISTINCT TOP (100) PERCENT dbo.SALESLINE.SALESID AS SalesOrderNum, 
	dbo.SALESLINE.LINENUM AS POLineNum, 
	dbo.SALESLINE.ITEMID AS ItemNum,
    dbo.SALESLINE.NAME AS ItemDesc, 
	dbo.SALESLINE.SALESUNIT AS UOM, 
	dbo.SALESLINE.PURCHORDERFORMNUM AS PONum,
    dbo.SALESLINE.EXTERNALITEMID AS ACCPartNum, 
	CONVERT(INT, dbo.INVENTITEMINVENTSETUP.MULTIPLEQTY) AS QTY, 
	dbo.SALESTABLE.SALESSTATUS AS [SO Status],
    dbo.SALESLINE.SALESSTATUS AS [Line Status]
	
FROM 
	dbo.SALESLINE WITH (NOLOCK) LEFT OUTER JOIN
    dbo.INVENTITEMINVENTSETUP WITH (NOLOCK) ON dbo.SALESLINE.ITEMID = dbo.INVENTITEMINVENTSETUP.ITEMID LEFT OUTER JOIN
    dbo.SALESTABLE WITH (nolock) ON dbo.SALESLINE.SALESID = dbo.SALESTABLE.SALESID
WHERE
     (dbo.SALESLINE.SALESSTATUS = 3) AND (dbo.SALESTABLE.SALESSTATUS = 3)
ORDER BY POLineNum

My problem is that I need to create this as view and then convert the view to a Data entity to be used by an external app

the big 'problem' is this part: SELECT DISTINCT TOP (100) PERCENT

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Pete Alberts 3,542 on at

Like (0)

Report
RE: How to create a view with SELECT DISTINCT TOP (100) PERCENT in D365FO

I see a few hurdles... Here goes.

If it is needed to say: Create a Data Entity. You can't create one from scratch - so I suggest copying an existing DE and then clearing it. Build the DE query: SalesTable, SalesLine & InventItemInventSetup.

Pull in all your fields from the relevant data sources.

If you really need the casting for InventItemInventSetup.MultipleQty, then you should use a computed column. I can assist with that.

As you gave the fields new names in AX4 - you can rename the DE fields to whatever you like.

SalesStatus: I'd add an additional relation in the query: SalesLine.SalesStatus == SalesTable.SalesStatus. And then add a range on SalesLine (or SalesTable, not sure) on SalesStatus. I guess you can hardcode it to 3.... But that's not BP. Also the Base Enum SalesStatus is extensible, so you shouldn't reference it with an integer - so if you do hardcode it, rather specify it like SalesStatus::Invoiced. The correct route would be to create a parameter somewhere and then inner join on the parameter field.

Ordering and Top100: I am 99% sure it isn't possible to do that in or on the DE element itself. But you will use OData to use the DE. And OData has that needed functionality. For example: rootURL/data/YourDE?$orderBy=POLineNum&$top=100. That syntax is most probably not 100% - I haven't worked with it in a while. But the idea is there.

And then if you need to use DISTINCT you should re-evaluate you query. If you build the query properly you won't have that problem. Worst case scenario I'd create a View with a relevant grouping to achieve the DISTINCT (I don't know how to word that properly). Then you can use the View as one (and maybe only) DS in the DE query.

Was this reply helpful? Yes No
Blue Wang on at

Like (0)

Report

RE: How to create a view with SELECT DISTINCT TOP (100) PERCENT in D365FO

Hi Rabol,

Do you get a solution?

To implement Distinct records, you can try Goup by...

You can refer this:https://stackoverflow.com/questions/27400080/how-to-distinct-records-in-dynamics-x

Was this reply helpful? Yes No
Suggested answer

Eugen Glasow 6,359 on at

Like (1)

Report

RE: How to create a view with SELECT DISTINCT TOP (100) PERCENT in D365FO

Use direct SQL, see CostStatementView / CostStatementViewBuilder.

Was this reply helpful? Yes No

Community site session details

How to create a view with SELECT DISTINCT TOP (100) PERCENT in D365FO

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

How to create a view with SELECT DISTINCT TOP (100) PERCENT in D365FO

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates