Solved

Trying to Create an Generic Inquiry and pull from two tables


Hello! 

I’m trying to create an Generic Inquiry that pulls data from our Sales Orders and Support Cases; however, I believe I’m having issues with configuring it properly. As of right now, I have it set up to where it can pull information from Sales Orders (Order qty, order total, etc.), but I can’t seem to pull a certain attribute from our Support Cases. When I add the attribute to the GI list, it just shows up empty. 

I’m assuming I don’t have a relation set up properly somewhere? All help is appreciated! 

icon

Best answer by vkumar 28 October 2020, 04:50

View original

10 replies

Userlevel 7
Badge +12

Hi @Kishanptl ,

Could you attach your current Generic Inquiry XML file?  I will take a look and let you know if I found anything that can help you.

 

Best Regards,
Naveen B

@naveenb74 ,

Sure thing! Attached is the XML file you requested. 

 

Thank you,

Userlevel 7
Badge +8

Hi,

I have checked the GI. The attribute data you are trying to pull sits in ‘CSAnswers’ table/DAC. Let me know if the column you are using is user defined column or just attribute. You need to link CSAnswers to CRCase based on NoteID column to pull attribute data. Here are couple of screenshots which explains how attribute data is stored in DAC/ db table. Hope this helps. 

 

 

Regards,

Vijay,

 

The column I am using is just an attribute as shown in your first screenshot. Not sure if this changes anything.

Userlevel 7
Badge +8

Hi @Kishanptl 

In that case, the DB links/details mentioned on my previous response should help you select required table and set the relationship. Let me know if it helps. I did not see CSAnswers table included on list Tables on your GI. 

Regards,

 

Vijay,

 

Thank you for the response! I tried what you mentioned and I might have missed something because it’s still not working quite like how I wanted it to.

Here is what I did:

  1. Added the CSAnswers table to the GI
  2. Created a relation from CRCase to CSAnswers (Parent to Child)
  3. Added Data Field links CR.Case.NoteID = CSAnswers.RedNoteID
  4. I kept the attribute the same in the results grid so this could also be the issue? Object “CRCase” and Data Field is “Attribute I want to use.”

When I go back to the results grid of the GI, the attribute I am trying to pull from is still  showing up blank.

Userlevel 5
Badge +2

 

Hi @Kishanptl 

Added Data Field links CR.Case.NoteID = CSAnswers.RedNoteID

If this still reads RedNoteID, then it will likely be the cause of the issue.

Otherwise I would make sure your configuration looks like this:

 

 

 

 

If you are still not getting the correct results try importing the GI attached.

 

Userlevel 7
Badge +8

Hi @Kishanptl 

Check above reply of Benjamin. If the result is still not correct, send us latest GI xml file for review.

 

Regards,

 

@vkumar and @BenjaminCrisman,

 

Sorry! That was a typo, it does indeed read “refNoteID” but it still reads blank. I think the issue is that there is that I haven’t made the link between the Case and it’s Sales Order.

For example, looking at Benjamin’s final screenshot of the list of cases, each Case should be linked to an SO. So let’s say SO-00001 is linked to case ID “000097”. I should see SO-00001 with case ID’s “000097” attribute. I apologize if I didn’t make that clear before! Should I consider rebuilding the GI?

Hey all,

I figured it out! The only thing I had to change was the links between SOOrder to CRRelation and CRRelation to CRCase. I changed the both “CRRelation.DocNoteID” to “CRRelation.RefNoteID” and it pulls the attribute from Cases and links them to the respective Sales Order!

Thank you all for your assistance and patience on this case! I really appreciate it!

Reply


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 — 2020  Acumatica, Inc. All rights reserved