Solved

Need help with an IF statement in GI

  • 16 December 2022
  • 5 replies
  • 553 views
J
Rank
Userlevel 5
Badge

Hi,

 

Trying to build out a GI and needing some help on an IF statement.

 

Here’s the scenario. 

 

If the Lot/Serial class is ‘LOTTED’, then the Lot/Serial Nbr. column should be ‘green’ for ‘LOTTED’ items where it is not empty and ‘red’ for ‘LOTTED’ items that are empty. If it is not LOTTED, then it should be ‘default’.

 

If the Lot/Serial Class is ‘DEFAULT’, the QTY Remaining column should be ‘green’ if QTY Remaining > QTY on Hand and should be ‘red’ if QTY Remaining < QTY on Hand. If it is LOTTED, then it should be ‘default’ in this column.

 

Here are my existing formulas in the respective fields:

QTY Remaining:

=IIf( [AMProdMatl.QtyReq] > [INSiteStatusSummary.QtyOnHand], 'bad', 'good' )

 

Lot/Serial Nbr:

=IIf([AMProdItem.LotSerialNbr]= Null,'bad', 'good' )

 

 

 

icon

Best answer by brendan91 19 December 2022, 20:27

View original

5 replies

BenjaminCrisman
Rank
Userlevel 7
Badge +4

Hi @jwarren! Have you already tried using just ‘’ instead of null?

So like =IIf([AMProdItem.LotSerialNbr]= ‘’,'bad', 'good' )

J
Rank
Userlevel 5
Badge

Hi @jwarren! Have you already tried using just ‘’ instead of null?

So like =IIf([AMProdItem.LotSerialNbr]= ‘’,'bad', 'good' )

The IF statements themselves are working - what I’m trying to do is for lines 3 and lines 4 in my screenshot, since these are Lot Class = DEFAULT, there should be no style (neither green or red) it should be the default style in Lot/Serial column. Vice versa for lines 1 and 2 - there should be no style in the QTY remaining column when it is LOTTED Class.

DConcannon
Rank
Userlevel 6
Badge +2

@jwarrenCan you post the GI XML? You’re going to need nested IIfs for both fields, if I am reading your posts correctly.

J
Rank
Userlevel 5
Badge

@jwarrenCan you post the GI XML? You’re going to need nested IIfs for both fields, if I am reading your posts correctly.

I believe you are correct. Here is an export of the results grid for the GI. 

1 Attachment
brendan91
Rank
Userlevel 5
Badge +1

@jwarren 

This should do it. You just turn the “else” into a second if statement which I think is what everyone else is saying. 

Replace the field captions with the actual field mapping and this should work fine.

Lot/Serial Class Color = iif([Lot/Serial Class] = ‘LOTTED’, ‘default’, iif([QTY Remaining] > [Qty on Hand], ‘good’, ‘bad’))

 

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