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 :
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
Best answer by raphrf
View original
+11
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, 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
+11
Hi, if im right, InStr return a value of position
You’re right. I edited my answer.
+11
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.
Hi ,
I corrected it and I have the same error
+11
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.
+11
Hi
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)
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
Can you figure why it’s not working :
+11
Try this instead:
IIf(InStr([InventoryItem.InventoryCD],'PRIM')>=0,'vrai','faux')
Casting the logic into a bool doesn’t work for some reason.
Try this instead:
IIf(InStr([InventoryItem.InventoryCD],'PRIM')>=0,'vrai','faux')
Casting the logic into a bool doesn’t work for some reason.
Hi
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?!
+11
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*)
Hi
Please find the attached Generic inquiry for your review and verification.
Screenshot showing the result:
Hi
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) 
Error
Thanks
+11
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.
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 !!!
+11
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')
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
[.....]
,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
Hi
Hi
yes , i editted my last post in order to clarify it.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.