Relevant Codes

A Test Development Resource for HP QuickTest Professional.

Introduction to Parameterization with QTP’s Local DataTable

November 23, 2009 · 42 comments

Parameterization is a process in which parameters, or variables are substituted for hard-coded values which help perform the same operations on the AUT with multiple sets of data. In other words, in QTP, parameterization helps automation developers test an application with different data from a single action (or multiple actions) through multiple iterations. Because the data changes each iteration, it must be stored in a data-pool, or in multiple variables. This data-pool can be QTP’s DataTable, an Excel Spreadsheet (external to QTP), Access DB, SQL Server, or any database of your choice. It can also come from several Environment variables. This topic will describe parameterization with QTP’s DataTable.

The trick to successful parameterization is cycling around a process. For example, consider a flight booking/checkout process. You would first login to the application, find and select the flight of your choice, make the purchase, and in the end view the confirmation.

If you were to parameterize the above process, instead of ending your script at the confirmation page, you would end it at the Find Flight page. This is because, when the next iteration executes, it is already at the correct page which can enable the script to start all over again at the expected point. In other words, our process should look like below:

Parameterization with DataTable
Before we start with the actual parameterization, we must first understand how data will be retrieved from the DataTable and the settings that we need to make in order to cover all the rows. In our example, we will test a few UserName and Passwords in the HP/Mercury’s Demo AUT.

Below, I have created 2 columns in the Local (Action1) DataTable- UserName & Password. You can create columns by simply double-clicking the Column heading:

Create Columns in DataTable

Create Columns in DataTable

Next, I will create 4 rows with different sets of data:

Add Data to Columns

Add Data to Columns

Below is the syntax to retrieve data from the DataTable’s local sheet:

'If retrieving data from Local DataTable (Action1, Action2,.. ActionN)
DataTable("ColumnName", dtLocalSheet)
 
'If retrieving data from Global DataTable (dtGlobalSheet)
DataTable("ColumnName", dtGlobalSheet)

Therefore, we can now create 2 statements using the syntax above to retrieve our user name and password:

DataTable("UserName", dtLocalSheet)
DataTable("Password", dtLocalSheet)

Before we create our script, we must first tell QTP to run the test from all rows in the Local DataTable. In Keyword view, right-click Action1 then click Action Call Properties:

Right-Click Action1

Right-Click Action1

In the Run Tab of Action Call Properties window, select “Run on all rows”. A Dialog will popup mentioning what we discussed in the beginning of this post, about ending our iteration where the first step in our process begins.

Action Call Properties Window

Action Call Properties Window

Close the dialog, then click OK to save the settings we made under Action Call Properties. We are now ready to create our script which will take data from each row and test each UserName/Password combination. Below will be the steps in our process:

1. Launch application
2. Enter UserName/Password from DataTable and click Login
3. Verify if the Find Flights page appears
4. If Find Flights page appears, iteration passed.
5. Return to Home Page and start Step #2
'Step 1
If DataTable.LocalSheet.GetCurrentRow = 1 Then
        SystemUtil.Run "iexplore.exe", "http://newtours.demoaut.com"
End If

After executing the above code, or manually launching the application, execute the below steps:

'Parameterization Block:
With Browser("title:=Welcome: Mercury Tours", "index:=0")
	'Step 2
        'Parameter 1: UserName
	.WebEdit("name:=userName").Set Datatable("UserName", dtLocalSheet)
        'Parameter 2: Password
	.WebEdit("name:=password").Set Datatable("Password", dtLocalSheet)
	.Image("name:=login").Click
End With	
 
'Step 3
'If Find a Flight page appears, go back to Home
If Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Exist(10) Then
	'Step 4
	Reporter.ReportEvent micPass, "Iteration " & DataTable.LocalSheet.GetCurrentRow, _
		"UserName: " & Datatable("UserName", dtLocalSheet) & " is valid"
	Browser("title:=Find a Flight: Mercury Tours:", "index:=0").Link("text:=Home").Click
Else
        'Step 5
        'Else, fail the iteration and return to the Home page
	Reporter.ReportEvent micFail, "Iteration " & DataTable.LocalSheet.GetCurrentRow, _
		"UserName: " & Datatable("UserName", dtLocalSheet) & " is invalid"
	Browser("title:=Sign-on: Mercury Tours", "index:=0").Link("text:=Home").Click
