Load a saved equation using a script

Hi xtrasico,

That is an interesting question.  I have never tried something like that before but it does make sense.  Here is the script that I cam up for it (I have also attached it).  As you can see I first check to make sure it is an IDEA equation file and then I read the equation.  I also found that the equation is stored with \L to indicate the list separator as it will be different depending on the language version of IDEA, so I needed to write a loop that looks for and replaces the \L.  Let me know how it works for you.

Sub Main
	Call DirectExtraction()	'Sample-Bank Transactions.imd
End Sub

' Data: Direct Extraction
Function DirectExtraction
	Dim sEquation As String
	Set db = Client.OpenDatabase("Sample-Bank Transactions.imd")
	Set task = db.Extraction
	task.IncludeAllFields
	dbName = "Cheque.IMD"
	'get the equation - 
	sEquation = getExtractionEquation("Cheque.EQX")
	task.AddExtraction dbName, "", sEquation
	task.PerformTask 1, db.Count
	Set task = Nothing
	Set db = Nothing
	Client.OpenDatabase (dbName)
End Function

'Import an equation that has been saves as a *.eqx file to be used in an IDEAScript
Function getExtractionEquation(sEquationName As String) As String
	Const ForReading = 1
	
	Dim fso As Object
	Dim ts As Object
	Dim task As Object
	Dim sPath As String
	Dim sText As String
	Dim iLine As Integer
	Dim iLengh As Integer
	Dim i As Integer
	Dim sChars As String
	Dim sEquation As String
	
	'assumes that equation is stored in project working directory under the Equation.ILB.  Equation should be called with full name and extension but no path.
	sPath = Client.WorkingDirectory & "Equations.ILB\" & sEquationName
	
	'Use the FileSystemObject to read the equation file
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set ts = fso.OpenTextFile(sPath, ForReading)
	
	iLine = 1
	'Loop through the equation file.
	Do Until ts.AtEndOfStream
		sText = ts.ReadLine
		'check to make sure the first line has IDEA32 on it (note this could change for future versions of IDEA)
		If iLine = 1 And Left(sText,6) <> "IDEA32" Then
			MsgBox "This does not appear to be a valid equation file"
			getExtractionEquation = ""
			Exit Function
		End If
		iLine = iLine + 1
	Loop
	Set ts = Nothing
	Set fso = Nothing
	
	'update the equation with the separator values
	Set task = CreateObject("idea.ConfigureIdea")
	sListSeparator = task.ListSeparator

	'swap the "\L" character for the list separator, this could be the "," or the ";" depending on language version of IDEA.
	iLengh = Len(sText)
	For i = 1 To iLengh
		sChars = Mid(sText, i , 2)
		If sChars = "\L" Then
			sEquation = sEquation & sListSeparator
			i = i + 1 'skip a character as we have replace two characters for one
		Else
			sEquation = sEquation & Left(sChars, 1) 'only take the first character in the sChars variable
		End If
	Next i

	getExtractionEquation = sEquation
	Set task = Nothing
	
End Function

Thank you Brian!  I have not been able to post for many days as I was receiving an error from the website.  I understand the code, but I am having a problem.  When I use the code as it is, I get:
"Error on line 41 - Path not found"
 
Line 41 is: 
<code>Set ts = fso.OpenTextFile(sPath, ForReading)</code>
 
I decided to try and remove the "\" from the sPath  to see what happened.  Now sPath was:
<code>sPath = Client.WorkingDirectory & "Equations.ILB" & sEquationName</code>
 
After that change, I got: 
"Error on line 41 - File not found" 
 
I also copied the EQX file to the working directory, modified the sPath to "sPath = Client.WorkingDirectory & sEquationName" to use the working directory, and still the same errors when using (or not using) the "\". 
 
I honestly have no idea what could be wrong.  Note: I should have started with this: I am using IDEA Client Version 11.2.1.  My bad!  Also, the code for the DirectExtraction is a bit different than AppendField, so I do not know if I will have to include additional parameters or define more variables.

Hi there, more than likely you are using the Unicode or a different language version of IDEA and the "Equation.ILB" is different.  I think in the Unicode version is is "Equation.ILBx" but I am not 100% on that.  You will have to use file explorer to go into that project (or any IDEA project) and see what the directory is.

Good morning Brian, 
 
I do not use the Unicode version of IDEA and use the English language version as I am based in the US.  The IDEA folders only include one ILBX folder: SmartAnalyzer.ILBX, and that's because the Smart Analyzer is now compatible in both versions, unicode and non-unicode (regular). 
Please, see attached screen captures. 
 
I will try to work on this to see if I can come up with a solution.  Thank you for all your help.  I appreciate it!  Have a wonderful day.

I am not sure why it is not working.  Did you try adding msgbox sPath and comparing it with the file explorer?  If the path is correct and file is there I am not sure why it is not working.

This has turned into a very interesting task!  Something to note is that that for one of the the append field equations I want to use, the separator shows as "\L".   For a direct extraction equation using only an .AND. operator for testing purposes, IDEA uses "\D". 
 
Using the "msgbox sPath" command, I found that the script was using the wrong path. 
 
Normally, when importing files, I save new IMB files in subfolders.  In this case, the initial IMB for the process I want to complete was in subfolder \TempPaySup.  Initially, the script was taking that subfolder as the working folder; therefore, the Equations.ILB folder was being looked under TempPaySub, not the main project folder (see Incorrect sPath.JPG): 
\Documents\My IDEA Documents\IDEA Projects\_AUPs NewProg\TempPaySup\Equations.ILB 
 
I started to test using an IMB in the main working project folder, and the script finds the equation (see Correct sPath.JPG): 
\Documents\My IDEA Documents\IDEA Projects\_AUPs NewProg\ 
 
Now the path is correct and the script finds the equation to use.  However, it gives me a new error (see New Error on Line 15.jpg): 
Error on line 15 - Bad equation provided 
 
I believe this has to do with the "\D" characters and the script's section that tell to replace those two characters for a separator, which in the case of a direct extraction using an .AND. operator, the "\D" is going to be a single period character "." (  It should be:    "\DAND\D"    for    ".AND."   ) 
 
Maybe I should just hard code the replacement of the "\D" for a "." and get over it, but I can't find the code to make it possible.  My primitive and basic knowledge of IDEA/VBA comes up to here.  The hamster in my brain fell from the wheel...
 
I added the EQX file, the initial script provided by Brian, and an IMB file with rates.  Any thoughts? 

I have a few things I need to check into to fix this problem.  I knew about the sub-folder problem, I think there is a function to get around this but I can't seem to find it.  Also I always thought the period was surrounding the .AND. in all languages so this I need to check out if it is different depending on the language.