Solved

How to trigger different values on Qty field depending on if the item was free or not?

  • 19 March 2024
  • 4 replies
  • 51 views

Userlevel 2

The report itself is rather simple. It’s a profitability analysis report where I have to show how much item we have sold to a customer, filtered by date. The formula for my Qty field right now is:

=sum(IIf( [ARTran.TranType] <> 'CRM' AND [ARTran.CuryTranAmt] >= 0, [ARTran.Qty], 0 )) - sum(IIf( [ARTran.TranType] = 'CRM', [ARTran.Qty], 0 ))

which lists the items we sold to the customer for a cost. Now, we also give out free stuff to our customer, and for that, I use this formula:

=sum(IIf( [ARTran.TranType] <> 'CRM' AND [ARTran.CuryTranAmt] > 0, [ARTran.Qty], 0 )) - sum(IIf( [ARTran.TranType] = 'CRM', [ARTran.Qty], 0 ))

I was using VisibileExpr field under properties > behavior to switch between formulas from a parameter where user selects if the report has any free items or not. 

Is there any way to do it without using VisibleExpr? I was looking for a way to do the same thing I’m doing right now, but simpler.. Maybe using just one field for Qty rather than using 2 and relying on VisibleExpr

Any help would be appreciated, thanks))

icon

Best answer by DrewNisley 19 March 2024, 13:54

View original

4 replies

Userlevel 1

You could use another IF statement based on the parameter that the user selects to show free items or not. Maybe something like this

=IIf([SHOW FREE QTY PARAMETER]=true, sum(IIf( [ARTran.TranType] <> 'CRM' AND [ARTran.CuryTranAmt] >= 0, [ARTran.Qty], 0 )) - sum(IIf( [ARTran.TranType] = 'CRM', [ARTran.Qty], 0 )), sum(IIf( [ARTran.TranType] <> 'CRM' AND [ARTran.CuryTranAmt] > 0, [ARTran.Qty], 0 )) - sum(IIf( [ARTran.TranType] = 'CRM', [ARTran.Qty], 0 )))

 

Badge +11

@lightman - How does the person running the report know if there are free items or not?

Userlevel 2

@darylbowman I have a parameter set where the user manually selects if there is a free item on the invoice or not.. if they’re the ones processing the purchase, then they already know.. but they can also look up data from past and take care of it

Userlevel 2

@DrewNisley thanks a lot! This worked!!

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