How to load data from a excel sheet into 3 tables at a time

[jenny32]

Hi,

I am new to Import Set and I wanted to load data into 3 tables from a excel sheet. The relationship between these tables is mentioned below

First table 'Test suite' has one-many relationship with second table 'Test Case' and the second table 'Test case' has one-many relationship with third table 'Test'. Here the second table 'Test Case' has a field 'test suite' which is referenced to the first table and third table 'Test' has 'test case' field which is referenced to second table.

I used import set to import the data's and created 3 transform maps.
I have written "onAfter" transform script in the second transform map where the target table is 'Test Case'.

This is my code:

answer=target.sys_id;

I have written "onBefore" transform script in the third transform map where the target table is 'Test'.

This is my code:

target.tm_test_case=answer;

Below image is a Test Suite record which contains 2 test case records.

Below image is first Test Case record which doesn't have 'Test' records listed down, which should have 2 test records as per requirement.

Below image is second Test Case record which have 'Test' records listed down.

Result should be:
I Test Suite record with 2 Test Case records and each Test case has 2 Test records to be loaded in appropriate tables.

But for me, the records are stored correctly in 'Test suite' and 'Test Case' tables

but the 'test' records are loaded only for the last 'Test Case' record. All the previous test case records doesn't contain any 'Test' records listed down in the related lists.

Can anyone please tell me what wrong i am doing here and how i can achieve this.

Chuck Tomasi Pradeep Sharma

Thanks,

Jenny

[Jon Barnes]

Your answer variable won't be carried over from the second transform to the third. So technically, I think your Test records are being created, but they are being created with an empty test case reference, so you don't see them in the related list. to confirm, go to /tm_test_list.do?sysparm_query=tm_test_caseISEMPTY. You may find your missing test records there.

You may consider consolidating this into one transform map (on the tm_test table).

And in your transform map, you would need an onBefore script.

In that script you would need to write code that would look for a matching parent 2 levels up for each test record.

If either level doesn't exist, create it.

Also, I am presuming all 3 layers of your data is flattened to one row?

Something like this, considering you would have to change the source fields I put here because I don't know what your source fields are. And you will need to probably coalesce on the Test table "Test" field.

Disclaimer, I did not test this approach at all, but I believe this idea should work. It could require some tweaks though

var tSuite = '';
var tCase = '';
// set the Test Suite
var grSuite = new GlideRecord('tm_test_suite');
grSuite.addQuery('name',source.u_suite);
grSuite.query();
if (!grSuite._next()) {
// couldn't find a match, create a new one
grSuite.newRecord();
grSuite.name = source.u_suite;
grSuite.insert();
}
if (grSuite.isValidRecord()) {
   // get the test suite here.
   tSuite = grSuite.sys_id.toString()
}

// set the Test Case
var grCase = new GlideRecord('tm_test_case');
grCase.addQuery('short_description',source.u_case);
grCase.addQuery('tm_test_suite',tSuite);
grCase.query();
if (!grCase._next()) {
// couldn't find a match, create a new one
grCase.newRecord();
grCase.short_description = source.u_case;
grCase.insert();
}
if (grCase.isValidRecord()) {
   // get the test suite here.
   tCase = grCase.sys_id.toString()
}

// set the target record to point to the correct test case.
target.tm_test_case = tCase;

[jenny32]

Hi Jonathan,

Thanks a lot for your response!!. I tried your code but for the first testcase record, there are no related test records displayed in the related lists. When i checked for the second test case record, related test records are displayed. As you said, i checked for "/tm_test_list.do?sysparm_query=tm_test_caseISEMPTY" i didn't find any test record with empty test case reference. I am still facing the same issue.

Please see the below reply i have posted the sample spreadsheet.

Thanks,

Jennifer

[Chuck Tomasi]

Hi Jennifer,

Can you provide an example of (or attach) the spreadsheet you are using as source data?

[jenny32]

Hi Chuck,

Please find the below screen shot of the spreadsheet (example spreadsheet).

Thanks,

Jennifer