"SELECT DISTINCT" on Data entity

(0) Share

Report

Posted on by add

Hello guys,

I have a custom data entity in D365 and I would like to perform operation similar to the "SELECT DISTINCT" in SQL. I want to remove the duplicate rows in the entity. Is this possible? Could I use range or computed column?

Thank you

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi add,

It is possible to use computed columns. You can find the documentation here: docs.microsoft.com/.../data-entity-computed-columns-virtual-fields

Was this reply helpful? Yes No
Suggested answer

Srini Ramidi 2 on at

Like (0)

Report

Hello,

You need to use "GroupBy" if you want distinct rows. With Computed/Virtual column you can manipulate column values.

Best,

Srini

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

You can use a computed column with RANK() to identify duplicates, but it doesn't filter them out.

Was this reply helpful? Yes No
add on at

Like (0)

Report

Thank you guys for the help but I'm still struggling. The whole case is this. I have to create a data entity which has these fields: RouteId, OperationId, ItemId, Setup time, Run time, Resource requirement. I have created the entity but I get many duplicate rows. I discovered that if I use SQL Query on the entity I can get what I need. Here's the query:

SELECT DISTINCT CustomColumn = CASE ITEMRELATION WHEN '' THEN ITEMID ELSE ITEMRELATION END,

ROUTETABLEROUTEID, OPERATION, COMPANY, SETUPTIME, PROCESSTIME, REQUIREDOPERATIONSRESOURCEGROUPID, REQUIREDOPERATIONSRESOURCEID

FROM MyEntity

Now I want to implement that logic in the entity. I have created a computed column with the condition and the only thing left is to filter out the duplicated values. I can't find a Group by on data entity fields.

The reason why I want to filter out the duplicated rows is because the entity will be exported to Azure and then used in PowerBI. I want to keep the exported rows to minimum to avoid unnecessary slow export procedure.

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report
Why you don't you simply build a query like this?

select * from TheTable group by RouteId, OperationId, ItemId, Setup time, Run time, Resource requirement

(It's pseudocode, not real source code.)

Was this reply helpful? Yes No
add on at

Like (0)

Report

Hello Martin. Thank you for your reply.

I think I can't use your suggestion because of the relations between the tables. This is the SQL that I tried but it returns many duplicate rows.

SELECT ROUTEVERSION.ROUTEID, ROUTEOPR.OPRID, ROUTEVERSION.ITEMID, ROUTEOPR.SETUPTIME, ROUTEOPR.PROCESSTIME FROM ROUTEVERSION

JOIN ROUTE on ROUTEVERSION.ROUTEID = ROUTE.ROUTEID

JOIN ROUTEOPR on ROUTE.OPRID = ROUTEOPR.OPRID

GROUP BY ROUTEVERSION.ROUTEID, ROUTEOPR.OPRID, ITEMID, SETUPTIME, PROCESSTIME

Here is a picture of the entity I created:

Was this reply helpful? Yes No
Suggested answer

Srini Ramidi 2 on at

Like (0)

Report

Hello,

Can you show us the screenshot of Fields and GroupBY? As i said earlier, you need to add the fields to GroupBy so that you will get only distinct rows.

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report
Can't you elaborate what problem you have with your T-SQL code?

SELECT ROUTEVERSION.ROUTEID, ROUTEOPR.OPRID, ROUTEVERSION.ITEMID, ROUTEOPR.SETUPTIME, ROUTEOPR.PROCESSTIME FROM ROUTEVERSION JOIN ROUTE on ROUTEVERSION.ROUTEID = ROUTE.ROUTEID JOIN ROUTEOPR on ROUTE.OPRID = ROUTEOPR.OPRID GROUP BY ROUTEVERSION.ROUTEID, ROUTEOPR.OPRID, ITEMID, SETUPTIME, PROCESSTIME

I don't believe that GROUP BY in T-SQL is broken.

Was this reply helpful? Yes No