Solved

Condition In report & GI - How to do IF string contain Substring?

  • 1 September 2023
  • 21 replies
  • 267 views

Userlevel 4
Badge

Hi,

As said in the title, How to do IF string contain Substring?

Example here : IF [ARInvoice.CustomerID_Customer_acctName] CONTAINS ‘IGA’ 

I saw in the help that we have this operator :

HELP ‘IN’ operator

I tried it in a GI :

I think it’s done the good way but i got an error : Syntax error: The items following the IN keyword must be separated by commas and be enclosed in parentheses.

Is it me or the help have mistake?

Thanks

icon

Best answer by raphrf 25 September 2023, 16:02

View original

21 replies

Badge +10

InStr(str, findStr) will return -1 if the value  findStr is not found in str or 0+ if the value is found, so something like this should work:

=CBool(InStr([ARInvoice.CustomerID_Customer_acctName],'IGA')>=0)

 

Userlevel 4
Badge

Hi, if im right, InStr return a value of position, and I need to verify in a IIf( InStr=null) , it’s a workaround but i think there is a way to have directly the true or false? isnt it?

 

This is my workaround for now, but i want to have something easier since I have big conditions formulas

Badge +10

Hi, if im right, InStr return a value of position

You’re right. I edited my answer.

Badge +10

I see now that your original formula may actually work, but it’s missing the opening quotation mark for ‘FALSE’. It should be:

=IIf('IGA' In [ARInvoice.CustomerID_Customer_acctName],'TRUE','FALSE')

This will return a text value of TRUE or FALSE.

Userlevel 4
Badge

Hi ,

I corrected it and I have the same error

Badge +10

I searched the Acumatica wiki and I believe I have found the page you are referencing.

Notice that this page is listed under the ‘Configuring Scenario Mapping’ section.

 

I believe it is very likely that this will only work in import/export scenarios.

I tried several variations on a Generic Inquiry in my Acumatica instance and nothing seems to work correctly.

Badge +10

@raphrf - Did you get this figured out?

Userlevel 4
Badge

Hi @darylbowman , i didnt found.

The IN operator don’t work in GI. The help don’t state if theses operator are only for import/export or for GI or for both, or “it depend”.

 

only workaround is to use two function :  InStr(str, findStr) 

Userlevel 4
Badge

InStr(str, findStr) will return -1 if the value  findStr is not found in str or 0+ if the value is found, so something like this should work:

=CBool(InStr([ARInvoice.CustomerID_Customer_acctName],'IGA')>=0)

 

Hi @darylbowman 

Can you figure why it’s not working :

 

Badge +10

Try this instead:

IIf(InStr([InventoryItem.InventoryCD],'PRIM')>=0,'vrai','faux')

 

Casting the logic into a bool doesn’t work for some reason.

Userlevel 4
Badge

Try this instead:

IIf(InStr([InventoryItem.InventoryCD],'PRIM')>=0,'vrai','faux')

 

Casting the logic into a bool doesn’t work for some reason.

Hi @darylbowman , thank for your answer,

If I try with this , (last column in example) , that don’t seem to work(in orange in the picture).

Beside, column 2 and 3 seem to work, but as soon I put a “And” (column named 5) and activate it, i get an error :

 

 

So now i’m not able to verify if string contain a word with a “And” with another condition.

What is wrong?!

Badge +10

You should be able to do an 'And' in the conditions of the IIf. For instance:

=IIf(InStr([InventoryItem.InventoryCD],'PRIM')>=0 And *next condition*,'vrai','faux')

I can't see what all you're attempting to put in there, but it looks like maybe you're testing something else. The other option is a Switch statement:

=Switch(InStr([InventoryItem.InventoryCD],'PRIM')>=0,'vrai',*another condition*,*another result*)

 

Userlevel 7
Badge +9

Hi @raphrf Here is a solution to suppor like ‘and’ operator with nested Iif =Iif(InStr( [POOrder.OrderNbr], 'SC')=1,Iif(InStr( [POOrder.OrderNbr], 'SC')=1,1,0),0).

Please find the attached Generic inquiry for your review and verification.

Screenshot showing the result:

 

Userlevel 4
Badge

Hi @ChandraM ,

Thanks , yes a nested IIf seems to work on my side. I don’t understand why we cannot use “And”, “Or”, “Not”….

 

My goal is to do multiple verification , like if InventoryCD contain 'PRIM' and dont contain 'HTRANS' . And I want to put each condition in a switch, i tryed a lot of combination without success

 

Worked only with a nested IIf

=IIf(InStr([InventoryItem.InventoryCD],'PRIM')>=1,IIf(InStr([InventoryItem.InventoryCD],'HPREP')=0,'1','0'),'0')

When i put it in a Switch I don't have result

=Switch((IIf(InStr([InventoryItem.InventoryCD],'PRIM')>=1,IIf(InStr([InventoryItem.InventoryCD],'HPREP')=0,1,0),0)=1),2,(IIf(InStr([InventoryItem.InventoryCD],'PRIM')>=1,IIf(InStr([InventoryItem.InventoryCD],'HTRANS')=0,1,0),0)=1),3,True,0) 
My switch builder

 

Error 

Thanks

Badge +10

