Siebel - DeleteRecord Method in eScript

Let me take this opportunity to share a unique issue that I faced during one small requirement development.

Requirement: Contacts with a specific roles are created due a configuration miss in the EAI. As the product is live the fix can be put only after the root cause is found and change window is available. Till then these records needs to be deleted. As the number of records is huge deleting these records manually is a time consuming task and no back end deletion is allowed as the product is live. As a work around a client side BS would be developed to over come the problem.

Siebel  Version : 7.7

Below is the pseudo code

BusCom = "TechLabHelp"
With BusCom
               clear Query()
               Search for Contact with Role "TECHLABS"
               FirstRec = FirstRecord
               While (FirstRec)
                       FirstRec = DeleteRecord
               End While
End With

Hope pseudo code is simple and self explanatory. Now let me show how the Siebel eScript looked like.


.............

BusComp = "TechLabHelp"
With BusComp
{
    ClearToQuery();
    SetSearchSpec("Type", "TECHLABS");
    ExecuteQuery(ForwardOnly);
    var FirstRec=FirstRecord();
    while(FirstRec)
    {
     FirstRec = DeleteRecord();
     }
}
..................


Not sure why but the above code would always delete only one record form the query result. Also as a best practice and as said by Siebel Bookshelf NextRecord method cannot be used after DeleteRecord as the cursor is moved to next record by default. I was totally not sure if I had a way to resolve this. However logically it can be resolved by having one more While loop and keep the count of records, delete the records until count goes to zero. But Two while loops is not a good idea as it consumes a lot of resources.

So how did I resolve the issue is as below.


.............

BusComp = "TechLabHelp"
With BusComp
{
    ClearToQuery();
    SetSearchSpec("Type", "TECHLABS");
    ExecuteQuery(ForwardOnly);
    var FirstRec=FirstRecord();
    while(FirstRec)
    {
     FirstRec = DeleteRecord();
     //This did the trick. The Cursor would always be reset to First record and the deletion went fine.
    FirstRec=FirstRecord();
     }
}
..................

Its simple but took good amount of time to find the issue.

Will try to share similar posts going forward.

Learn basics of Vb.Net in 10 Simple Posts - Part 2

Welcome back to Learn basics of VB.Net in ten Simple posts. This is the second post in the series and we shall see some more useful ways of using VB.Net and also see how we can improve our first project to do more.  In this post I will be showing how to add a TextBox, Label and also how a little bit coding.

Before we begin lets understand what is Text box and a Label. In simple terms we can say TextBox is a Object where you can Enter a value or Diplay a value and Label would act like a Tag for the Text box showing what data is represented by a TextBox or any other GUI object. For example in the below User Name and Password act as a Label for Textboxs. Where to use what is decided by the requirement.

Now that we know what a Label and a Text box is let us continue with our project.

1. Add two label and two text boxes from Toolbox and arrange them as shown below

2. Change the Text property of Label1 and Label2 to First Name: and Last Name: respectively. The labels might require a slight alignment. Now the form should like this.

3. Now let us read the value from the Labels and display Hellow message. To do this we will have to modify the code behind the Say !! Hellow Button. double click on the Button in design mode we should able to see the code behind it. Add the below line of code and remove any extra code that was created previously

MessageBox.Show("Hellow !!!! " & TextBox1.Text & vbTab _

                        & TextBox2.Text, "Welcome to TechLabs")

Complete code should look as below

By now you must be wondering previously we used MsgBox to display message and now we are using MessageBox.Show to display message box. MsgBox is VB6 kind of message box and MessageBox.Show is VB.Net method both are equally useful. 

The Method MessageBox.Show would take different parameters I will discuss a few of them, first parameter value is the display message and second is the Title of the message box. similarly there are other parameters which we can discuss as required.

So the display message is "Hellow !!!! " & TextBox1.Text & vbTab & TextBox2.Text, lets understand part by part. First part "Hellow !!! " is a static text and & is used to concatenate the string a very useful and most commonly used one. TextBox1.Text would give the value entered from the Text box and vbTab is to add a space between the Strings. Its simple until we try it.

once the code is added save it and debug. On clicking Say !!! Hellow button the bellow message should be displayed.

4. Congrats now you have successfully completed your second exercise. Tune in for more.

Learn basics of Vb.Net in 10 Simple Posts - Part 1

Hi, this is the first post where I am going to give a brief introduction on VB.Net and how to start learning it. If you know any of the programming basics or good at logic and make use of existing resources then you are at the right place to gain all the knowledge.

All the posts in the series would use Microsoft Visual Studio 2010 (VS 2010) to demonstrate the VB.Net.

Creating First Project in VS 2010

1.       Open Microsoft VS 2010

2.       Goto File->New Project (Ctrl + N shortcut)

3.       Select Windows on the left pane and Project type as Windows Forms Application from middle pane. At the bottom of the Window Name: WindowsApplication1 is given by default change it to MYFirstProject and click on Ok.

4.       Now the screen should be showing the below the Form Form1.vb is created by default by VS and the solution Explorer shows the project files such as all the VB files, forms etc

5.       Now let us Debug the project created. Go to Debug -> Start Debugging (F5 shortcut).  And the below window should pop up.

6.       Next would be changing the Form name and Form Title. To do this we need to change the property of the Form.                The Property of any object in VB.Net can be changed in two ways one is by changing the property from properties window and other by changing the property from code. Both are equally helpful and would need understanding on how to use and when to use.

The Properties window shows the various properties that an object supports. Let us understand few of them a. Name: specifies the name of the Object. b. Text:  this property is to give the display value for the object. For a form it would be the Title and for a Button it would be a Button name and so on. c.  Font :  this property is to change the font type. d. ForeColor: this property is to change the color of the text. e. BackColor: this property is to change the background color of the object. f.  Enabled: this property is used to Enable or Disable the Object g. StartPosition: this property is used to set the start position of the Form. Like the form should at the center of the screen, bottom right etc. There are still a lot of properties that we will be using let use discuss on them when we come across.

7.       Let us change the Form name from Form1 to MyFirstForm and Form Text from Form1 to Hellow Form.

8.       Let us add the first control on the Form MyFirstForm. From the ToolBox click on Button and drag the Button on the Form and place it somewhere on the form.

9.       Let us rename the Button name and Button text. Select the Button on the Form and go to Properties window. And change the Button Name property to HellowButton and Button Text to Say !! Hellow . The form should as below now

Note: The button size might not be sufficient to hold the Button Text as “Say !! Hellow” increase the button width and should work. Also for the form can be decreased.

10.   Now that we have added a button on the form let us assign functionality to the form. When we click on the Button then a popup should come up saying “Hellow”. To do this double click on the Button and it should take to you to the code screen of Form1

The above code is generated by default. We shall discuss on each of them in details in later posts. As of now let us just add the required functionality.

Add the below lines of code before End Sub.

MsgBox (“Hellow !!!” )

11.   Now let us debug the code by pressing F5 and click on Say !! Hellow Button

12.   Congrats we just completed our first VB.Net Project. Stay tuned for more.

Excel Automation - Part 1

Automation Using Excel Macro

Most of us use the Excel for one reason or the other. with Excel we can automate all the manual work of data comparison.

Here is an example of how such a automation can be done.

Requirement.
1. There are two systems with Data1 and Data2
2. Because of some reason Data1 Accounts do not match with Data2 Accounts
3. The data of both the systems are provided in a separate excel sheets
4. Only the Accounts match in both sheets and rest of the data needs to be validated

Follow the below steps to accomplish the above requirement

=>Open a New Excel sheet
=>Copy the data of First System in Sheet "Data1"
=>Copy the data of Second System in Sheet "Data2"
=> Use Alt+F11 to open MS VS
=>Insert New Module
=>Create a new Sub by name Compare

The data I am using has a sample format. And the data does not remain same always, however a little change in the code can satisfy the requirement. The sample data here has the below format

Fig 1: Data Sheets

Fig 2: Data format in Data1 Sheet

Fig 3: Data format in Data2 Sheet

Use the below code (Modify the code as required)

Sub Compare()
    Dim iRowData1, iColumnData1 As Integer
    Dim iRowData2, iColumnData2 As Integer
    Dim MatchFlg As Integer
    iRowData1 = 2
    iColumnData1 = 1
    Sheets("Data1").Select
    While (Cells(iRowData1, iColumnData1) <> "")
    AccountData1 = Cells(iRowData1, iColumnData1)
    SsnData1 = Cells(iRowData1, iColumnData1 + 1)
    Sheets("Data2").Select
    iRowData2 = 2
    iColumnData2 = 1
    MatchFlg = 0
        While (Cells(iRowData2, iColumnData2) <> "")
        AccountData2 = Cells(iRowData2, iColumnData2)
        SsnData2 = Cells(iRowData2, iColumnData2 + 2)
            If AccountData1 = AccountData2 Then
                If SsnData1 = SsnData2 Then
                MatchFlg = 1
                GoTo NextRow
                End If
            End If
        iRowData2 = iRowData2 + 1
        Wend
NextRow:
        Sheets("Data1").Select
        If MatchFlg = 0 Then
        Cells(iRowData1, iColumnData1 + 3) = "No Match Found"
        Else
        Cells(iRowData1, iColumnData1 + 3) = "Match Found"
        End If
        iRowData1 = iRowData1 + 1
    Wend
End Sub


After the Macro run the Data1 Sheet would be having the following data

This code really helped me a lot. For me it saved around three days work and I had to compare around 35K records. The Macro would run for 5 min. 

For more Stay Tuned.....

Batch File Automation - Part 4

Archive Files Using Automation - by Passing Arguments

In the previous post we had a scenario where we had archived files form one folder to Archive folder but the structure of the folder was fixed as we had hard coded the values in the script. Now let us see how we can write a code that can work on any folder structure. 

The best part of any VB Script is it can accept arguments from command line. As the script can accept the arguments from command line we can have a bat file that can call the VB.

The VB script below would accept four parameters and if four parameters not passed it would throw a message as "Not enough Arguments". The four arguments w.r.t to the position of the arguments is 

Arg(0):{DriveName, E:}, 

Arg(1):{PathName, Test_E}, 

Arg(2):{ArchivePath, E:\Test_E\Archive\}, 

Arg(3):{Prefix, Test_E}

These arguments are for the requirement if the source is E:\Test_D and Archive path is E:\Test_D\Archive and Files with prefix as Test_E*.txt would be archived.

strComputer = "."

If WScript.Arguments.Count = 4 Then

Arg_1 = cstr(WScript.Arguments.Item(0))

Arg_2 = cstr(WScript.Arguments.Item(1))

Arg_3 = cstr(WScript.Arguments.Item(2))

Arg_4 = cstr(WScript.Arguments.Item(3))

strName = strDrive & "\" & "Test\"

Else

 Wscript.Echo "Not enough Argumets"

 Wscript.Quit

End If

strDrive = Arg_1

StrPath = Arg_2

desPath = Arg_3

PrefixfName = Arg_4 & "%"

Set objWMIService = GetObject("winmgmts:\\" & strComputer)

Set objF = CreateObject("Scripting.FileSystemObject")

Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Drive = '" & _

strDrive & "' AND Path = '\\" & strPath & "\\' AND FileName Like '"& prefixfName &"'  AND Extension = 'txt'")

For Each objFile In colFiles

objF.CopyFile objFile.Name, (desPath & objFile.Filename & "_" & _

Left(objFile.CreationDate, Len(objFile.CreationDate) - 11) & "." & objFile.Extension)

Next

The above script is saved as ArchiveAny.vbs

The requirement as below

 There are two folders in two different drives 

E:\Test_E and E:\Test_E\Archive\ and files have prefix as Test_E

E:\Test_D and D:\Test_D\Archive\ and files have prefix as Test_D

The bat file has following lines

@echo off

Cscript ArchiveAny.vbs "e:" Test_E "e:\Test_E\Archive\" "E_Test" 

Cscript ArchiveAny.vbs "d:" Test_D "d:\Test_D\Archive\" "D_Test" 

This is really a good way to automate the process of automation if scheduled. We shall discuss on how such a process can be scheduled. 

Stay Tuned....

Batch File Automation Part - 3

Archive Files Using Automation

Let me discuss on a new requirement that was in my project. The requirement is as follows.

1. A particular directory would have a number of text files with different names but they all have a similar prefixes.

2. The number of files are too many which would make manual work tedious and time consuming.

3. The files must be archived to the Archive file and they should be having a time stamp appended in the file name.

4. The appended time stamp should not be the archived date but the file created date. So that this helps in checking the date  which the file was created.

5. Once the files are archived they would be deleted form the source directory.

The requirement is very simple and a VB script would be doing the entire process with few lines in it.

Consider the folder structure E:\Test\ and E:\Test\Archive.

E:\Test\ folder would have 10 files namely Text_1.txt, Text_2.txt, Text_3.txt, Text_4.txt, Text_5.txt, Text_5.txt, Text_7.txt, Text_8.txt, Text_9.txt, Text_10.txt.

All these 10 files in folder E:\Test\ to be archived to E:\Test\Archive. 

Here is a sample VB Script which would do satisfy the above requirement.

strComputer = "." ' Comuter Name

strDrive = "e:" ' Drive Letter

strPath = "Test" 'Directory Name

desName = strDrive & "\" & strPath & "\Archive\" 'Complete path of Target Directory

Set objWMIService = GetObject("winmgmts:\\" & strComputer)

Set objF = CreateObject("Scripting.FileSystemObject")

Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Drive = '" & _

strDrive & "' AND Path = '\\" & strPath & "\\' AND FileName Like 'Test%' AND Extension = 'txt'") ' this would fetch a cursor like in PLSQL with all the matches

For Each objFile In colFiles

objF.CopyFile objFile.Name, (desName & objFile.Filename & "_" & _

Left(objFile.CreationDate, Len(objFile.CreationDate) - 11) & "." & objFile.Extension)  'Move each file and rename it to archived directory

Next

Save the above code snippet in a file by name Archive.vbs and then call the vbs file from bat file. I would call my bat file Archive.bat. This bat file would have following line

@echo off
Start Archive.vbs

The bat file would call the vbs and archive all the files as required.

How ever the above code would be specific only to a specific structure. We wuld be discusing how this can be generalized to any folder structure.

Stay Tuned....... 

Batch File Automation Part-2

As discussed in the previous thread. A Batch file along with VB can used to automate a process.

Let us consider a requirement where you are suppose to place a file in a directory. The directory might have a file already with same name as the one being moved. This is very easy when we do it manually as the OS would through a error saying "file with same name already exists and do you want to replace it"  and we can easily click on yes and continue. But when we automate we should be able to move the file with out any discrepancies.

Let us see how such a automation can be done.

Steps
1. Create a Batch file that would create a New file by name "Move_Me.txt" at C:\Test
2. The batch file would then try to move the file to C:\Test\Moved using a VBS

Fig : Showing the files used in the example 

The details of the files

CreateFile.bat
This file has the following lines to


@Echo off
Echo "I will be Moved" > C:\Test\Move_Me.txt
Echo "File Move_Me.txt has been created. File would be moved now...."
pause
Start MoveFile.vbs


The above piece of code would first create a File Move_Me.txt with test as "I will be Moved".
Then the code would display message on the screen saying "File Move_Me.txt has been created. File would be moved now...." and pause would wait for user input.
Start command would kick start the MoveFile.vbs which would move the Move_Me.txt to the destination folder.

MoveFile.vbs
VB script is as below

FilePath = "C:\Test\"

Filename = "Move_Me.txt"

NewPath = "C:\Test\Moved\"

Set ScriptObj = CreateObject("Scripting.FileSystemObject")

If ScriptObj.FileExists(FilePath & Filename) Then

    If ScriptObj.FileExists(NewPath & Filename) Then

        ScriptObj.DeleteFile (NewPath & Filename)

    End If

ScriptObj.MoveFile (FilePath & Filename), (NewPath & Filename)

    End If

Set ScriptObj = Nothing

The code is hard coded with the values of file path and file name. Not a good programming practice. But for beginner this should be fine. How ever we will discuss on how parameters can be passed with a different example.

Try this out and let us discuss if any better way of doing this.

Stay tunned for upcoming topics.........