Data Driven Test Automation - New dummy object to fetch data from excel, IF-Else statement trigger span button click

Hi team,

On the website, there are 3 buttons $100, $200, $300. Each of them has it’s own test object, example, ‘span_100’, ‘span_200’, ‘span_300’. Currently my test case is fetching data from eTravel_TestData.xlxs file, in it has 3 rows of data. Automation test run cover every row of the data, currently is only clicking on the object ‘span_100’ which means the ‘$100’ button on website.

Below image illustrates what I would like to achieve.

I would like to seek help on how to create a dummy object that will fetch the price value from excel file, then based on the fetched value, perform IF-Else check. Once the price value matches, then the specific button will be clicked.

Following are the click statements that iI have on standby for IF-Else statement.
WebUI.waitForElementClickable(findTestObject(‘DataDrivenTest eTravel/Page_Company/span_100.00’), 5)

WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_100.00'))

WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_200.00'), 5)

WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_200.00'))

WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_300.00'), 5)

WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_300.00'))

Kindly seek team’s guidance on how to create a dummy object that is non existence on the website, and advice how to fetch the value for matching in IF-Else statement.

Thank you very very much team.

1 Like

Wow, such a very detailed explanation of your situation. Thank you for that.

I would suggest some of the following steps:

1. Read Price Data from Excel:

Use Katalon Studio’s built-in keywords for working with Excel files. Here’s an example using the “Excel Keywords” plugin:

GroovyString priceValue = ExcelKeywords.getCellValue('eTravel_TestData.xlsx', 'Sheet1', 'PriceColumn', currentRow) // Replace placeholders with your actual sheet name, column name, and way to identify the current row

2. Implement Conditional Click using IF-ELSE:

Groovyif (priceValue.equals('100')) {
    WebUI.waitForElementClickable(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_100.00'), 5)
    WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_100.00'))
} else if (priceValue.equals('200')) {
    WebUI.waitForElementClickable(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_200.00'), 5)
    WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_200.00'))
} else if (priceValue.equals('300')) {
    WebUI.waitForElementClickable(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_300.00'), 5)
    WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_300.00'))
} else {
    // Handle unexpected price values (optional)
    println "Unrecognized price value: " + priceValue
}

Let me know if it not work

3 Likes

Hi @Elly_Tran ,

I tried the code but katalon highlighted with some syntax. So I googled and searched in community, found some info and modified bits by bits, even then when execute the test hit error.

Please find below the error message.

---
Test Cases/DataDrivenTest eTravel FAILED.
Reason:
groovy.lang.MissingMethodException: No signature of method: static com.kms.katalon.keyword.excel.ExcelKeywords.getCellValueByIndex() is applicable for argument types: (org.apache.poi.xssf.usermodel.XSSFSheet, Integer, String) values: [Name: /xl/worksheets/sheet1.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml, ...]
Possible solutions: getCellValueByIndex(org.apache.poi.ss.usermodel.Sheet, int, int)
	at DataDrivenTest eTravel.run(DataDrivenTest eTravel:131)
	at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
	at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
	at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:448)
	at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:439)
	at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:418)
	at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:410)
	at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:285)
	at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:137)
	at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:128)
	at TempTestCase1718883040578.run(TempTestCase1718883040578.groovy:25)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

----

Also sharing with you the full code, hopefully helps.

import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import static com.kms.katalon.core.testobject.ObjectRepository.findWindowsObject
import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling as FailureHandling
import com.kms.katalon.core.testcase.TestCase as TestCase
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testng.keyword.TestNGBuiltinKeywords as TestNGKW
import com.kms.katalon.core.testobject.TestObject as TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import com.kms.katalon.core.windows.keyword.WindowsBuiltinKeywords as Windows
import internal.GlobalVariable as GlobalVariable
import org.openqa.selenium.Keys as Keys
import org.openqa.selenium.JavascriptExecutor as JavascriptExecutor
import org.openqa.selenium.WebDriver as WebDriver
import org.openqa.selenium.WebElement as WebElement
import com.kms.katalon.core.webui.driver.DriverFactory as DriverFactory
import com.kms.katalon.keyword.excel.ExcelKeywords as ExcelKeywords


WebUI.openBrowser('')

WebUI.maximizeWindow()

