Skip to main content

In my code I’m looking to see if an invoice already exists before proceeding.  I was using this query:

 ARRegister invoice =

    PXSelect<ARRegister,
       Where<ARRegister.docType, Equal<Required<ARRegister.docType>>,
       And<ARRegister.refNbr, Equal<Required<ARRegister.refNbr>>>>>.
       Select(this, receiptRow.ApplyToDocType, receiptRow.InvNbr);

But it would not find the record.  I confirmed that it is in the ARRegister table (local install, local database).

So I decided to try this:

ARInvoice invoice = SelectFrom<ARInvoice>.
    Where<ARInvoice.docType.IsEqual<@P.AsString>.
    And<ARInvoice.refNbr.IsEqual<@P.AsString>>>.
    View.
    Select(this, receiptRow.ApplyToDocType, receiptRow.InvNbr);

and that worked.  It is a different table but the primary keys on both tables are the same.

So, my question is, what’s the difference between the PXSelect and the SelectFrom calls? 

In case it matters ‘this’ is within an Action button on a custom screen (custom graph showing records from a custom DAC).

Hi @ddunn  As per my knowledge, there will NOT be any difference between BQL and FBQL queries.

In the background, FBQL queries will be converted to the BQL queries and fetch the details.


Also, Have you tried with the below BQL query ? (PXReadonly will fetch the details directly from the database)

If you have NOT verified, can you please verify and confirm?

ARRegister invoice =

    PXSelectReadonly<ARRegister,
       Where<ARRegister.docType, Equal<Required<ARRegister.docType>>,
       And<ARRegister.refNbr, Equal<Required<ARRegister.refNbr>>>>>.
       Select(this, receiptRow.ApplyToDocType, receiptRow.InvNbr);

 

Interesting the PXSelectReadonly didn’t work, either.

So, I ran the Profiler.  The SQL query ran the query against ARPayment (with an alias of oARPayment_ARRegister].

Is that because, earlier in the routine I had run this query:

ARPayment existingPayment = SelectFrom<ARPayment>.
    Where<ARPayment.docType.IsEqual<@P.AsString>.
    And<ARPayment.refNbr.IsEqual<@P.AsString>>>.
    View.
    Select(this, receiptRow.DocType, receiptRow.PaymentRef);

Did running the different queries, using the same ‘this’, blur how the second query looked for the data? 

I know that ARRegister is not a base table but is an extension (off of ARTran?) Could that be part of the issue?

Hi @ddunn 

 

Since you are doing the lookup by the key fields, you could also try either of the following:

 

ARInvoice invoice = ARInvoice.PK.Find(this, receiptRow.ApplyToDocType, receiptRow.InvNbr);

ARRegister register = ARRegister.PK.Find(this, receiptRow.ApplyToDocType, receiptRow.InvNbr);

 

The current versions of Acumatica generally have a PK.Find option for most DACs.  I also find this approach to be much easier to follow when trying to retrace my code later (plus, it’s a lot less verbose than writing a BQL/FBQL query that requires a two line where clause).

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