Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

Implement first only record for joining tables in AOT Queries

Posted on by Microsoft Employee

Hi Team,

I am developing some AOT queries. There is some duplicates data are in joining tables. I tried to fix it by first only property set to true but its not working with inner join.

I need a solution so that I could fetch single record on the basis of duplicates data column.

I tried with Group by option as well but this way i am getting error as i set range in the same query. 

parent table

pastedimage1605625228817v2.png

join table

pastedimage1605624946367v1.png

Thanks

Ashok Kumar

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: Implement first only record for joining tables in AOT Queries

    Hi Ashok,

    You can create a new view and add the table ShippingCarrierAddress as a data source. CustAccount, DlvTermId, and DlvModeId should be added to the group by section and to the list of fields for the new view. ShippingCarrierAccount should be added to the list of fields and aggregation property should be set to Min or Max. After you compile and sync the view, you can use it instead of ShippingCarrierAddress in your query.

    But note that if you will have different ShippingCarrierAccount for the same CustAccount, DlvTermId, and DlvModeId records you will get only one value in the view.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Implement first only record for joining tables in AOT Queries

    Hi Sergei,

    I am developing a query that's have parent table is custtable and its join with shippingcarrieraddress table.

    In shippingcarrieraddress, there is a field named "shippingcarrieraccount". I need this field value to populate in my  view. But i need single record as we have same shippingcarrieraccount value for single customer.

    My view name is custtoshipview. This view retrieving all the delivery address for a specific customer.  please see the screen shot below.

    pastedimage1605631156030v1.png

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: Implement first only record for joining tables in AOT Queries

    Hi Ashok,

    Are you trying to join CustTable with another table with 1-n relationship? If you need to make 1-1 relationship between tables with 1-n relationship, you can make a view for the child table and apply group by criteria in view to make a single record. Then you just join the parent table and created a view in the query.

    One standard example is CustPackingSlipMinMaxDates view, which selects minimal and maximum dates from sales packing slips per line, and later if the view joined to sales line it doesn't create duplicates

  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,417 on at
    RE: Implement first only record for joining tables in AOT Queries

    Hi Ashok,

    What is the desired result from the query? Please provide a query in SQL or X++.

    Also please share screenshot(s) of the query you developed that is not working.

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,513 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,862 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans