Skip to main content

Hey Community!! 

 

I have customized a an out of the box GI (Appointment Details). I have added tables SO order and joined it with Service Order. And joined opportunity table (CR Relation and CR Opportunity) to SO order.

 

This was done to get owner ID of the opportunity.

 

What I can observe is that for certain records only, the lines are duplicating and for others it is fine. Also,  observed this was working fine few days ago but suddenly not sure why it is showing the duplicates.

 

Here is the table joins: 

 

These are the dupe values I am seeing only for certain records: 
 

One thing I noticed is the Owner Field is coming for one line and is not coming for another wherever there are dupes.

 

Attaching xml for the GI.

Hello,

Duplicates usually mean something is wrong with the join.  When you join CRRelation to CROpportunity,  the tables’ key fields should be used to join, Possibly Company ID and Relation ID, not NoteID.

CRRelation has two note id fields (TargetNoteID and DocNoteId) though I recommend sticking with key fields when joining tables/classes.

@laura01 : What would be the key fields For CR Relation to CROpportunity?

@laura01 : Or is there any other way to get the owner of Opportunity on this GI?

Hello,

The Key Fields in CRRelation are Company + Relation ID.  The key fields in Opportunity are Company + Opportunity ID.  Joining on both will only work if Relation ID is the same entity as an Opportunity.  I’m not familiar with what a “Relation ID” is in Acumatica; not sure these tables can be correctly joined on their key fields.

Another idea to remove duplicates:  Maybe try joining Noteid = Noteid… this will work if every note id is unique within Acumatica. Or try CompanyID (this is the same field in both tables) +  noteid fields in the join.

HTH.

 

Hey @laura01 . It is possible to join SO order Table to CROpportunity table using CRRelation.
Here are the joins: 
 

The issue of getting some duplicates was because some of the opportunities had a Quote on it which was used to create Sales order. So it was showing 2 lines for one Sales Order, one joint with the Quotes and one with the Opportunity.

I put this condition on my query and it removed the dupes that were showing due to the quotes relation.
 

 

The GI works fine now and is giving the result as expected. Thanks :) 

Thank you for sharing your solution with the community @adalvi !

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved