Skip to main content

Is there any way to within a GI using the IIF command to do the following?

I wrote a GI to pull all of the SO’s of a certain customer along with the associated invoices created from the SOShipments.  No problem.  See attached screen shot below for an example.

What I would like to do is set the “Sales Order Total” to be 0.00 if there are more than one associated invoice numbers.

The pseudo code logic statement would be something like this:

IIF (‘Order Nbr’.row-1 = ‘Order Nbr’.row), ‘Sales Order Total’=0, ‘Sales Order Total’)

Using this type of logic then the second line in the example screen shot below would be “0.00”, while the first row would be “14,283.93”.  (Ideally that value would be set to NULL, so that no value at all appears there...but that may be even greater wishful thinking.)

Is something like that even possible to do in an Acumatica GI?  

 

 

In some ways, yes, I trying to create a ‘group by’ function inside of the GI.

to create a ‘Group by’ GI

just add the grouping field at the ‘Grouping’ tab

and for the invoice total field set the Aggregate Function to SUM

 

yoelb, I had tried that.  That is not what I want to do because what happens is that the value in “Sales Order Total” then doubles to $28,567.86 as its value when you use the “Group” tab of the GI.  AND it only displays one of the two invoices.  Personally, I think this is a MAJOR bug in Acumatica’s “group” feature for the GI.  Below is the screenshot using that method.
 

 

for the sales order total use MAX

its not a BUG its how the group by function works in database ( to consolidate data )

yoelb.  Huh.  I had no idea that was how MAX worked.  Thank you!!

That was not exactly what I wanted, but this will work!  Again, thank you,
 

 

Actually that is NOT going to work at all.  When you add back the group it no longer displays the second invoice for the order.  

Again, what I want to do is display BOTH lines but only the first line should have a value in the ‘Sales Order Total’ field.  Oh, well.  If it cannot be done, no worries.  I will simply stay with it as it is.

 

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