Solved

Formula error on generic inquiry

  • 3 December 2023
  • 3 replies
  • 90 views

Userlevel 6
Badge +3

Hi! I’m using the formula editor on generic inquiry for this formula: =IIf( ([POReceipt.Status] = 'R' And [INRegister.Status] = 'R') And  ([POReceipt.OrderQty] = sum([INRegister.TotalQty])), 'True', 'False' )

It validates correctly but when I run it, it returns this error: Column 'POReceipt.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'INRegister.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'POReceipt.OrderQty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

Any ideas how to troubleshoot?

 

Attached is GI and this field has the formula:

 

 

icon

Best answer by Robert Sternberg 4 December 2023, 02:17

View original

3 replies

Userlevel 7
Badge +8

Hello! Your Generic Inquiry looks good, here are a few suggestions for troubleshooting:

  1. The formula editor in Acumatica only checks for syntax errors and doesn't guarantee that your formula will execute correctly. For instance, if there's a misspelling in a function name like 'SUM', or a missing parenthesis, the editor would identify these as syntax errors and the validation would fail. However, since your syntax is error-free, the formula passed validation.

 

  1. Regarding the use of aggregate functions, the error is Acumatica asking for more information on the grouping data. Here’s an example: If you're calculating the SUM of 'Total Qty', you need to specify whether the summation is per customer, per PO Receipt, per vendor, etc. This specification is where 'group by' clauses factor into Acumatica’s calculation. You've identified that we should group by 'ReceiptNbr'. This introduces ambiguity with other fields. With grouping by 'ReceiptNbr', Acumatica assumes we could have multiple Line Statuses, Quantities, and so on. Hence, it's necessary to instruct Acumatica whether we want to calculate the SUM, MAX, MIN, or AVG of these values, or alternatively, add these fields to the group by clause.
Userlevel 7
Badge +8

Attaching a cleaner version of your GI, I removed unnecessary tables and as our primary table is INReceipt there is no longer a reason for the group by. 

 

Userlevel 4
Badge +1

Hi!

Can you please check if any of the fields that you’re using in the formula has a NULL value? It has happened to me before because of that. If you do have one, use the IsNull() formula on that value or filter out the NULLs.

Hope this helps.

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