End If

Once the above script ends its execution, your test results should look like this:

Execution Results

Execution Results

This topic covered a very high-level process of parameterization, but I hope it gives you a good idea how this process can be enhanced and adapted to your application. The next topic in Parameterization will cover Excel Spreadsheets and a few tricks that we can use for successfully driving different sets of data to our AUT.

If you have any questions, please ask them in the comments section. If your query is confidential, please use the Contact Form to send me an e-mail instead.

{ 1 trackback }

QTP: Parameterization with Excel | Relevant Codes by Anshoo Arora
February 5, 2010 at 12:12 am

{ 41 comments… read them below or add one }

1 Ranjini November 24, 2009 at 1:45 am

Hi Anshoo,
For iterating the Datatable values is it enough if we change the Data table iterations in Action Call Properties dialog ? or we need to do change the setting under the File —>Setting —>Run –> data table iteration ?
Why we have the data table iterations setting at 2 differnet places.
i am sure that the questions is very basic but still i want to understand the reason behind it ? kindly clarify

Thanks,
Ranjini

Reply

2 Anonymous November 24, 2009 at 5:33 am

hi
ranjini
Setting under File-setting iteration is for particular test. It is global setting.
If we change in Action call property it is for Particular action not for entire test.So it is Local setting. Hence we have 2 sheets in Data table.

Reply

3 Ranjini November 24, 2009 at 11:29 am

Hi,
Thanks for the response.
If i need to parametrize my test both by Global and Local datatable sheet then Dt iterations setting has to be changed in both palces (Action properties and Test Setting)?

Thanks,
Ranjini

Reply

4 Anshoo Arora November 26, 2009 at 5:50 pm

Hi Ranjini,

The concept with the Global DataTable is that, the “entire test” will run as many number of iterations as there are in the Global Table if its option is set to “Run on All Rows”.

That means, if you have:

2 rows in Global Table (Run on All Rows Option Selected)
Action1: 4 Rows
Action2: 3 Rows

The total number of times the entire test will cycle is twice. That means, Action1 will have 8 iterations, and Action2 will have 6 iterations in all.

Also, the setting for dtGlobalSheet (GlobalTable) is in the test settings, whereas for the actions, its in the Action Call Properties.

Reply

5 Ranjini November 30, 2009 at 6:39 am

Hi Anoosha,
Very clear explanation. Thanks Verymuch

Regards,
Ranjini

Reply

6 Anshoo Arora November 30, 2009 at 1:18 pm

Thank you :)

Reply

7 Ranjini November 24, 2009 at 1:49 am

Hi Anshoo,
As you thought us “data-pool can be QTP’s DataTable, an Excel Spreadsheet (external to QTP)”
which is the best way to maintain/use it ? either QTP’s data table or external spreadsheet ?

Thanks,
Ranjini.

Reply

8 Anshoo Arora November 24, 2009 at 10:24 am

Good question, Ranjini.

I think its a matter of comfort level that makes people choose between the two. Which one are you the most comfortable with? If you use DataTable, you must be well versed with the methods of the DataTable. If you use an Excel spreadsheet, then you must be well versed with Excel’s COM Interface. That’s the usage component. I almost always use Excel, because I feel I can do more, and create my code with much better performance as I would with the DataTable.

Maintenance is subjective, here. You can use an Excel spreadsheet and import it to QTP on runtime. You can even copy-paste the changes you made to an Excel spreadsheet to the DataTable. Similarly, you can export a DataTable sheet to Excel. So, I think this one is a little hard to answer – I guess a good thing to say in regards to this is a person’s comfort-level between the two.

Reply

9 Mahesh Upadhyay December 4, 2009 at 1:26 am

Hi Anshoo,

I am unable to see the call action properties in my Keyword view, i am using QTP 10.0 evaluation version, can you please tell me the substitute.

Thanks
Mahesh

Reply

10 Anshoo Arora December 8, 2009 at 9:48 pm

Hi Mahesh,

