Solved

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

Userlevel 3
+1
• Semi-Pro II
• 92 replies

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

icon

Best answer by aaghaei 24 January 2023, 06:45

View original

### 15 replies

Userlevel 7
+9

=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

Userlevel 3
+1

=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!

Userlevel 7
+9

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])

Userlevel 7
+9

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])

Userlevel 7
+9

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])

Userlevel 7
+9

Can you copy your edited formula here?

Userlevel 7
+9

Is everything Resolved?

Userlevel 3
+1

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!

Userlevel 3
+1

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!

Userlevel 3
+1

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?

Userlevel 3
+1

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

Userlevel 3
+1

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 : (

Userlevel 7
+9

I see some single quotations I typed are wrong

Userlevel 3
+1

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?

Userlevel 3
+1

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])