Solved

Different Values of the Same Field?

  • 15 June 2021
  • 5 replies
  • 194 views

Can anyone explain why these provide different values?

from the results grid

 

the result:

from the GI

 

icon

Best answer by Neil Cantral 15 June 2021, 22:54

View original

5 replies

Userlevel 5
Badge +3

The first column is displaying the AcctCD value of the parent account while the second column is showing the actual BAccountID value.

The parent BAccountID is the value on the Customer record and does not require a second join to grab the parent AcctCD. I assume this is done for performance reasons to increase the efficiency of formulas and queries. However, when writing a GI it is changed to display the AcctCD for the sake of convenience and usability since it doesn’t require the user to manually add another table/join if they’re just looking for a reference to the parent account.

Userlevel 5
Badge +2

It is because one value is the Values that you “see” displayed & the other is the SQL value of the record in the Table. 

You can see this clearly using Doc Type form the ARInvoice table.  

 

@wfilipiak67@Neil Cantral Thanks for the responses. Is it possible to return the more human-readable AcctCD in a formula? Is there a different field I should use?

Userlevel 5
Badge +2

@jthrashr, I have found that in the formula’s it easier to use the Schema value.
As an example when totaling/  displaying the AR Invoices & Credit memos, we negate the Credit Memos by using 

=IIf( [ARInvoice.DocType]='CRM', -([ARInvoice.CuryOrigDocAmt]), [ARInvoice.CuryOrigDocAmt] )
Instead of

 =IIf( [ARInvoice.DocType]='Credit Memo’, -([ARInvoice.CuryOrigDocAmt]), [ARInvoice.CuryOrigDocAmt] )

Userlevel 5
Badge +3

I recommend joining an aliased table so you can easily reference anything from the parent account without unexpected behaviors.

Here is a quick example stolen from a Business Accounts inquiry:

 

 

 

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