Solved

Adding condition in calculated field generic inquiry

  • 20 September 2022
  • 2 replies
  • 373 views

Userlevel 5
Badge +1

Is something like this possible? I wanted to avoid excel by making a calculated field that summarizes a single or group of inventory IDs through GI or report designer. So a calculated field would look like this...

=IIf((( [POLine.InventoryID] = 'DULS001') OR ([POLine.InventoryID] = 'DULS002')), [POLine.LineAmt], '')

Results would look like this...

    Calculated Field Calculated Field
Inventory ID Line Amt Dulse Kelp
DULS001 200 200  
KELP001 500   500
DULS002 300 300  
icon

Best answer by BenjaminCrisman 23 September 2022, 22:26

View original

2 replies

Userlevel 7
Badge +4

Hi @brendan91 ! Are you grouping these results or displaying all the lines?

There won’t be a way to SUM all the lines unless it’s grouped I suspect, unless you create a SQL view which already SUMS these, then this could be displayed instead of a calculation.

But your calculation should work based on the way it looks.

I did a quick mock up using SOOrder and Order Type and found it looks exactly like you’re showing:

=IIF([SOOrder.OrderType]= 'CS' OR [SOOrder.OrderType]='IN', CStr([SOOrder.CuryOrderTotal]), '')

Again though, if you are wanting a summary of these amounts you’ll need to group by the Order Nbr or Inventory ID maybe

Userlevel 5
Badge +1

@BenjaminCrisman I think I was trying it easier for an excel export, or was hoping I could figure out how to summarize this in report designer. The issue I was having with getting this formula to work was around data types and I just figured it out.

I was having a bad day when I wrote this apparently. Thanks!

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