WebUI.navigateToUrl('https://abc.abc')

WebUI.waitForPageLoad(5, FailureHandling.STOP_ON_FAILURE)

WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/input_User Name_username'), 
    5)

WebUI.setText(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/input_User Name_username'), 
    'abc.abc.com')

WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/input_Password_password'), 
    5)

WebUI.setEncryptedText(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/input_Password_password'), 
    'sd3GT36GH53JUJ787HHwwmiO88VU2kkLXcpN1W/pbAUWu')

WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/button_SIGN IN'), 
    5)

WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/button_SIGN IN'))

WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/button_Proceed'), 
    5)

WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/button_Proceed'), 
    FailureHandling.OPTIONAL)

WebUI.waitForPageLoad(10, FailureHandling.STOP_ON_FAILURE)

//Some of the objects fetch data from excel file and loop until all rows in the test data file has been completed successfully

for (def rowNum = 1; rowNum <= findTestData('etRAVEL_TestData').getRowNumbers(); rowNum++) {

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/a_PRODUCTS'), 
        5)

    WebUI.mouseOver(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/a_PRODUCTS'))

    WebUI.waitForElementClickable(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/a_Create New Quote'), 
        5)

    WebUI.mouseOver(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/a_Create New Quote'), 
        FailureHandling.STOP_ON_FAILURE)

    WebUI.enhancedClick(findTestObject('DataDrivenTest eTravel/Page_Company/a_Create New Quote'))

    WebUI.waitForElementPresent(findTestObject('DataDrivenTest eTravel/Page_Company/input_Intermediary Name_mat-input-10 - Copy'), 
        5)

    WebUI.setText(findTestObject('DataDrivenTest eTravel/Page_Company/input_Intermediary Name_mat-input-10 - Copy'), 
        findTestData('eTravel_TestData').getValue(1, rowNum))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_CREATE NEW - Copy'), 
        5)

    WebUI.click(findTestObject('DataDrivenTest eTravel/Page_Company/span_CREATE NEW - Copy'))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/input_Destination_mat-input-12 - Copy'), 
        5)

    WebUI.setText(findTestObject('DataDrivenTest eTravel/Page_Company/input_Destination_mat-input-12 - Copy'), 
        findTestData('eTravel_TestData').getValue(2, rowNum))

    WebUI.sendKeys(findTestObject('DataDrivenTest eTravel/Page_Company/input_Destination_mat-input-12 - Copy'), 
        Keys.chord(Keys.TAB))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/button_Annual'), 
        5)

    WebUI.click(findTestObject('DataDrivenTest eTravel/Page_Company/button_Annual'))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/input_Group_next - 1'), 
        5)

    WebUI.click(findTestObject('DataDrivenTest eTravel/Page_Company/input_Group_next - 1'))

	//Following section is to fetch price from test data into a dummy object (non existence object in website)
	//Then value in dummy object based on IF-ELSE statement, meet condition will click the respective button

	// Test data excel file
	String excelFilePath = 'C:/Users/emlearn/Desktop/eTravel_TestData.xlsx'
	String sheetName = 'Sheet1'
	
	// Get the workbook and sheet
	def workbook = ExcelKeywords.getWorkbook(excelFilePath)
	def sheet = ExcelKeywords.getExcelSheet(workbook, sheetName)

	// Get the cell value
	def priceValue = ExcelKeywords.getCellValueByIndex(sheet, rowNum, 'price')
			
	if (priceValue.equals('100')) {
		WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_100'), 5)
		WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_100'))
	} 
	if (priceValue.equals('200')) {
		WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_200'), 5)
		WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_200'))
	} 	if (priceValue.equals('300')) {
		WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_300'), 5)
		WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_300'))
	} 
	else {
		// Handle unexpected price values 
		println "Unrecognized price value: " + priceValue
	}
	
	
    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/input_Group_next - 2'), 
        5)

    WebUI.click(findTestObject('DataDrivenTest eTravel/Page_Company/input_Group_next - 2'))

    WebUI.waitForElementPresent(findTestObject('DataDrivenTest eTravel/Page_Company/input_NRICFIN Number_finNo'), 
        5)

    WebUI.setText(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/input_NRICFIN Number_finNo'), 
        findTestData('eTravel_TestData').getValue(4, rowNum))

    WebUI.waitForElementPresent(findTestObject('DataDrivenTest eTravel/Page_Company/input_Given Name_givenName'), 
        5)

    WebUI.setText(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/input_Given Name_givenName'), 
        findTestData('eTravel_TestData').getValue(5, rowNum))

    WebUI.waitForElementPresent(findTestObject('DataDrivenTest eTravel/Page_Company/input_Family Name_familyName'), 
        5)

    WebUI.setText(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/input_Family Name_familyName'), 
        findTestData('eTravel_TestData').getValue(6, rowNum))

    WebUI.waitForElementPresent(findTestObject('DataDrivenTest eTravel/Page_Company/input_Date of Birth_mat-input-15'), 
        5)

    WebUI.setText(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/input_Date of Birth_mat-input-15'), 
        findTestData('eTravel_TestData').getValue(7, rowNum))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/button_MALE_1'), 
        5)

    WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/button_MALE_1'))

    WebUI.waitForElementPresent(findTestObject('DataDrivenTest eTravel/Page_Company/input_Postal Code_postalCode'), 
        5)

    WebUI.setText(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/input_Postal Code_postalCode'), 
        findTestData('eTravel_TestData').getValue(8, rowNum))

    WebUI.waitForElementPresent(findTestObject('DataDrivenTest eTravel/Page_Company/input_Phone Number_phoneNo'), 
        5)

    WebUI.setText(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/input_Phone Number_phoneNo'), 
        findTestData('eTravel_TestData').getValue(9, rowNum))

    WebUI.waitForElementPresent(findTestObject('DataDrivenTest eTravel/Page_Company/input_Email_email'), 
        5)

    WebUI.setText(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/input_Email_email'), 
        findTestData('eTravel_TestData').getValue(10, rowNum))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/input_Group_next - 3'), 
        5)

    WebUI.click(findTestObject('DataDrivenTest eTravel/Page_Company/input_Group_next - 3'))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/label_I accept the Terms and Conditions sta_fef253'), 
        5)

    WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/label_I accept the Terms and Conditions sta_fef253'))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/input_Group_next - 4'), 
        5)

    WebUI.click(findTestObject('DataDrivenTest eTravel/Page_Company/input_Group_next - 4'))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/input_Group_next - 5'), 
        5)

    WebUI.click(findTestObject('DataDrivenTest eTravel/Page_Company/input_Group_next - 5'))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/button_OK'), 
        5)

    WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/button_OK'))

    WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/button_View Quotation'), 
        5)

    WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/button_View Quotation'))

    WebUI.waitForPageLoad(10, FailureHandling.STOP_ON_FAILURE)
}