I am still in the process of downloading/installing QTP 10.0. As soon as its complete, I will check it and get back to you! This will also give me a chance to test the framework and find any defects with the new version – which seems quite buggy to me.

Reply

11 Anshoo Arora December 16, 2009 at 2:39 pm

Mahesh,

Here is what you need to do to get to the Action’s Call Properties:

1. In Keyword View, Click on the Drop-Down list right below the test name.
This list generally contains Test Flow, Reusable Actions -> Action1, Action2 etc.
2. Click on Test Flow
3. Now you will have all actions in a hierarchical format
4. Right-Click on any action.

Once you perform the above steps, you will see “Action Call Properties” whenever you right-click on any Action.

Reply

12 Mahesh Upadhyay December 17, 2009 at 5:47 am

Hi Anshoo,

Thanks for the help, i am able to see “Action Call Properties” in Keyboard View when i follow your given steps.

Thanks
Mahesh

Reply

13 Jayachandra January 8, 2010 at 1:04 am

HI Anshoo can you give me brief Description regarding datapool is it jst like a external file with which we are going to work? Can you be more precisely.. Thanks in advance..

Jayachandra

Reply

14 Anshoo Arora January 8, 2010 at 1:27 am

A Datapool can be anything that you pull data from for testing. This can be an Excel File, an Access/SQL database, a text file, XML file etc.

For example, RelevantCodes[1]One uses an Excel File as its data pool.

Reply

15 Jayachandra January 8, 2010 at 1:23 am

In the above example i got some doubt regarding after log of wid valid id and where is the next statment to run 2nd iteration.. thnxs.. anshoo

Reply

16 Anshoo Arora January 8, 2010 at 1:29 am

When we use the DataTable, we refer to the Action’s “Action Call Properties” to determine how many iterations we would like to be run. If you see this image from this article, you will notice that I have created a setting that will enable the action to make use of all the 4 rows.

Once we make that setting, the action will run the test on all 4 rows, thus, it will run 4 iterations for each username/password combination.

Reply

17 Jayachandra January 8, 2010 at 1:34 am

thnxs alot anshoo can you explain how to test the broken links .

thanks in advance

Reply

18 Jayachandra January 8, 2010 at 3:59 am

Hi Anshoo,

Can you post me how to work on Borken Links Please i m looking for some snippets.

Thanks in Advance

Th

Reply

19 Anshoo Arora January 8, 2010 at 10:02 am

Hi Jayachandra,

For testing broken links, please see the following link.

Reply

20 Jayachandra January 19, 2010 at 5:33 am

HI Anshoo,

The broken links which u provided site is automation , i am looking for some hard coded thng by which we can test in qtp

Reply

21 Anshoo Arora January 20, 2010 at 9:16 am

I checked that solution, and its for QTP. What kind of solution you’re looking for? If its possible we can try to work something out.

Reply

22 Pradeep Babu. R January 30, 2010 at 10:39 am

Hi Anshoo..
All the documents here are useful to me. Thanks for everything..
Could you please direct me how to do the follwing,

I need to do one framework for my project. My basic need is to,
1) Update an excel sheet with the order number received from the last page of my application.
– Could you please give a sample script to get the value from GUI and update an excel sheet placed in some location.

2) I need to run the script from QC and I need to update the order number from the last page with PASS/FAIL status.
– Give a Basic idea how to connect, Run the particular test case in QC from QTP and a simple script/ program to run and update the status into QC.

Since I am very new to QTP , I need a basic idea about this, I dont have any mentor, So it is taking too much time to read and there is no one to help when any steps fail. I searched in many places and none of the website is clear and no screenshots or step by step procedure is available. If you help me in this it would be more thankful.. :-)

Thanks in advance :)

Reply

23 Anshoo Arora February 2, 2010 at 9:38 pm

Hi Pradeep,

I am currently working on an article that explains working with Excel, and I think that might help you for #1. I will try to release that article soon.

When running from QC, all you need to do is create a connection to it through QTP. Once that’s done, all the results will be automatically exported to QC once the test finishes its execution.

Reply

24 Pradeep Babu. R February 13, 2010 at 4:17 am

Hi Anshoo ! Could you please give some input on this. I am in need to do this as soon as possible.
—-
Updating an excel sheet with the order number received from the last page of my application.
– Could you please give a sample script to get the value from GUI and update an excel sheet placed in some location.

