Using a Variable in Equation Editor

VITOPIEPOLI

Forums

Hi there,
I am trying to call the value of a variable created with this code:
        Dim Materiality As Long
 Dim PerfMateriality As Long
Dim SamplingThreshold As Long
Materiality = InputBox("Type in the Materiality for this client (to the nearest €).")
PerfMateriality=Materiality*0.5
SamplingThreshold=PerfMateriality*0.01
 
Now I am tryinh to append a field on the spreadsheet that would give me a yes or not as to the transaction is above or below the materiality or the sampling threshold.
 
In the Equation Editor I am using the following code:
@if( AMOUNT  >= &Materiality&, "Yes","No").
 
I tried to find guideline as to how to call a variable but I did find it anywhere.
Could you please help me?
 
Many thanks.
 
Vito
 
 
 

klmi Wed, 10/30/2019 - 11:12

You are mixing IDEAScript code (input and calculation of materiality) with an IDEA equation. Like you want to do that it's not possible!

VITOPIEPOLI Wed, 10/30/2019 - 11:22

HI klmi,
You are right, I was mixing up two different wolrds.
I came about a script written by my previous colleague, but I wanted it to get modified. I am quite new with IDEA Script. 
This is the script:
Sub Main
Dim Materiality As Long' Could set to 2 decimal places & do similarly for the following two variables
Dim PerfMateriality As Long
Dim SamplingThreshold As Long
Materiality = InputBox("Type in the Materiality for this client (to the nearest €).")
PerfMateriality=Materiality*0.5
SamplingThreshold=PerfMateriality*0.01
MsgBox "Materiality = €" & Materiality & vbCrLf & "Performance  Materiality = €" & PerfMateriality & vbCrLf  & "SamplingThreshold = €" & SamplingThreshold
 
Call AddPERFMATCOL()
 
End Sub
 
Function AddPERFMATCOL' Add PMAT & Sampling Threshold columns
Dim db As Database
Set db = Client.OpenDB("TEST-JLines.IMD")' Open the sample database
Dim NewField As Task' Create the task
Set NewField = db.TableManagement
Dim ThisTable As Table' Obtain a reference to the table
Set ThisTable = db.TableDef
Dim AuditField As Field' Create a new field
Set AuditField = ThisTable.NewField
AuditField.Name = "PERFMAT"' Configure the PMAT field
AuditField.Description = "Performance Materiality"
AuditField.Type = WI_EDIT_NUM
AuditField.Equation =PMateriality 
NewField.AppendField AuditField' Add the PMAT field
NewField.PerformTask
AuditField.Name = "SAMPLINGT"' Configure the Sampling Threshold field
AuditField.Description = "SamplingThreshold"
AuditField.Type = WI_EDIT_NUM
AuditField.Equation =SamplingThreshold
NewField.AppendField AuditField' Add the Sampling Threshold field
NewField.PerformTask
AuditField.Name = "PMAT"' Configure the PMAT field
AuditField.Description = "Absolute value above Performance Materiality"' Both variables are Long type
AuditField.Type = WI_EDIT_NUM
AuditField.Equation = "@If ( ( ABSOLUTE >= PERFMAT), 1, 0)"' @int() - taken out
NewField.AppendField AuditField' Add the PMAT field
NewField.PerformTask
AuditField.Name = "ST"' Configure the Sampling Threshold comparison field
AuditField.Description = "Absolute value above Sampling Threshold"' Both variables are Long type
AuditField.Type = WI_EDIT_NUM
AuditField.Equation = "@If ( ( ABSOLUTE >= SAMPLINGT), 1, 0)"' @int() - taken out
NewField.AppendField AuditField' Add the Sampling Threshold comparison field
NewField.PerformTask
Set AuditField = Nothing' Clean up memory
Set ThisTable = Nothing
Set NewField = Nothing
Set db = Nothing
Client.CloseDatabase("TEST-JLines.IMD")
End Function
I can't get this work because it is stuck on Set db = Client.OpenDB("TEST-JLines.IMD") because it gives me an error as "Object does not support this property or method"
Could you help me on this?