Skip to main content
Answer

Help with Expression: Fallback to First Word of Description if Brand is Empty

  • June 12, 2025
  • 3 replies
  • 76 views
T
Captain I
Forum|alt.badge.img+3

Hi everyone,

I'm trying to write a formula in Acumatica Report Designer where:

  • If [CWMSAItemAttributes.Brand] is empty or null, I want to return the first word from [InventoryItem.Descr].

  • Otherwise, it should return the value from [CWMSAItemAttributes.Brand].

I’ve tried using IndexOf() and Substring(), but I keep getting errors like “index out of range” or “missing operator before the space operand” depending on how I write it.

 

Please guide me .

Here’s one of the formulas I attempted:

 

=IIf( Len(Trim([CWMSAItemAttributes.Brand])) = 0, Substring([InventoryItem.Descr], 0, IndexOf([InventoryItem.Descr], ' ')), [CWMSAItemAttributes.Brand] )

Best answer by darylbowman

Try this:

=IIf(Len(Trim([CWMSAItemAttributes.Brand]))=0,IIf(InStr([InventoryItem.Descr],' ')>0,Substring([InventoryItem.Descr],0,InStr([InventoryItem.Descr],' ')),''), [CWMSAItemAttributes.Brand])

 

I don’t believe ‘IndexOf’ is part of the formula functions. ‘InStr’ should accomplish the same thing.

Taha Yab Ali

3 replies

darylbowman
Captain II
Forum|alt.badge.img+15
  • darylbowman
  • Answer
  • June 13, 2025

Try this:

=IIf(Len(Trim([CWMSAItemAttributes.Brand]))=0,IIf(InStr([InventoryItem.Descr],' ')>0,Substring([InventoryItem.Descr],0,InStr([InventoryItem.Descr],' ')),''), [CWMSAItemAttributes.Brand])

 

I don’t believe ‘IndexOf’ is part of the formula functions. ‘InStr’ should accomplish the same thing.

Daryl⚡Freelance Acumatica Developer (MVPx2🏆) for hire ⏩ linkedin.com/in/darylbowman
Chris Hackett
T
nhatnghetinh
Captain II
Forum|alt.badge.img+11
  • nhatnghetinh
  • Captain II
  • June 13, 2025

Hi ​@tahayabali 

It appears that IndexOf() is not used in Acumatica Report Designer. Please refer to the formula below.

=IIf(
  [CWMSAItemAttributes.Brand] = '' Or Trim([CWMSAItemAttributes.Brand]) = '',
  Left(
    [InventoryItem.Descr],
    IIf(
      InStr([InventoryItem.Descr], ' ') > 0,
      InStr([InventoryItem.Descr], ' '),
      Len([InventoryItem.Descr])
    )
  ),
  [CWMSAItemAttributes.Brand]
)

 

Best Regards,

NNT

 

I have devoted my time and deep passion to Acumatica! 🌐 https://www.linkedin.com/in/phambaotruong
Chris Hackett
T
T
Captain I
Forum|alt.badge.img+3
  • tahayabali
  • Author
  • Captain I
  • June 13, 2025

Thanks I already solved it with =IIf(    Len(Trim([CWMSAItemAttributes.Brand])) > 0,    [CWMSAItemAttributes.Brand],    IIf(        InStr([ARTran.TranDesc], ' ') > 0,        Left([ARTran.TranDesc], InStr([ARTran.TranDesc], ' ') - 1),        [ARTran.TranDesc]    ))

Taha Yab Ali
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