Load one excel for multiple tables

[Lean S]

Hi All,

I am working on import sets and transform maps. I wanted to load one excel sheet for loading in multiple tables.

This is for loading into test case and related test case steps instance

Under test plan I have to load test cases as first step and second I have to map to test case -> test case step

so from one excel sheet 

test plan -> I have load test cases

test case -> to load its related test case steps.

How can achieve using one excel sheet. Any specific coding needed for this.

Thanks,

Lean S

[asifnoor]

Hello @Lean S 

Without scripting, I don't think it is possible to do the import into multiple tables from a single excel file.

You can create multiple transform maps and in onBefore of your 1st transform map, ignore if any of the 2nd table entries exist. in this case, if test case step number present while test cases are importing. 

Similarly in 2nd transform onBefore ignore if any of the test case entries are there. That way your 1st transform map will import only test cases and 2nd transform map will take care of importing test case step numbers.

Mark the comment as a correct answer and helpful if this helps to solve the problem.

[Lean S]

Hi Asifnoor,

Now I have a excel sheet which contains both data related test case and test case steps so I would need to do following:

1st transform map : Test Case table (here I will give test plan as reference and load accordingly)

this is Ok now. then

2nd Transform map: Test Case Step table (in this test case created in 1st transform map  should be mapped to current test case record and add test case step under it (which I will loading through transform map 2) 

Please help me with script or method to achieve this hierachy

Thanks,

Lean S

[asifnoor]

Hi

In the 2nd transform map, click on the test case column in your 2nd xls.

Click on the test case column and click on source script checkbox enabled. Then in that add this code

var gr = new GlideRecord("your_test_case_table");//metnion test case table
gr.addQuery("test_case_id",source.u_test_case);//check the field names correctly
gr.query();
if(gr.next()) {
  source.u_test_case=gr.sys_id.toString();
}

NOTE: check the column name and table name correctly.

Mark the comment as a correct answer and helpful if it helps.

[Lean S]

Hi Asifnoor,

Thanks for valuable response. I have a little query here, I was able to load in to Test Case table (Step 1) Now a record will be created with TC12345 , I have used above code in source script for Step 2 (2nd Transform map) for creating a new record under TC12345 (test case step table) but record is getting created but I unable attach it to TC12345. How we can get this, do I miss any thing.

Kindly help.

Thanks,

Lean S

[asifnoor]

Hi,

If your test case step table is sn_test_management_step, then in this table there is a column named test_version which links to the test case.

In your test step excel sheet rows.. is there a column which mentions TC12345. If yes, then column has to be mapped with test_version column in your target table. 

And also open that field in transform map, enable source checkbox and add this code

var gr = new GlideRecord("sn_test_management_test_version");//metnion test case table
gr.addQuery("short_description",source.u_test_case);////Assuming u_test_case in xls has the test number for test step rows.
gr.query();
if(gr.next()) {
  source.u_test_case=gr.sys_id.toString(); //Assuming u_test_case in xls has the test number for test step rows.
}

Mark the comment as  a correct answer and helpful if it helps to solve the problem.