Skip to main content
Solved

Formula error on generic inquiry

estebanperalta54
Captain II
Forum|alt.badge.img+4

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:

 

 

Best answer by Robert Sternberg

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. 

 

View original
If you found the post helpful please like the post or if it answered your question mark it as best answer. Thanks!
Did this topic help you find an answer to your question?

3 replies

Robert Sternberg
Captain II
Forum|alt.badge.img+7

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.
Robert Sternberg, Crestwood Associates -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
Laura02
Chris Hackett
2 people like this
  • Laura02
    Laura02
  • Chris Hackett
    Chris Hackett
Robert Sternberg
Captain II
Forum|alt.badge.img+7

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. 

 

1 Attachments
Robert Sternberg, Crestwood Associates -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
Laura02
Chris Hackett
2 people like this
  • Laura02
    Laura02
  • Chris Hackett
    Chris Hackett
miguel80
Semi-Pro II
Forum|alt.badge.img+1
  • miguel80
  • Semi-Pro II
  • 71 replies
  • December 5, 2023

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.

Miguel Q
Chris Hackett
1 person likes this
  • Chris Hackett
    Chris Hackett

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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