Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / is there a way to sele...
Finance forum
Answered

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

Posted on by 1,548

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

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: is there a way to select first Only in data entity?

    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.

  • junior AX Profile Picture
    junior AX 1,548 on at
    RE: is there a way to select first Only in data entity?

    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

  • Verified answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: is there a way to select first Only in data entity?

    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,548 on at
    RE: is there a way to select first Only in data entity?

    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?

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 288,459 Super User on at
    RE: is there a way to select first Only in data entity?

    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,548 on at
    RE: is there a way to select first Only in data entity?

    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?

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 288,459 Super User on at
    RE: is there a way to select first Only in data entity?

    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,548 on at
    RE: is there a way to select first Only in data entity?

    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... 288,459 Super User on at
    RE: is there a way to select first Only in data entity?

    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?

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: is there a way to select first Only in data entity?

    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?

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans