Scripting Examples

The following scripts provide useful examples of VB scripts that may be used to manipulate User-Defined Fields on Contacts and Loans. These scripting techniques may be extended for use in controlling the execution of tasks in the Task Manager.

Note

Please refer to the COM API developer documentation for additional information on functions and fields.

Tip

Previous examples used an alternate syntax that created an object to reference the NLS application. This method may not be compatible in some situations. If your scripts are not executing as expected, check and see if your script begins with the Set App = GetObject(, "nls.application") line. If it does, try removing the line and replace all instances of App.function with nlsApp.function in the script.
OldNew
Set App = GetObject(, "nls.application")
Income = App.GetField("CIF_Detail_UDF1")
Income = nlsApp.GetField("CIF_Detail_UDF1")

CIF_Detail_UDF1 is Annual Income. CIF_Detail_UDF2 is a text field called Income Status. This field has a default value of Pending. The field is to be automatically updated based on the amount entered in Annual Income: Less than 25,000 = Low; 25,000 – 75,000 = Medium; Greater than 75,000 = High.

The following script is set “When Field Changes” on CIF_Detail_UDF1:

VBScript

Income = nlsApp.GetField("CIF_Detail_UDF1")
If Income < 25000 then
	nlsApp.SetField "CIF_Detail_UDF2", "Low"
Else
	If Income < 75000 then
		nlsApp.SetField "CIF_Detail_UDF2","Medium"
	Else
		nlsApp.SetField "CIF_Detail_UDF2","High"
	End If
End If

Income = nlsApp.GetField("CIF_Detail_UDF1")

A variable called Income is set to the value in the CIF_Detail_UDF1 field

If Income < 25000 then

nlsApp.SetField "CIF_Detail_UDF2", "Low"

Else

If Income < 75000 then

nlsApp.SetField "CIF_Detail_UDF2","Medium"

Else

nlsApp.SetField "CIF_Detail_UDF2","High"

End If

End If

 The proper text is placed in the CIF_Detail_UDF2 field, based on the value of the Income variable.

CIF_Detail_UDF3 is Land Value. CIF_Detail_UDF4 is Structure Value. CIF_Detail_UDF5 is Property Value. Whenever Land Value or Structure Value is changed, Property Value will be updated to be the sum.

The following script is set “When Field Changes” on both CIF_Detail_UDF3 and CIF_Detail_UDF4:

VBScript

LandValue = nlsApp.GetField("CIF_Detail_UDF3")
StructureValue = nlsApp.GetField("CIF_Detail_UDF4")
PropertyValue = CDbl(LandValue) + CDbl(StructureValue)
nlsApp.SetField "CIF_Detail_UDF5", PropertyValue

LandValue = nlsApp.GetField("CIF_Detail_UDF3")

 Gets the value in field CIF_Detail_UDF3

StructureValue = nlsApp.GetField("CIF_Detail_UDF4")

Gets the value in field CIF_Detail_UDF4

PropertyValue = CDbl(LandValue) + CDbl(StructureValue)

Adds them together. The CDbl command forces the two variables to be numbers instead of strings.

nlsApp.SetField "CIF_Detail_UDF5", PropertyValue

Places the sum into field CIF_Detail_UDF5

To prevent a user from changing the Property Value field other than as a sum of the Land and Structure Value fields, the focus can be made to jump to Land Value any time the Property Value field is clicked.

The following script is set “Before Input” on CIF_Detail_UDF5:

VBScript

nlsApp.SetFocus ("CIF_Detail_UDF3")

nlsApp.SetFocus ("CIF_Detail_UDF3")

 Switches focus back to Land Value field

The lending company wishes to track how long it is taking to sell their loans from first contact with the customer to closing date. Field CIF_Detail_UDF6 will be the first contact date. CIF_Detail_UDF7 will be the closing date. Whenever one of these dates is set, and there exists a valid date in both fields, CIF_Detail_UDF8 “Selling Period” will be set to the number of days between them.

The following script is set “When Field Changes” on both CIF_Detail_UDF6 and CIF_Detail_UDF7:

VBScript

FirstContact = nlsApp.GetField("CIF_Detail_UDF6")
CloseDate = nlsApp.GetField("CIF_Detail_UDF7")
If FirstContact <> "" and CloseDate <> "" Then
	FirstContact = mid(FirstContact, 6, 5) & "/" & left(FirstContact, 4)
	CloseDate = mid(CloseDate, 6, 5) & "/" & left(CloseDate, 4)
	SalePeriod = DateDiff("D", FirstContact, CloseDate)
	nlsApp.SetField "CIF_Detail_UDF8", SalePeriod
