Solved

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

P
Rank
Userlevel 3
Badge +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

icon

Best answer by aaghaei 24 January 2023, 06:45

View original

15 replies

P
Rank
Userlevel 3
Badge +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!

P
Rank
Userlevel 3
Badge +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!

aaghaei
Rank
Userlevel 7
Badge +8

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
Rank
Userlevel 3
Badge +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?

 

P
Rank
Userlevel 3
Badge +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

aaghaei
Rank
Userlevel 7
Badge +8


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
Rank
Userlevel 3
Badge +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 : (

aaghaei
Rank
Userlevel 7
Badge +8

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*.
aaghaei
Rank
Userlevel 7
Badge +8

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
Rank
Userlevel 3
Badge +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?

aaghaei
Rank
Userlevel 7
Badge +8

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
Rank
Userlevel 3
Badge +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])

aaghaei
Rank
Userlevel 7
Badge +8

=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*.
aaghaei
Rank
Userlevel 7
Badge +8

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
Rank
Userlevel 3
Badge +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!

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