How to insert records in master/detail relationship

Question

I have two tables:

OutputPackages (master)

|PackageID|

OutputItems (detail)

|ItemID|PackageID|

OutputItems has an index called 'idxPackage' set on the PackageID column. ItemID is set to auto increment.

Here's the code I'm using to insert masters/details into these tables:

//fill packages table
for i := 1 to 10 do
begin
  Package := TfPackage(dlgSummary.fcPackageForms.Forms[i]);

if Package.PackageLoaded then
begin
  with tblOutputPackages do
  begin
    Insert;
    FieldByName('PackageID').AsInteger := Package.ourNum;
    FieldByName('Description').AsString := Package.Title;
    FieldByName('Total').AsCurrency := Package.Total;
    Post;
  end;

  //fill items table
  for ii := 1 to 10 do
  begin
    Item := TfPackagedItemEdit(Package.fc.Forms[ii]);
    if Item.Activated then
    begin
      with tblOutputItems do
      begin
        Append;
        FieldByName('PackageID').AsInteger := Package.ourNum;
        FieldByName('Description').AsString := Item.Description;
        FieldByName('Comment').AsString := Item.Comment;
        FieldByName('Price').AsCurrency := Item.Price;
        Post; //this causes the primary key exception
      end;
    end;
  end;
end;

This works fine as long as I don't mess with the MasterSource/MasterFields properties in the IDE. But once I set it, and run this code I get an error that says I've got a duplicate primary key 'ItemID'.

I'm not sure what's going on - this is my first foray into master/detail, so something may be setup wrong. I'm using ComponentAce's Absolute Database for this project.

How can I get this to insert properly?

Update

Ok, I removed the primary key restraint in my db, and I see that for some reason, the autoincrement feature of the OutputItems table isn't working like I expected. Here's how the OutputItems table looks after running the above code:

ItemID|PackageID|
1     |1        |
1     |1        |
2     |2        |
2     |2        |

I still don't see why all the ItemID values aren't unique.... Any ideas?

Answer 1

Does using insert rather than append on the items table behave any differently? My guess here is that the append on the detail "sees" an empty dataset, so the auto-increment logic starts at one, the next record two, etc even though those values have already been assigned... just to a different master record.

One solution I used in the past was to create a new table named UniqueNums that persisted the next available record id number that I was going to use. As I used a number, I would lock that table, increment the value and write it back then unlock and use. This might get you around the specific issue you are having.

Answer 2

First of all the idea of autoincrement and setting ID's by code clash in my opinion. The clear path to go is to generate the key yourself in the code. Especially with multi user apps that require master/detail inserts it is hard to impossible to get the right key inserted for the detail.

So generate a ID by code. When designing the table, set the ID field to primary key but no auto increment. If I'm not mistaken Append is used for the operation.

Also you seem to iterate while the visual controls are enabled? (Item.Activated) . But the operation is a batch process by nature. For GUI performance you should consider, disabling db controls that are connected and then execute the operation. Being in the master/detail scope, this may be the issue that two other cursors not iterating as expected.