End If

FirstContact = nlsApp.GetField("CIF_Detail_UDF6")

CloseDate = nlsApp.GetField("CIF_Detail_UDF7")

If FirstContact <> "" and CloseDate <> "" Then

 If no date is entered in either field the rest of this procedure is skipped.

FirstContact = mid(FirstContact, 6, 5) & "/" & left(FirstContact, 4)

Dates in UDF fields are stored internally in the format “yyyy/mm/dd.” This command converts the date to “mm/dd/yyyy.

CloseDate = mid(CloseDate, 6, 5) & "/" & left(CloseDate, 4)

SalePeriod = DateDiff("D", FirstContact, CloseDate)

 The DateDiff function, when used with the “D” parameter will return the number of days between the other two parameters.

nlsApp.SetField "CIF_Detail_UDF8", SalePeriod

End If

Sets the answer in CIF_Detail_UDF8 and closes the If statement.

Field CIF_Detail_UDF9 is the customer’s birthday. Field CIF_Detail_UDF10 is the customer’s age at the time of loan closing. We will use the close date in field CIF_Detail_UDF7.

The following script is set “When Field Changes” on both CIF_Detail_UDF9 and CIF_Detail_UDF7. On CIF_Detail_UDF7, it is placed after the script that is already there, so that changing the close date will result in updates to both CIF_Detail_UDF8 and CIF_Detail_UDF10.

VBScript

DateOfBirth = nlsApp.GetField("CIF_Detail_UDF9")
ClosingDate = nlsApp.GetField("CIF_Detail_UDF7")
If DateOfBirth <> "" And ClosingDate <> "" Then
	Age = CInt(Left(ClosingDate, 4)) - CInt(Left(DateOfBirth, 4))
	If Mid(DateOfBirth, 6, 5) > Mid(ClosingDate, 6, 5) Then
		Age = Age - 1
	End If
	nlsApp.SetField "CIF_Detail_UDF10", Age
End If

DateOfBirth = nlsApp.GetField("CIF_Detail_UDF9")

ClosingDate = nlsApp.GetField("CIF_Detail_UDF7")

If DateOfBirth <> "" And ClosingDate <> "" Then

Age = CInt(Left(ClosingDate, 4)) - CInt(Left(DateOfBirth, 4))

 Extracts just the year portion of each date and finds the difference.

If Mid(DateOfBirth, 6, 5) > Mid(ClosingDate, 6, 5) Then

Age = Age - 1

End If

 Extracts the “mm/dd” portion from each date and compares them. If the closing date is before the customer’s birthday, one is subtracted from the customer’s age.

nlsApp.SetField "CIF_Detail_UDF10", Age

End If

Sets the answer in CIF_Detail_UDF10 and closes the If statement.

Field CIF_Detail_UDF11 is Hire Date. Field CIF_Detail_UDF12 will be Time at Job as of Closing.

The following script is set “When Field Changes” on both CIF_Detail_UDF11 and CIF_Detail_UDF7. On CIF_Detail_UDF7, it is placed after the scripts that are already there, so that changing the close date will result in updates to CIF_Detail_UDF8, CIF_Detail_UDF10, and CIF_Detail_UDF12.

VBScript

ClosingDate = nlsApp.GetField("CIF_Detail_UDF7")
HireDate = nlsApp.GetField("CIF_Detail_UDF11")
If ClosingDate <> "" And HireDate <> "" Then
	Years = CInt(Left(ClosingDate, 4)) - CInt(Left(HireDate, 4))
	Months = CInt(Mid(ClosingDate, 6, 2)) - CInt(Mid(HireDate, 6, 2))
	If Mid(HireDate, 6, 5) > Mid(ClosingDate, 6, 5) Then
		Years = Years -1
		Months = Months + 12
	End If
	If Mid(HireDate, 9, 2) > Mid(ClosingDate, 9, 2) Then
		Months = Months -1
	End If
	Employed = Years & "  Years, " & Months & " Months"
	nlsApp.SetField "CIF_Detail_UDF12", Employed
End If

ClosingDate = nlsApp.GetField("CIF_Detail_UDF7")

HireDate = nlsApp.GetField("CIF_Detail_UDF11")

If ClosingDate <> "" And HireDate <> "" Then

Years = CInt(Left(ClosingDate, 4)) - CInt(Left(HireDate, 4))

Months = CInt(Mid(ClosingDate, 6, 2)) - CInt(Mid(HireDate, 6, 2))

If Mid(HireDate, 6, 5) > Mid(ClosingDate, 6, 5) Then

Years = Years -1

Months = Months + 12

