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

[jenny32]

ctomasi   Any idea on how to achieve this requirement.

[Chuck Tomasi]

Hi Jennifer,

Here are my first impressions - note that I have never done a single import in to three tables at once before. To do this as one import would take a fair amount of scripting with onBefore and onAfter scripts plus arrays to keep track of the sys_ids created. It might be easier to do this at three separate imports to get the test suites imported, then do the test cases, and finally the tests.

[jenny32]

Hi Chuck,

I didn't get the part where you mentioned to "create three separate imports for test suite, test case and test".

Could you please explain it again.

Thanks,

Jennifer

[Chuck Tomasi]

Sorry for the confusion Jennifer. One import set for test suite, then run another separate import set for test cases, and finally one for tests. import sets can be set to be dependent so they get loaded in the right order (e.g. the test suite records are there waiting and ready in the target table when the test cases get imported.)

[stopper09e]

Hi Chuck,

If your client is using one spreadsheet and following your suggested train of thought, break it into 3 separate files for import, what do you suggest be used as the coalesce field during the transform?   What field should be unique on each of the separate spreadsheets (test suite, test cases and tests?

Thank you.