Dialog Box (Updating the extraction with the variables)

talebi

Forums

Hi everyone,
 
I hope you are doing great.
I have got some errors while I was updating some extraction with defined variables.
Below are some examples:
036 is character (because of leading zero)
"ENTITY" is a cloumn name (we did not define field names in the dialog box) but "sEntity" is defined for entity number.
How can I update "036" with "sEntity"?
-- task.AddExtraction dbName, "", "ENTITY == ""036"""
I tried below, for instance, but it did not work.
-- task.AddExtraction dbName, "", "ENTITY" & " == " &  sEntity
or
 
I defined pm and om in the dialog box so how I can update them in the extraction below?
-- task.AddExtraction dbName, "", "@Between(@Abs(AMT) , 750000, 1000000)"
For instance, I tried:
-- task.AddExtraction dbName, "", "@Between(@Abs(AMT) , pm, om)" (it does not work)
 
Thanks,
 
 

talebi Tue, 03/01/2022 - 14:37

Also how about exporting a file? For instance, cYear, PeriodFrom and PeriodTo are variables and all of them are characters. The output shoud be the same as:
cYear = 2021_22
PeriodFrom= Apr21
PeriodTo= Jan22
...\2021_22\Apr21Jan22\...
 
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX1
Dim db As database
Dim task As task
Dim dbName As String
Dim eqn As String
Set db = Client.OpenDatabase("JV.IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "N:\IDEA\Data Analyst\IDEA Projects\Social Service (036)\Exports.ILB\JV\cYear\PeriodFrom+PeriodTo\JV.XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 

Brian Element Tue, 03/01/2022 - 15:51

Hi talebi,

For the first one you can define it as:

"ENTITY == " & Chr(34) & sEntity & Chr(34)

or

"ENTITY == """ & sEntity & """"

The first example uses the Chr(34) for the quotation mark.  The second example uses the double quotes to represent the quotation mark.

For the second item use:

"@Between(@Abs(AMT) , " & pm & ", " & om & ")"

Remember variable have to be outside of the quotes as if they are between the quotes IDEAScript views them as text and not variables.

Brian Element Tue, 03/01/2022 - 15:54

For your second post your folder string should be:

"N:\IDEA\Data Analyst\IDEA Projects\Social Service (036)\Exports.ILB\JV\" & cYear & "\" & PeriodFrom & PeriodTo & "\JV.XLSX"

Remember variables have to be outside the quotes, if not IDEAScript doesn't know what to do with them.

talebi Mon, 03/07/2022 - 12:00

Hi Brian.
I have a question. 
 I ahve a project for ministry of Social Servoce and I save JV exported files in the path below:
task.PerformTask "N:\IDEA\Data Analyst\IDEA Projects\Social Service (036)\Exports.ILB\JV\Excel.XLSX", "Database", "XLSX", 1, db.Count, eqn
Suppose I would like to make my dialog box global and everyone in my office wants to use it. The path below is just for my laptop and I need to make a variable to find each project's path. For instance, we have a variable named PATH so N:\IDEA\Data Analyst\IDEA Projects\Social Service (036) should be replaced by PATH. Suppose we made the JV folder in the Exports.ILB folder.
Is there any way to do that?
Thanks,