Skip to main content
Solved

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


Forum|alt.badge.img+1

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

Best answer by raphrf

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

View original
Did this topic help you find an answer to your question?

21 replies

darylbowman
Captain II
Forum|alt.badge.img+13

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)

 


Forum|alt.badge.img+1
  • Author
  • Semi-Pro III
  • 146 replies
  • September 1, 2023

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


darylbowman
Captain II
Forum|alt.badge.img+13
raphrf wrote:

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

You’re right. I edited my answer.


darylbowman
Captain II
Forum|alt.badge.img+13

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.


Forum|alt.badge.img+1
  • Author
  • Semi-Pro III
  • 146 replies
  • September 1, 2023

Hi ,

I corrected it and I have the same error


darylbowman
Captain II
Forum|alt.badge.img+13

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.


darylbowman
Captain II
Forum|alt.badge.img+13

@raphrf - Did you get this figured out?


Forum|alt.badge.img+1
  • Author
  • Semi-Pro III
  • 146 replies
  • September 13, 2023

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) 


Forum|alt.badge.img+1
  • Author
  • Semi-Pro III
  • 146 replies
  • September 22, 2023
darylbowman wrote:

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 :

 


darylbowman
Captain II
Forum|alt.badge.img+13

Try this instead:

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

 

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


Forum|alt.badge.img+1
  • Author
  • Semi-Pro III
  • 146 replies
  • September 22, 2023
darylbowman wrote:

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


darylbowman
Captain II
Forum|alt.badge.img+13

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*)

 


Forum|alt.badge.img+9
  • Semi-Pro I
  • 714 replies
  • September 22, 2023

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:

 


Forum|alt.badge.img+1
  • Author
  • Semi-Pro III
  • 146 replies
  • September 23, 2023

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


darylbowman
Captain II
Forum|alt.badge.img+13

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.


Forum|alt.badge.img+1
  • Author
  • Semi-Pro III
  • 146 replies
  • September 23, 2023

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

 

 


darylbowman
Captain II
Forum|alt.badge.img+13
raphrf wrote:

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')

 


Forum|alt.badge.img+1
  • Author
  • Semi-Pro III
  • 146 replies
  • Answer
  • September 25, 2023

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


Forum|alt.badge.img+9
  • Semi-Pro I
  • 714 replies
  • September 25, 2023

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


Forum|alt.badge.img+1
  • Author
  • Semi-Pro III
  • 146 replies
  • September 25, 2023
ChandraM wrote:

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.

 


Forum|alt.badge.img+9
  • Semi-Pro I
  • 714 replies
  • September 25, 2023

@Ralph, Thanks for your update.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings