Skip to main content
Solved

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

  • January 24, 2023
  • 15 replies
  • 197 views
  • Translate All
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

View original

15 replies

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!

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

Translate
aaghaei
Captain II
Forum|alt.badge.img+11
  • 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])

Translate
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*.
    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?

     

    Translate
    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

    Translate
    aaghaei
    Captain II
    Forum|alt.badge.img+11
    • 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])

    Translate
    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*.
      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 : (

      Translate
      aaghaei
      Captain II
      Forum|alt.badge.img+11
      • 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])

      Translate
      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
        Captain II
        Forum|alt.badge.img+11
        • aaghaei
        • Captain II
        • January 24, 2023

        I see some single quotations I typed are wrong

        Translate
        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*.
        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?

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

        Can you copy your edited formula here?

        Translate
        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*.
          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])

          Translate
          aaghaei
          Captain II
          Forum|alt.badge.img+11
          • 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

          Translate
          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
            Captain II
            Forum|alt.badge.img+11
            • aaghaei
            • Captain II
            • January 24, 2023

            Is everything Resolved?

            Translate
            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*.
              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!

              Translate
                Terms & ConditionsAccessibility 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