WebUI.mouseOver(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/svg_FAQ_svg-inline--fa fa-user'))

WebUI.mouseOver(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/button_Logout'))

WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/button_Logout'))

WebUI.closeBrowser()

Hi there,

Thank you very much for your topic. Please note that it may take a little while before a member of our community or from Katalon team responds to you.

Thanks!

@emlearn

Please use the “code formatting syntax” (enclose lines of code with a pair of triple back ticks) .

This will make the quoted code much readable.

2 Likes

The log contains the following message which tells you what’s wrong.

The message tells that the getCellValueByIndex method wants an integer value as the 3rd argument, but you gave a String.

In your script, there is a statement:

That’s your mistake.

1 Like

Well noted. Thank you @kazurayam :upside_down_face:

Thank you @kazurayam for spotting my mistake. I have corrected it, now the automation test ran without error but the button is not clicked. Nothing in self-healing as well.

Below is info extracted from Console.

2024-06-21 08:41:58.153 DEBUG .DataDrivenTest eTravel - 17: excelFilePath = "C:/Users/emlearn/Desktop/eTravel_TestData.xlsx"
2024-06-21 08:41:58.161 DEBUG .DataDrivenTest eTravel - 18: sheetName = "Sheet1"
2024-06-21 08:41:58.165 DEBUG .DataDrivenTest eTravel - 19: workbook = getWorkbook(excelFilePath)
2024-06-21 08:41:58.276 DEBUG .DataDrivenTest eTravel - 20: sheet = getExcelSheet(workbook, sheetName)
2024-06-21 08:41:58.281 DEBUG .DataDrivenTest eTravel - 21: priceValue = getCellValueByIndex(sheet, rowNum, 3)
2024-06-21 08:41:58.306 DEBUG .DataDrivenTest eTravel - 22: if (priceValue.equals("100"))
2024-06-21 08:41:58.343 DEBUG .DataDrivenTest eTravel - 23: if (priceValue.equals("200"))
2024-06-21 08:41:58.348 DEBUG .DataDrivenTest eTravel - 24: if (priceValue.equals("300"))
2024-06-21 08:41:58.355 DEBUG .DataDrivenTest eTravel - 25: else
2024-06-21 08:41:58.359 DEBUG .DataDrivenTest eTravel - 1: println("Unrecognized price value: " + priceValue)
Unrecognized price value: S0416647G

Below is the modified script.

	//Following section is to fetch price from test data into a dummy object (non existence object in website)
	//Then value in dummy object based on IF-ELSE statement, meet condition will click the respective button

	// Test data excel file
	String excelFilePath = 'C:/Users/emlearn/Desktop/eTravel_TestData.xlsx'
	String sheetName = 'Sheet1'
	
	// Get the workbook and sheet
	def workbook = ExcelKeywords.getWorkbook(excelFilePath)
	def sheet = ExcelKeywords.getExcelSheet(workbook, sheetName)

	// Get the cell value
	def priceValue = ExcelKeywords.getCellValueByIndex(sheet, rowNum, 3)
			
	if (priceValue.equals('100')) {
		WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_100'), 5)
		WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_100'))
	} 
	if (priceValue.equals('200')) {
		WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_200'), 5)
		WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_200'))
	} 	if (priceValue.equals('300')) {
		WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_300'), 5)
		WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_300'))
	} 
	else {
		// Handle unexpected price values 
		println "Unrecognized price value: " + priceValue
	}

1 Like

This message clearly tells that your script has something wrong.

What’s wrong?

You shouldn’t ask it to others.

You have the code in your hand. You can debug it. But others can’t.

Why don’t you debug it yourself before asking a new question to others?

1 Like

Counting columns in Excel starts at zero (0), so what you actually want is:

// Get the cell value
def priceValue = ExcelKeywords.getCellValueByIndex(sheet, rowNum, 2)

Edit: depending on your level of expertise, you might try to use a parameterized test object and not have the “if” tree at all. You might use the Excel value within the pathway for your object and at the same time use the Excel value in your code.
xpath = "//span[text()=${indexAmount}]"

and then:

WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_Index', ['indexAmount' : ExcelKeywords.getCellValueByIndex(sheet, rowNum, 2), 5)
WebUI.click(findTestObject('DataDrivenTest eTravel/Page_Company/span_Index', ['indexAmount' : ExcelKeywords.getCellValueByIndex(sheet, rowNum, 2))

No “if” tree needed, but then, I also don’t know your HTML layout.

1 Like

Hi @grylion54, thank you for the advice and tips. I will explore the parametrization later. In the meantime am thankful that your ealier solution has helped to fix the issue. Am actually beginner level.

If else and variable - Product Forums / Katalon Studio - Katalon Community

Here’s my revised script that is working fine now. The respective buttons are clicked when condition is met.

	// Fetching price value from eTravel_TestData.xlsx, fulfil condition, respective button will be clicked
	
	if (findTestData('eTravel_TestData').getValue(3, rowNum) == "100") {
		
		WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_100.00'),
			5)

		WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_100.00'))
	} else if (findTestData('eTravel_TestData').getValue(3, rowNum) == "200") {
		
		WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_200.00'),
			5)

		WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_200.00'))
	} else if (findTestData('eTravel_TestData').getValue(3, rowNum) == "300") {
		
		WebUI.waitForElementClickable(findTestObject('DataDrivenTest eTravel/Page_Company/span_300.00'),
			5)

		WebUI.click(findTestObject('Object Repository/DataDrivenTest eTravel/Page_Company/span_300.00'))
	}

Thank you so very much :pray: :upside_down_face:

2 Likes

Hi @emlearn, :wave:

Glad to see that your code is working fine now. If possible, could you mark one of the comments in this thread as a solution :white_check_mark: so that other members - especially those who may have the same issue as you did - can benefit from it? Thanks! :+1:

This topic was automatically closed after 14 hours. New replies are no longer allowed.