In an earlier post, we looked at mapping data from nested repeating tables using DBXL v2.2. In that post, the SQL table associated with the main repeating form group used an identity column that was set to auto-increment for the key. DBXL, however, currently follows a delete and reshred model which would mean that our identity would change every time the document was updated.

While this may not be an issue for some scenarios, there could be cases where you would prefer your identity to remain a bit more stable. One way to work around this is to use the DocId (generated by DBXL) for your key -- after all, that number remains the same for the life of the document. However, in our initial example, we have mapped a repeating group for the node path for our main table (Projects). We could have one to umpteen projects for a given DocId!

I am sure there are many creative solutions to this problem, but only one occurred to me, and that is what I'd like to walk through in this post. What if we modify our Projects table to use a composite key consisting of the DocId and a ProjectId that identifies each project group in the document? We could do away with auto-incrementing completely. The foreign key on the Issues table would relate back to the DocId and ProjectId, and then, upon re-shredding, none of our identites will change.

First, let's modify our form schema:

We've added a ProjectId field. The datatype is integer, and it has a default value of count(../preceding-sibling::my:Project/my:ProjectId) + 1 which will give us a running count of our groups -- the field's value will be 1 for the first group inserted, 2 for the second, and so forth.

Now we need to modify our database. Add a column called ReportId (we'll map the DocId to it) to both the Projects and the Issues tables. In the Projects table, remove the Identity specification from the ProjectId column, as we'll now be mapping our new ProjectId field to that column.

We need to set ReportId and ProjectId as a composite key for the Projects table. While your table is open in design view, right click anywhere on the canvas and select Indexes/Keys:

When the Indexes/Keys window opens, you can select the button next to the Columns field in the General section and select the columns you wish to use for your composite key:

Don't forget to modify the foreign key relationship with the issues table:

To our mapping! We need to make a few changes -- we are going to map ResourceId to the DocId and ProjectId to ProjectId. When you select your columns from the Database Tree, your keys will be marked as such.

Don't forget -- special DBXL fields are available to you for mapping in the Schema Tree:

Next, we adjust our Issues mapping to reflect our changes, first setting up our keys:

And, after we add the rest of the Issues columns, our full map looks like this:

Let's cut to the fun part. We save our new mapping, and open our form, fill it out and submit it:

When you look at the tables, you can see that our DocId is being used for ReportId and the new ProjectId field is providing a ProjectId:

And, in Issues, we are relating all of a project's issues back to it, as well as relating the issues to the report:

For greater database integrity, you could use the same approach as we used for Projects to provide the Issues group in the form an identity, and create an IssuesId column in your table. Your primary key for your Issues table could be a composite key consisting of ReportId, ProjectId and IssueId.

We can now reopen our form that we just submitted and make some changes, as well as adding additional rows:

Once again, we race to the database, happily clapping our hands, to make sure that all our identities remain unchanged and our new identities are what we would expect: