Appending a row to an outer table and adding values to a nested table; appending values to a nested table

This time we'll append a row to the outer table (Tasks) and add a value to an inner (nested) table, Assigned To: (Task Information).

1. Rerun your Import data file exercise11_train1.csv (where Train1 is your login name). This will return your Events record to its original condition.
2. Add the following Columns and data to the Import data file exercise14_train1.csv (where Train1 is your login name):

Column Name (Row 1)	Record 1 (Row 2)
irn	[The IRN noted at Step 4 in Exercise 11].
TasDescription_tab(+)	Task 3
TasPersonAssignedToRef_nesttab(+:1).NamLast	[Your login name]
TasPersonAssignedToRef_nesttab(+:2).NamLast	Train18

1. Save the Import data file as exercise14_train1.csv (where Train1 is your login name).
2. Perform a Typical Import.

   If all goes well, the Tasks tab of your Events record will appear similar to:

   

   Here we have appended a row to the outer table (Tasks) and added two rows to the nested table, Assigned To: (Task Information).

   All is not as it seems however.

3. Rerun your Import data file exercise11_train1.csv (where Train1 is your login name). This will return your Events record to its original condition.

   The Tasks tab of your Events record should appear similar to:

   

4. For task 2, delete Description: (Task Information):

   

5. Rerun exercise14_train1.csv (where Train1 is your login name).

   The result is not as expected.

   Our Import data file specifies that a row is added to Description: (Task Information), which is a table, and this has happened, but there was only one row filled in the Description: (Task Information) table and so the second row was used, not the third as we intended:

   

6. Select the third row in the Tasks table.

   Our Import data file also specifies that a row is appended to the Tasks outer table and two rows are added to the Assigned To nested table, and this also has been done:

   

   The Import Tool does not know how the various tables and nested tables are related.

   Consider that a task (Task 3) comprises various pieces of information stored in various fields, Description and Assigned To for instance. Description and Assigned To are separate tables. When we add a task with values in Description and Assigned To, a row is added to the two tables (Description and Assigned To). But, if for some reason the Assigned To table has two rows but Description only has one row, when we tell the Import tool to add a row to each table, Assigned To will now have three rows and Description will now have two rows and the details that make up our task are now out of sync with each other.

   The only way to ensure that related columns are kept together with any of the update modifiers (+, -, =) is to include every relevant row in your Import data file, even if there is no data to be imported for the column. With an append (using the + update modifier), we also use the Group attribute to keep rows together.

   In this example, our Import data file would need to include all of these column names if we are to ensure that the data for each of our tasks is kept together:

Column Name (Row 1)	Record 1 (Row 2)
irn	[The IRN noted at Step 4 in Exercise 11].
TasDescription_tab(+ group='1')	Task 3
TasPersonAssignedToRef_nesttab(+ group='1':1).NamLast	[Your login name]
TasPersonAssignedToRef_nesttab(+ group='1':2).NamLast	Train18
TasTaskAssignerRef_tab(+ group='1').NamLast
TasCommencementDate0(+ group='1')
TasStartNotifyDate0(+ group='1')
TasCompleted_tab(+ group='1')
TasTimeTakenOnTask_tab(+ group='1')
TasCompletionDate0(+ group='1')
TasEndNotifyDate0(+ group='1')
TasCompletionNotifyRef_nesttab(+ group='1':1).NamLast

1. Rerun your Import data file exercise11_train1.csv (where Train1 is your login name). This will return your Events record to its original condition.
2. For task 2, delete Description: (Task Information) and save your Events record:

   

3. Modify exercise14_train1.csv (where Train1 is your login name) to include all the columns listed in the table in Step 8.

   Save the file and perform a Typical Import.

   This time the Tasks tab displays as expected:

   

4. To append a value to the inner table, Assigned To: (Task Information) for Task 3:

   Modify the Import data file exercise14_train1.csv (where Train1 is your login name) as follows:

   Column Name (Row 1)	Record 1 (Row 2)
   irn	[The IRN noted at Step 4 in Exercise 11].
   TasPersonAssignedToRef_nesttab(3:+).NamLast	Train17

5. Save the file and perform a Typical import:

   

   Here we have specified that a row is appended to the Assigned To: (Task Information) nested table for the third task listed in the Tasks table.

6. Rerun your Import data file exercise11_train1.csv (where Train1 is your login name). This will return your Events record to its original condition.
7. Rerun the Import data file exercise14_train1.csv (where Train1 is your login name).