Skip to main content
Answer

(Repost) Can I insert a PREV statement in IIF statement?

  • January 24, 2023
  • 15 replies
  • 123 views
P
Semi-Pro II
Forum|alt.badge.img+1

Hello community,

I have this existing formula where multiple serial#s per item are listed down, but the thing is, say I have 20 serial#s for that item, it lists down the name of the item, its price, and qty again and again (please take a look at the picture). In a separate concern, I received a formula in which I can make a condition that if the same description exists in the previous line, it won’t display again. But I don’t know if it’s possible and how should I insert it in my formula.

Here’s my formula:

=IIf(IsNull([SOShipLine.LotSerialNbr], [SOShipLine.TranDesc])<>’’, [SOShipLine.TranDesc] +'{br}'+ 'SN: ' + [SOShipLine.LotSerialNbr], [SOShipLine.TranDesc])

 

And here’s what I wanna add:

IIf(Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc], '', [],[SOShipLine.TranDesc]

 

My desired result is to list the Item Description once with its multiple SNs.

 

Can somebody please analyze how this would work for me? Huge thanks!

 

Paula

Best answer by aaghaei

=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '')<>'', IIf(Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc],'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] + '{br}' + 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

 

extra closing bracket before first SN

15 replies

P
Semi-Pro II
Forum|alt.badge.img+1
  • paula012
  • Author
  • Semi-Pro II
  • January 24, 2023

Made it, but it only applies to line details with SN in [SOShipLineSplit.LotSerialNbr] and gets error when the DR I pick up has serial number from [SOShipLine.LotSerialNbr]. Been trying to play around this formula:

=IIf(IsNull([SOShipLine.LotSerialNbr], Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc]),'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] +'{br}'+ 'SN: ' + [SOShipLineSplit.LotSerialNbr])

This is the error,

This is the source of SN from details tab,

While this is where I am getting SN when sn from details tab is <split>,

Can somebody see where I should change something here, please? Thanks!

P
Semi-Pro II
Forum|alt.badge.img+1
  • paula012
  • Author
  • Semi-Pro II
  • January 24, 2023

To be more precised, my formula only works with <split> SerialNbr

And with SerialNbrs already available in the details tab, it gets error.

=IIf(IsNull([SOShipLine.LotSerialNbr], Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc]),'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] +'{br}'+ 'SN: ' + [SOShipLineSplit.LotSerialNbr])

Can anybody see what is wrong in my formula? Thanks!

aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Captain II
  • January 24, 2023

try
=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '’)<>'’, IIf(IsNull([SOShipLine.LotSerialNbr], Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc]),'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] +'{br}'+ 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
P
P
Semi-Pro II
Forum|alt.badge.img+1
  • paula012
  • Author
  • Semi-Pro II
  • January 24, 2023

try
=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '’)<>'’, IIf(IsNull([SOShipLine.LotSerialNbr], Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc]),'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] +'{br}'+ 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

Thank you sir, but I think got the same error. What do you think is the problem?

 

P
Semi-Pro II
Forum|alt.badge.img+1
  • paula012
  • Author
  • Semi-Pro II
  • January 24, 2023

try
=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '’)<>'’, IIf(IsNull([SOShipLine.LotSerialNbr], Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc]),'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] +'{br}'+ 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

Thank you sir, but I think got the same error. What do you think is the problem?

 

Pls note, this works with SN from SOShipLineSplit also but gets error when SN is directly from details tab. Thank you

aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Captain II
  • January 24, 2023


I am not so familiar with inventory module but seems SOShipLine.LotSerialNbr is a binary field. Try

=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '’)<>'’, IIf(Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc]),'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] +'{br}'+ 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
P
P
Semi-Pro II
Forum|alt.badge.img+1
  • paula012
  • Author
  • Semi-Pro II
  • January 24, 2023


I am not so familiar with inventory module but seems SOShipLine.LotSerialNbr is a binary field. Try

=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '’)<>'’, IIf(IsNull([SOShipLineSplit.LotSerialNbr], Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc]),'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] +'{br}'+ 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

Sad to say it also doesn’t work Sir, got the same error but for both getting DR with SN from SOShipLineSplit and SOShipLine : (

aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Captain II
  • January 24, 2023

I edited the formula after I posted, did you get the last one and make sure to fix single quotes as I am typing on iPhone 

 

=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '’)<>'’, IIf(Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc]),'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] +'{br}'+ 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
P
aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Captain II
  • January 24, 2023

I see some single quotations I typed are wrong

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
P
Semi-Pro II
Forum|alt.badge.img+1
  • paula012
  • Author
  • Semi-Pro II
  • January 24, 2023

I edited the formula after I posted, did you get the last one and make sure to fix single quotes as I am typing on iPhone 

 

=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '’)<>'’, IIf(Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc]),'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] +'{br}'+ 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

Yes sir I’m changing the single quotes everytime. Now I get a syntax error tho : ( whereelse could I be wrong?

aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Captain II
  • January 24, 2023

Can you copy your edited formula here?

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
P
P
Semi-Pro II
Forum|alt.badge.img+1
  • paula012
  • Author
  • Semi-Pro II
  • January 24, 2023

Can you copy your edited formula here?

=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '')<>'', IIf(Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc]),'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] + '{br}' + 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Captain II
  • Answer
  • January 24, 2023

=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '')<>'', IIf(Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc],'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] + '{br}' + 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

 

extra closing bracket before first SN

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
jinin
Chris Hackett
P
aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Captain II
  • January 24, 2023

Is everything Resolved?

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
P
P
Semi-Pro II
Forum|alt.badge.img+1
  • paula012
  • Author
  • Semi-Pro II
  • January 24, 2023

=IIf(IsNull([SOShipLineSplit.LotSerialNbr], '')<>'', IIf(Prev([SOShipLine.TranDesc])=[SOShipLine.TranDesc],'SN: ' + [SOShipLineSplit.LotSerialNbr],[SOShipLine.TranDesc] + '{br}' + 'SN: ' + [SOShipLineSplit.LotSerialNbr]), [SOShipLine.TranDesc])

 

extra closing bracket before first SN

Oh, I saw it! Thank you very very very much sir @aaghaei ! Now I will try to understand how this works. Big thanks! Really helped me!

aaghaei
Chris Hackett
Terms & ConditionsCookie settingsAccessibility statement
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