End If

 If we have not yet reached the anniversary of the hire date in the year of closing, we will have a negative number for months. We correct this by adding 12 months and subtracting one year.

If Mid(HireDate, 9, 2) > Mid(ClosingDate, 9, 2) Then

Months = Months -1

End If

 If we have not yet reached the day of the month on which the customer was hired, then we subtract one from the number of months.

Employed = Years & " Years, " & Months & " Months"

nlsApp.SetField "CIF_Detail_UDF12", Employed

End If

 We build a string consisting of the years and months of employment, assign that string to CIF_Detail_UDF12, and close the If statement.

Field CIF_Detail_UDF13 will be a numeric field Taxable Income. Field CIF_Detail_UDF14 will be a drop down list called Tax Status with the following options: Single, Married – Filing Jointly, Married – Filing Separately, Head of Household, and Trust or Estate. The script in CIF_Detail_UDF12 and CIF_Detail_UDF13 will calculate the Tax Bracket and display it in field CIF_Detail_UDF15.

VBScript

TaxableIncome = nlsApp.GetField("CIF_Detail_UDF13")
TaxStatus = nlsApp.GetField("CIF_Detail_UDF14")
If TaxableIncome <> "" And TaxStatus <> "" Then
  Select Case TaxStatus
    Case "SINGLE"
      If TaxableIncome <= 6000 Then
        Bracket = "10%"
      Else
        If TaxableIncome > 6000 and TaxableIncome <= 26250 Then
          Bracket = "15%"
        Else
          If TaxableIncome > 26250 and TaxableIncome <= 63550 Then
            Bracket = "27%"
          Else
            If TaxableIncome > 63550 and TaxableIncome <= 132600 Then
              Bracket = "30%"
            Else
              If TaxableIncome > 132600 and TaxableIncome <= 288350 Then
                Bracket = "35%"
              Else
                Bracket = "38.6%"
              End If
            End If
          End If
        End If
      End If
    Case "MARRIED - FILING JOINTLY"
      If TaxableIncome <= 12000 Then
        Bracket = "10%"
      Else
        If TaxableIncome > 12000 and TaxableIncome <= 43850 Then
          Bracket = "15%"
        Else
          If TaxableIncome > 43850 and TaxableIncome <= 105950 Then
            Bracket = "27%"
          Else
            If TaxableIncome > 105950 and TaxableIncome <= 161450 Then
              Bracket = "30%"
            Else
              If TaxableIncome > 161450 and TaxableIncome <= 288350 Then
                Bracket = "35%"
              Else
                Bracket = "38.6%"
              End If
            End If
          End If
        End If
      End If
    Case "MARRIED - FILING SEPARATE"
      If TaxableIncome <= 6000 Then
        Bracket = "10%"
      Else
        If TaxableIncome > 6000 and TaxableIncome <= 21925 Then
          Bracket = "15%"
        Else
          If TaxableIncome > 21925 and TaxableIncome <= 52975 Then
            Bracket = "27%"
          Else
            If TaxableIncome > 52975 and TaxableIncome <= 80725 Then
              Bracket = "30%"
            Else
              If TaxableIncome > 80725 and TaxableIncome <= 144175 Then
                Bracket = "35%"
              Else
                Bracket = "38.6%"
              End If
            End If
          End If
        End If
      End If
    Case "HEAD OF HOUSEHOLD"
      If TaxableIncome <= 10000 Then
        Bracket = "10%"
      Else
        If TaxableIncome > 10000 and TaxableIncome <= 35150 Then
          Bracket = "15%"
        Else
          If TaxableIncome > 35150 and TaxableIncome <= 90800 Then
            Bracket = "27%"
          Else
            If TaxableIncome > 90800 and TaxableIncome <= 147050 Then
              Bracket = "30%"
            Else
              If TaxableIncome > 147050 and TaxableIncome <= 288350 Then
                Bracket = "35%"
              Else
                Bracket = "38.6%"
              End If
            End If
          End If
        End If
      End If
    Case "TRUST OR ESTATE"
      If TaxableIncome <= 1750 Then
        Bracket = "15%"
      Else
        If TaxableIncome >1750 and TaxableIncome <= 4150 Then
          Bracket = "28%"
        Else
          If TaxableIncome > 4150 and TaxableIncome <= 6300 Then
            Bracket = "31%"
          Else
            If TaxableIncome > 6300 and TaxableIncome <= 8650 Then
              Bracket = "36%"
            Else
              Bracket = "39.6%"
            End If
          End If
        End If
      End If
    End Select
  nlsApp.SetField "CIF_Detail_UDF15", Bracket
End If