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, ... / is there a way to sele...
Finance | Project Operations, Human Resources, ...
Answered

is there a way to select first Only in data entity?

(0) ShareShare
ReportReport
Posted on by junior AX 1,552

So If i made a entity with an entity key that works properly but there are special cases where this key will get duplicated because of a dataSource inside the entity.

Can i do sth like select firstOnly, instead of adding the extra entity key? as i don't want it to appear twice in the xml file for different values of a certain field in this case

I have the same question (0)
  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,093 on at

    Hi JuniorAX,

    I guess you want to apply firstOnly only to the joined data source and it's not possible. Can't you make a view for a table used as  joined data source and use aggregations to get only one record per certain key?

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

    Hi Junior AX,

    Can you provide an example related to your question? Are you able to use virtual fields or computed columns as an alternative?

  • junior AX Profile Picture
    junior AX 1,552 on at

    Hi Sergie,

    You want me to replace the table in the entity with a view with this table, but what do you mean by aggregations what should i do to get first record?

    Hi Andre,

    it's like i have a table with 4 fields joined with the main datasource based on Id

    Id  description   field1    field2  field3

    1       Desc1

    1       Desc2

    i need to show description,field1,field2 and field3

    but i don't want to add the description to the entity key as it's not that important. That's why i want to select first only but how would i replace this with computed column?

    I think i can use virtual fields since i'm using composite entity. But is adding 4 virtual fields instead of joining the table better?

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

    Hi Junior AX,

    What is the main datasource and what is the entity key for that one? Do you intend to export or read data only or would this entity also be used for importing data? Why do you need the first record of the "sub-table"?

  • junior AX Profile Picture
    junior AX 1,552 on at

    Hi Andre,

    - Main DataSource is table1 outer join table2 outer join table 3

    - table 3 is the table with the description field and other 3 fields

    - table2 relation with table1 (table1.Field1 = table2.Field1)  // unique index of table 2 is Field1 + Fieldx

    - table3 relation with table2 ( table3.Fieldx = table2.Fieldx) 

    - Entity key is: Id1,Id2,RecId ( all those are taken from table1) i know recId would have been enough but when i first put the entity key i thought they are enough then i ended up with RecId and left the other fields

    - only for export (read), so i think to make the export work I need to add Field1  of table2 in the entity key.

    - it's not that i need the first record, i just want any description. It's not worth it to get two nodes in the xml with just different description. So i thought instead of adding the entity key i can add somehow  select first only, or should i make 4 virtual fields?

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

    Hi Junior,

    Thanks for the clarification. As mentioned, above you can go create a view and use it in a data entity or add the virtual columns. If you have a lot of records and performance is important, you can test which approach might be quicker. Initially, I would think the view might be quicker as 4 virtual fields will have a kind of 4 additional table joins in the SQL query.

    When you only export to a file, the entity key is not important. This is used to find possible unique existing records when you import data.

  • junior AX Profile Picture
    junior AX 1,552 on at

    Hi Andre,

    1. can you help in how can i do first only using views? i didn't get what i should do exactly?

    2. what do you mean by 4 additional joins, what would happen exactly if i use virtual fields?

    3. I noticed that sometimes the export fails but however the file gets generated and sometimes it fails without the file. I'm using composite entity here ( can't skip staging) so i think entity key is important so as i said it failed for me because i didn't add the extra entity key but it didn't stop the file from getting generated. So if it's not important why would they make it fail?

  • Verified answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,093 on at

    Hi JuniorAX,

    Personally, I would create 2 views

    1. View1  for table3 with Fieldx and max (or min) RecId (FieldX added to GroupBy section and to view fields, RecId field added view fields and aggregation set to Min or Max)

    2. View2 for table3 + inner join by view1 with relation by table3.RecId = view1.MaxRecid and table3.Fieldx = view1.FieldX

    In this case, view2 will show you only one record per Fieldx and you can join it in the data entity.

  • junior AX Profile Picture
    junior AX 1,552 on at

    Hi Sergie,

    Can i make it in one view as well?

    Table 3 (1st datasource) inner join with table3 (2nd datasource) then join this view with the entity?

    where I'll add Fieldx to the group by section of the 2nd dataSource, I'll also add Max/Min RecId to the 2nd Datasource fields ( i think there is an aggregation property based on you suggestion)

    and relation between1st DS and 2nd DS is 1stDS.RecId = 2ndDS.MaxRecid and 1stDS.Fieldx = 2ndDS.FieldX

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,093 on at

    Hi JuniorAX,

    I think it will not work with one view as aggregation should be done for the entire view and you can't leave one data source as is and do aggregation for another.

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
Martin Dráb Profile Picture

Martin Dráb 451 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

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

#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