Skip to main content
Solved

Help to debug Generic Inquire generated query

  • 14 June 2024
  • 8 replies
  • 92 views

Hello everyone,


I'm working on a tool to convert GIs to SQL Queries for an external tool, and when debugging the generated Acumatica SQL query for one of my Generic Inquiry I see that it has a LEFT JOIN to the ARPayment table, but I can't find any reference to ARPayment in the GI's editor, or in the GI's exported XML file. The ARPayment is neither a BaseType DAC for any of the DACs used in the GI.

 

These are the tables in the GI:

And all relations:

 

The generated query used to retrieve the GI data has this LEFT JOIN:

 

There is no reference to the ARPayment in the selected fields also.

 

Does someone have any clue as to why this join is being added?

 

Thanks!

Ricardo.

8 replies

darylbowman
Rank
Badge +12

Could you post your Results tab?

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
R
Rank

Hi, @darylbowman,

Here are the Results Grid tab:

Results Grid 1/2
 
Result Grid 2/2

 

Thanks!

Ricardo.

darylbowman
Rank
Badge +12

I was looking for something like this, where you’re using a field that is available but not able to be displayed without the system performing an extra table join, but I’m not really seeing anything related to ARPayment.

 

Does the generated query also include the results? Can you see what information is being returned by [SOInvoice_ARPayment]?

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
R
Rank

The results do not appear to have any SOInvoice_ARPayment related data. The joins to resolve the descriptions fields are expected and I can understand, but I can't trace the source of the join to ARPayment.

Chris Hackett
Rank
Userlevel 7
Badge

Hi @ricardosilva were you able to find a solution? Thank you!

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
R
Rank

Hi, @Chris Hackett. Not yet. We're checking each GI query on the "trace" screen manually.

andriitkachenko
Rank
Userlevel 6
Badge +2

Hi @ricardosilva 

PX.Objects.SO.SOInvoice is a projection:

[PXProjection(typeof(Select2<SOInvoice, InnerJoin<SOInvoice.ARRegister, On<SOInvoice.ARRegister.docType, Equal<SOInvoice.docType>, And<SOInvoice.ARRegister.refNbr, Equal<SOInvoice.refNbr>>>, LeftJoin<PX.Objects.AR.Standalone.ARPayment, On<SOInvoice.docType, Equal<PX.Objects.AR.Standalone.ARPayment.docType>, And<SOInvoice.refNbr, Equal<PX.Objects.AR.Standalone.ARPayment.refNbr>>>>>>), Persistent = true)]

When projection is added to an SQL query, Acumatica replaces it with the subquery, built using BQL declaration defined in the PXProjectionAttribute.

Andrii Tkachenko | Sprinterra | https://www.sprinterra.com/ | If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
R
Rank

Hi, @andriitkachenko. This is what I have beenlooking for. Thank you!

Reply


Terms & ConditionsCookie settings