I don’t mean this impolitely, but the issue is not with the system. There is syntax that formulas must use in order to be interpreted. As you’ve discovered, they can be rather complicated. Perhaps if you could explain what you’re hoping to accomplish, I could build a statement that would do so.

Just as an example, nesting an IIF statement in a SWITCH statement is probably not the best way to go about it. An example of a switch statement is:

=Switch(InStr([InventoryItem.InventoryCD],'PRIM')>=1,'has PRIM',InStr([InventoryItem.InventoryCD],'HPREP')>=1,'has HPREP')

It makes a logical evaluation and displays a result based upon it. In theory, you could also do this

=Switch(InStr([InventoryItem.InventoryCD],'PRIM')>=1 Or InStr([InventoryItem.InventoryCD],'HPREP')>=1,'has PRIM or HPREP')

which chains two logical statements together with an OR.

But again, if you can concisely communicate what you’d like the statement to do, we could probably help you do it.

Userlevel 4
Badge

Hi Dary, thank for your answer.

First of all i’m sorry for my english is not my native language(french).

The goal is to evaluate in a big Switch some of conditions with priorities in order to choose a desired value.

First condition is Contain “PRIM” and not “HPREP”

Second condition is Contain “PRIM” and not “HTRANS”

Third one condition is Contain “PRIM” 

The final goal is to put these Switch in a Billing Rule(project billing) , but before doing this, i test it in a GI, it’s more quick to see the result .

I tried your logical here and it work properly, even if i replace the Or by a And for testing purpose, the GI work :

=Switch(InStr([InventoryItem.InventoryCD],'PRIM')>=0 Or InStr([InventoryItem.InventoryCD],'HPREP')>=0,'has PRIM or HPREP')

 

If I put a AND NOT it don’t work, and may be I misunderstand something(it’s not the system)

How to acheive AND NOT?

=Switch(InStr([InventoryItem.InventoryCD],'PRIM')>=0 And Not InStr([InventoryItem.InventoryCD],'HPREP')>=0,'has PRIM or HPREP')

Beside, i dont understand why, but the information is that InStr must return -1 if he dont find any result, but on my GI, I need to put 1 instead of 0 in order to work properly.

What do I not understand about the result of InStr?

See my video here Test of InStr verification Value

 

I putted my GI in attachment

Again, thank you !!!

 

 

Badge +10

Beside, i dont understand why, but the information is that InStr must return -1 if he dont find any result, but on my GI, I need to put 1 instead of 0 in order to work properly.

I discovered this as well, and I can’t answer why. It could be that it is documented improperly.

 

The logic of your third condition won’t work because it will always match either of the first two statements. I assumed the logical difference would be ‘contains PRIM and either HPREP or HTRANS. Let me know if that’s not correct.

=Switch(InStr([InventoryItem.InventoryCD],'PRIM')>=1 And InStr([InventoryItem.InventoryCD],'HPREP')<=0,'has PRIM not HPREP',
InStr([InventoryItem.InventoryCD],'PRIM')>=1 And InStr([InventoryItem.InventoryCD],'HTRANS')<=0,'has PRIM not HTRANS',
InStr([InventoryItem.InventoryCD],'PRIM')>=1 And (InStr([InventoryItem.InventoryCD],'HPREP')>=1 Or InStr([InventoryItem.InventoryCD],'HTRANS')>=1),'has PRIM')

 

Userlevel 4
Badge

Hi,

just to let you know , if someone have same issue in the future.

I resolved my problem, with your help and a lot of testing .

1st problem :I wanted to assign a value as last priority in the switch, to do this I put this:

[.....]   ,TRUE,0)

But I had to change to this in order for it to work ,

Solving solution : it’s like this now :

[.....]   ,IIf(TRUE=TRUE,TRUE,FALSE)=TRUE,0)

I don't understand why because for me the condition must be Boolean, and True is a Boolean value?🤔

at least it works🙂

EDIT  :  i realized that the last condition(default) can be ommited, so it’s simpler.

2nd problem : each condition, i was trying to put And/Or without good result in the switch, like:

[.....],

(DayOfWeek([PMTran.Date])=0 Or DayOfWeek([PMTran.Date])>5) And [EPEmployee.PR672TITLE_Attributes]='8' And InStr([InventoryItem.InventoryCD],'HTRANS')>0,60

[.....]

Solving solution :

But I had to change to this in order for it to work(added an Nested IIF proposed from @ChandraM and a comparison) , it’s like this now :

[.....]   
,IIf((DayOfWeek([PMTran.Date])=0 Or DayOfWeek([PMTran.Date])>5) And [EPEmployee.PR672TITLE_Attributes]='8' And InStr([InventoryItem.InventoryCD],'HTRANS')>0,TRUE,FALSE)=TRUE,60,
[.....]

like for the first one, Idon't understand why because for me the first attempt seem to be Boolean?

at least it works🙂

Thanks

Userlevel 7
Badge +9

Hi @raphrf Thanks for the update. Did the nested iif work for you. Please confirm.

Userlevel 4
Badge

Hi @raphrf Thanks for the update. Did the nested iif work for you. Please confirm.

yes , i editted my last post in order to clarify it.

 

Userlevel 7
Badge +9

@Ralph, Thanks for your update.

Reply


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