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
join table
Thanks
Ashok Kumar
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.
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.
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
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.
#1
André Arnaud de Cal...
293,276
Super User 2025 Season 1
#2
Martin Dráb
231,975
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
Share
Report
All responses (
Answers (
