Finance | Project Operations, Human Resources, ...

Implement first only record for joining tables in AOT Queries

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (4)

Answers (0)

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Sergei Minozhenko 23,093 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

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.

Was this reply helpful? Yes No