– Give a Basic idea how to connect, Run the particular test case in QC from QTP and a simple script/ program to run and update the status into QC.

Reply

25 Anshoo Arora February 14, 2010 at 2:00 am

Hi Pradeep,

To retrieve the data from any application, you can use .GetROProperty and to write to Excel, you can refer to this article which shows code snippets to retrieve data, but reverting the code can be used to write back to Excel. You can also find some code to write to Excel here: http://relevantcodes.com/descriptive-programming-dp-concepts-3-ordinal-identifiers-demo/#comment-2236

Not sure what you mean by #2. Once you upload the scripts to QC, you can add them to the Test Lab and execute them.

Reply

26 vartika February 11, 2010 at 10:55 am

How can we pass “local datatable” parameters in Actions.

Reply

27 Anshoo Arora February 11, 2010 at 2:31 pm

Hi Vartika,

Do you mean to retrieve stored data in the DataTable from within Actions? If yes, consider Action1 with its Local DataTable and the following fields:

FirstName | LastName | Age | Gender | SSN
John      | Smith    | 20  | Male   | XX
Mary      | Smith    | 22  | Female | XX

To retrieve the data from one of the columns, you would use the following code:

sFirstName = DataTable("FirstName", dtLocalSheet)
sLastName = DataTable("LastName", dtLocalSheet)
iAge = DataTable("Age", dtLocalSheet)
sGender = DataTable("Gender", dtLocalSheet)
lngSSN = DataTable("SSN", dtLocalSheet)

Reply

28 Anonymous February 17, 2010 at 2:45 pm

How do we pass datable items as parameters to another action.
say Action1 has firstname, lastname and you want to use those in Action2.

Reply

29 Anshoo Arora February 19, 2010 at 2:01 am

Hello,

I think the entire process is documented in the help file, and a quick-overview of it could be found here: http://aboutqtp10.blogspot.com/2009/11/quicktest-professional-passing.html

I have written a draft on this topic as well, and should be published in the coming few days..

Reply

30 Puneet February 18, 2010 at 3:39 pm

Hi Anshoo,

I have created a new patient from the datatable and now I want the provider to log into the system and take the new patient and verify it and submit it through the application and then verify it that it has gone through to the website. If I have created mutliple patients then it should store in a datatable and use those values to verify each patient and submit. Once submitted then another user can go the website and verify the patient have been successfully listed in the website. Do you know what is the best approach will be?

Thanks
Puneet

Reply

31 Anshoo Arora February 19, 2010 at 2:12 am

Puneet,

I think it depends how you are creating the new patients and verifying them. The 2 instances are described below:

Creating and Verifying patients within the same iteration
This is quite simple and can be done by the use of variables. Example:

sName = sName_FromTheApplicationUsingGetROProperty
iAge = iAge_FromTheApplicationUsingGetROProperty

and the variables sName and iAge can be used when testing for verification throgh the Provider.

Creating all first. Verify all later.
This is where you would need to store the patient information in a Table, either a DataTable or Excel spreadsheet. I generally use Excel Spreadsheets, but with the DataTable, the process is quite similar. You would need to create the columns in the DataTable that would contain the patient’s information.

For example, if the patient has the following information to be written to the DataTable, then appropriate columns would have to be created:

Name | Age | Alergies | Weight

When creating patients, information would be written to these fields:

DataTable("Name", dtLocalSheet) = sName_FromTheApplicationUsingGetROProperty
DataTable("Age", dtLocalSheet) = iAge_FromTheApplicationUsingGetROProperty

and written to the instance where Provider is verifying information:

NameField = DataTable("Name", dtLocalSheet)
AgeField = DataTable("Age", dtLocalSheet)

Reply

32 Puneet February 19, 2010 at 4:02 pm

One thing I noticed is that I am getting the values from the datatable and if the value is selected from row 6 then the GetROProperty will store the value in the datatable in row 6 instead of from the beginning of the row and when I ran the following script then I will see it duing the run time but otherwise I don’t see it after the run time. Below is I am trying to store SSN. Thanks for your help.

