Search variable keyword in column with @isini()

KoreKa

Forums

Hi IDEA friends!I want to add a variable to the script that can be set by the script user. 
Sub Main
...
 
Keyword1="test"
Keyword2="test2"
Keyword3="test3"
Keyword4=""
Keyword5=""
...
Keyword15=""
...
End Sub
 
The corresponding function:
Function Risk_Summary (Keyword1, Keyword2, Keyword3)
Set db = Client.OpenDatabase("XY.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "KEYWORD"
field.Description = "Risiko vorhandene Keywords"
field.Type = WI_VIRT_NUM
field.Equation = "@If(@isini(""+Keyword1+"";TEXT);1;@If(@isini(""+Keyword2+"";TEXT);1;@If(@isini(""+Keyword3+"";TEXT);1;0)))"
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
The new column shall hold 1 if one of the keywords is true and 0 if no keyword is found within the TEXT column. The final test shall be able to hold 15 keywords to test for.
Currently I have the problem that the script runs fine but the keywords are not found eventhough they are true within the TEXT column.
Thanks in advance!

klmi Wed, 04/05/2023 - 04:41

Please check your equation of the appended field (f.e. with the equation editor).
With the double quotation marks around the keywords @isini searches for "+Keyword1+", "+Keyword2+" and so on.
 
The correct equation should be:
field.Equation = "@If(@isini("+Keyword1+";TEXT);1;@If(@isini("+Keyword2+";TEXT);1;@If(@isini("+Keyword3+";TEXT);1;0)))"
 
Double quotation marks are neccessary if you would like to define the searched word within the equation:
field.Equation = "@If(@isini(""test"";TEXT);1;@If(@isini(""test2"";TEXT);1;@If(@isini(""test3"";TEXT);1;0)))"
 
For longer equations that can become a little bit confusing. You can also try Chr(34) in such cases.

Brian Element Sun, 04/16/2023 - 07:47

A good way to test this out is to make the equation equal to a variable and then display the variable in a msgbox, if it doesn't look like what you would expect to see in the equation editor then it won't work.  

eqn = "@If(@isini(""+Keyword1+"";TEXT);1;@If(@isini(""+Keyword2+"";TEXT);1;@If(@isini(""+Keyword3+"";TEXT);1;0)))"
MsgBox eqn

When I run this I can see right away that there is a problem, the keywords aren't showing up.  

As klmi mentioned you are missing the double quotes so you are staying within the the equation instead of accessing the variables in IDEASCript.

Your equation should be written like this:

eqn = "@If(@isini(""" & Keyword1 & """;TEXT);1;@If(@isini(""" & Keyword2 & """;TEXT);1;@If(@isini(""" & Keyword3 & """;TEXT);1;0)))"

In this case when we view it in the msgbox we have the following:

Now if we put this in our equation editor it would run, the first one would not give you the expected results.  One problem with this is that the editor gets confused with all the quotes and it looks like you have problems when you don't.  

In the editor everything is showing as red which indicates it is text, even though the Keyword1 is a variable and not part of the text, it will work fine but the editor gets confused.  If you use klmi suggestion of using the chr(34) which is the same as the double quote you can rewrite your equation so that it the editor reads it properly. 

Both the equations are the same, just how you write it changed.