One Excel Datasource and Multiple Test cases using Specific Test Case ID within source

Question

Hi, I have the following excel source file

Test Case ID	CustomerID
test001	Cust 001
test002	Cust 002
test003	Cust 002

I want to setup Testcases for e.g. Test002 that is picks up the value of "Cust 002" (which changes from day to day) and check the response via assertion that its correct.

how do I parse the excel file and find "test002" and input that value to that testcase only?

I would envisage the layout to be:

- Datasource

- "test001"REST step

- "test002" REST step

- "test003" REST step

- datasource loop

Looks like Groovy script is needed but there could be 100 testcases and associated data in the source file.

Thanks Dave

other examples with no answers: https://community.smartbear.com/t5/ReadyAPI-Questions/How-to-use-DataSource-and-DataSource-Loop-in-multiple-test-cases/m-p/150457

https://community.smartbear.com/t5/ReadyAPI-Questions/One-datasource-Excel-and-Multiple-Test-Cases/m-p/146569?search-action-id=42105450853&search-result-uid=146569

chrisadams · Answer

Hi Dave,&nbsp;You have Datasource's which I guess means you have the licensed version of SoapUI.&nbsp;Datasources and datasource loops are for data driven (aka functional) tests whereby you exercise one or more service calls using the datasource values as parameters for the service call(s).&nbsp;The most simplest example might look like&nbsp;Assuming the data source content looks like this....Customer IDCustomer Name1John2Paul3George4Ringo&nbsp;The Datasource would look like this....&nbsp;The service call being exercised then needs to pick up the Customer Id to use in this iteration.&nbsp; E.g.&nbsp;The $ syntax above reads the current customerId value from the datasource.You can type this directly, or use the nifty Get Data option.&nbsp; Click in the Value cell and ensure the cursor is there.&nbsp; Sometimes you have to double-click.&nbsp; Then right-mouse click to open the context menu.&nbsp; Select Get Data.The Get Data screen allows you to navigate around your Workspace to select the value you want to pull in.&nbsp; E.g.&nbsp;If you've followed this far, you'll see that using the datasource and 'Get Data' you can exercise one service call with almost unlimited values.&nbsp; You don't need separate service call test steps per row in the datasource.&nbsp;You could then extend the test to call additional services to form sort of use case.&nbsp; E.g.&nbsp; Get Customer, Amend Customer, Customer places order.&nbsp; Each step would be called once per iteration...&nbsp;&nbsp;IMHO, the data-driven tests are the best feature of ReadyAPI and are definitely worth persevering with.&nbsp;Here is a link to SMartBears own guide...&nbsp;https://support.smartbear.com/readyapi/docs/testing/data-driven/index.html?sbsearch=Data%20Driven%20Tests&nbsp;

chrisadams · Answer

Ah, the assertions are hard-coded into each test step?&nbsp;You could added the expected results to your datasouce; take out the hard-coded assertions from the test step and create a groovy script to perform the assertion on every iteration.&nbsp;E.g.&nbsp; Modify the datasource to include the params for each test and the expected result.&nbsp;Customer IdCustomer NameCredit LimitSaleExpected Credit Check Result1John1000800PASS2Paul9001000FAIL3George800200PASS4Ringo700900FAIL&nbsp;Update the Datasource to reference the new columns....&nbsp;Remove the hard-coded assertions from Test Steps 001, 002, 003 etc.&nbsp; No screenshot here.&nbsp;Add a groovy script step.....&nbsp;Open the Assertion Groovy Step and create you check.&nbsp; Here's a pseudo-like example.// Let's get the params and expected result for our test.
def customerId = context.expand( '${Customer Data Source#customerId}' )
def customerName = context.expand( '${Customer Data Source#customerName}' )
def creditLimit = context.expand( '${Customer Data Source#Credit Limit}' )
def sale = context.expand( '${Customer Data Source#Sale}' )
def expectedCreditCheckResult = context.expand( '${Customer Data Source#Expected Credit Check Result}' )

log.info("Testing Customer ${customerId} ${customerName}.  Credit Limit ${creditLimit}.  Sale ${sale}.  Expected Result ${expectedCreditCheckResult}.");

// Get the result of the credit check from the test step.
// Big assumption here, so this is just an example, but assuming the response from the SOAP Request contains the result.
// Use Get Data to find the path to the variable of interest...
def actualResult = context.expand( '${SOAP Test Request#CreditCheckResult}' );

// Assert!
assert (expectedCreditCheckResult == actualResult);&nbsp;This assertion will use the data and expected result in current row.&nbsp; No need for a test step per row because of assertions.&nbsp; I think this achieves what you're asking for.&nbsp;Going futher...Worth noting, that if the assertion fails, the test stops.&nbsp; You might only be 3 rows in out of a thousand and it's stopped.&nbsp;I personally don't like this, so I go further.... Instead of the assertion, I check the result and use a datasink step to report this test row.&nbsp; E.g.&nbsp;&nbsp;Then configure the datasink...&nbsp; Note how we pull the values from the datasource and the step that performs our check.&nbsp;Lastly, update the Assertion step and make it return the result instead of the assertion.....// Let's get the params and expected result for our test.
def customerId = context.expand( '${Customer Data Source#customerId}' )
def customerName = context.expand( '${Customer Data Source#customerName}' )
def creditLimit = context.expand( '${Customer Data Source#Credit Limit}' )
def sale = context.expand( '${Customer Data Source#Sale}' )
def expectedCreditCheckResult = context.expand( '${Customer Data Source#Expected Credit Check Result}' )

log.info("Testing Customer ${customerId} ${customerName}.  Credit Limit ${creditLimit}.  Sale ${sale}.  Expected Result ${expectedCreditCheckResult}.");

// Get the result of the credit check from the test step.
// Big assumption here, so this is just an example, but assuming the response from the SOAP Request contains the result.
// Use Get Data to find the path to the variable of interest...
def actualResult = context.expand( '${SOAP Test Request#CreditCheckResult}' );

// Assert!
//assert (expectedCreditCheckResult == actualResult);

// Let's not assert.  Just return the result.
if (expectedCreditCheckResult == actualResult) {
	
	return "PASS";
	
} else {
	
	return "FAIL";
	
}

&nbsp;So, for each row in the datasource, we can call our service.&nbsp; We then run a check (or assertion) in the following step.&nbsp; Finally, we can report out the result.&nbsp; The result file will be the same as the datasource, but with the result of the check tagged on the end of each row.&nbsp;This is actually my typical approach.&nbsp; I can then import the text file into Excel and format it nicely.&nbsp;&nbsp;

richie · Answer

Hey davecoleman,I never bothered responding to this as ChrisAdams was doing a cracking job and i couldnt have even come close to adding anything, but i just wanted to mention something in regards to your hardcoded assertions.Obviously i dont know what youre asserting against in your responses, however if the testdata tests are changing everyday im guessing the expected results are changing everyday.  With this in mind, i just wanted to highlight you can parameterise your assertions within the GUI, rather than hardcode them.  I do this as much as possible (along with some other "tricks" (i.e. setup scripts, project/testsuite/testcase level properties, setup testsuites, reusable code, parameterisation) when creating a ReadyAPI project so i can lift and shift my ReadyAPI project to point at completely different environments with completely different data without having to change anything in project).  If you know the assertion values for each testcase, you could include these in your Excel Datasource as an additional field for each test case.  You could then parameterise your assertion to point to these values, removing the need to change hardcoded values.Ok, that was all i wanted to mention,Cheers,Rich

davecoleman · Answer

I haven't returned to this work since my last update&nbsp;sonya_m&nbsp;. The approach is sound around the assertion comparison and I may have more questions at that time on completion of that.

davecoleman · Answer

Working through a Proof of concept i have come up with the below solution:- A groovy script goes to each testcase as per the testcase Identifier in the data source.&nbsp;Datasource excelGroovy to decide on which step to go to:import com.eviware.soapui.support.XmlHolder

def testcaseNo = context.expand('${Data Source#TestcaseId}')

log.info (testcaseNo)

if (testcaseNo=="test001")
{
log.info ("go to test step: " + testcaseNo)
testRunner.gotoStepByName (testcaseNo)
}
else if (testcaseNo=="test002")
{
log.info ("go to test step: " + testcaseNo)
testRunner.gotoStepByName (testcaseNo)
}
else if (testcaseNo=="test003")
{
log.info ("go to test step: " + testcaseNo)
testRunner.gotoStepByName (testcaseNo)
}
else if (testcaseNo=="test004")
{
log.info ("go to test step: " + testcaseNo)
testRunner.gotoStepByName (testcaseNo)
}
else if (testcaseNo=="test005")
{
log.info ("go to test step: " + testcaseNo)
testRunner.gotoStepByName (testcaseNo)
}&nbsp;Groovy after each step to move to the loop and start again on next row:import com.eviware.soapui.support.XmlHolder

testRunner.gotoStepByName ("Data Source Loop")
log.info ("moved to the Data Source Loop")It will work for myself but there may be better solutions out there.&nbsp;&nbsp;

Forum Discussion

One Excel Datasource and Multiple Test cases using Specific Test Case ID within source

Related Content

Groovy Automated DataSource Loopers

One datasource (Excel) and Multiple Test Cases

Code for groovy datasource

Watching multiple Requests sent during a Datasource Loop

Load records from Datasource[Excel] into SOAP Request as multiple XML nodes

Recent Discussions

Assert Step Not Executing All Assertions

Include external files (XML) for JDBC connection

Accessing database connection string from groovy test step

JDBC database connection pooling

JDBC "Some connection settings are not specified"

Customer ID	Customer Name
1	John
2	Paul
3	George
4	Ringo