For x = 1 to maxRows
datatable.SetCurrentRow(ssnRowNum)
SwfWindow(“HA – You are currently”).SwfEdit(“txtNewSSN”).Type DataTable(“SSN”, dtGlobalSheet)
enterSSN = SwfWindow(“HA – You are currently”).SwfEdit(“txtNewSSN”).GetROProperty(“text”)
SwfWindow(“HA – You are currently”).SwfObject(“btnSSN”).Click 6,10

If SwfWindow(“ePDHA – You are currently”).Dialog(“HA – [Confirm]_2″).Exist Then
SwfWindow(“HA – You are currently”).Dialog(“HA – [Confirm]_2″).WinButton(“OK”).Click
ssnRowNum = ssnRowNum + 1
End if

If SwfWindow(“HA – You are currently”).SwfEdit(“txtNewSSN”).GetVisibleText = “xxx-xx-xxxx” Then
Exit for
End If
next

num = enterSSN
SwfWindow(“HA – You are currently”).SwfEdit(“txtVerifySSN”).Set num
SwfWindow(“HA – You are currently”).SwfObject(“btnVerifySSN”).FireEvent “Click”

DataTable(“SSN_VERIFY”, dtLocalSheet) = enterSSN

Reply

33 Anshoo Arora February 24, 2010 at 8:09 am

Hi Puneet,

Your observation is correct, but don’t you think it will be easier to correspond the test-time and run-time data because they both land on the same rows? I will be confused if the data for Row 6 ends up on some other DataTable row..

but otherwise I don’t see it after the run time.

It should be in the Run Time DataTable when you open the Results? Is it not there?

I saw your code, and it seems correct as long as the value ssnRowNum points to the correct value of the DataTable..

Reply

34 Puneet February 19, 2010 at 4:07 pm

I have another issue and that at the end of the application I am trying to move over the patient data to a website, before I can do it I have to change the website URL in the notepad which is located in the system.util unde the C: drive and I try to record this but I was not able to record it where it opens the file and change the URL in the notepad and save it. Am I suppose to do something different before recording?

Thanks
Puneet

Reply

35 Anshoo Arora February 24, 2010 at 8:12 am

Puneet,

Using the FileSystemObject (FSO) Object would be a better approach in this situation as compared to record and playback. I would recommend you to download all chapters of Scripting QuickTest Professional and read Chapter 14 for FSO. Good luck :)

Reply

36 Puneet February 22, 2010 at 12:20 am

Hi Anshoo,

Thanks for the code. I was able to add this to my code and use the Excel sheet to store my values. I am trying to open this webpage but I am unable to do you with this code. Any to program the QTP to click on the warning message?

Browser(“title:=Certificate Error: Navigation”).Page(“title:=Certificate Error: Navigation”).Link(“name:=Continue to this website”).FireEvent “Click”

Thanks
Puneet

Reply

37 Anshoo Arora February 24, 2010 at 8:17 am

Puneet,

Are you trying to navigate ahead from a page like this one: http://helpdesk.ugent.be/img/cert_error1.png

If yes, then I suppose the following code should work:

Browser("title:=Certificate Error.*").Link("innertext:=Continue.*").Click

Reply

38 Puneet February 22, 2010 at 10:45 am

Hi Anshoo,

I have increment SSN by 1, is there any way to force QTP not to wirte it to the results. Because if I have increment by 1 10+ times then it is odd to have too much info in the test reuslts.

Thanks
Puneet

Reply

39 Anshoo Arora February 24, 2010 at 8:23 am

Puneet,

I think it depends how you are doing this. Are you using a Reporter.ReportEvent statement, or an Output CheckPoint or some other technique?

Reply

40 Puneet February 22, 2010 at 10:47 am

Hi Anshoo,

I am unable to get QTP recognize the security and click on the link in the browser.

Browser(“Certificate Error: Navigation_2″).Page(“Certificate Error: Navigation”).Link(“Continue to this website”).Click

Thanks
Puneet

Reply

41 Anshoo Arora February 24, 2010 at 8:24 am

Puneet,

You can try the below code:

Browser("title:=Certificate Error.*").Link("innertext:=Continue.*").Click

Reply

Leave a Comment

You can use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="">

Previous post:

Next post: