Report: How to merge data with same primary key

if not Tempcustomer.get( customer."no." ) then
   begin
     tempcustomer := customer;
     tempcustomer.insert;
   end;

mxlim wrote:

Hi all,

I'm trying to create a report which consolidates data from multiple databases and multiple companies (from each database). The table is the Customer table. I have created a virtual data item in the report which pulls in the data into a temporary Customer table by calling a stored procedure which returns the data.

The problem is that in the Customer table for several companies, they have the same primary key (ie "No."). So when I run the report it prints error saying that "the xxx primary key already exists".

I have already tried to create an array of temporary Customer tables (where each company's Customer table is stored in tmpCust[ct]). I have also tried to create multiple temporary tables where each variable stores one company. But all return the same error.

Anyone have any ideas? Much appreciated.

Regards,
Nicholas

mxlim wrote:

Now I can get each company's Customer data into a tmpCust[] array. The problem is when I try to print the report, it only prints the Customer data for the last company read. Here is my code:

Integer - OnPreDataItem()
<Start SQL Connections>
WHILE NOT ADORecordset.EOF DO BEGIN
  currCompany := ADORecordset.Fields.Item('CName').Value;
  IF currCompany <> prevCompany THEN BEGIN
    tmpCustIndex := tmpCustIndex + 1;
    tmpCust[tmpCustIndex].DELETEALL;
  END;
  tmpCust[tmpCustIndex].INIT;
  tmpCust[tmpCustIndex]."No." := ADORecordset.Fields.Item('No').Value;
  tmpCust[tmpCustIndex].Name := ADORecordset.Fields.Item('Name').Value;
  tmpCust[tmpCustIndex].INSERT;
  prevCompany := currCompany;
  ADORecordset.MoveNext;
END;

FOR Count := 1 TO ARRAYLEN(tmpCust) DO BEGIN
  tmpCust[Count].RESET;
  SETRANGE(Number,1,tmpCust[Count].COUNT);
END;

Integer - OnAfterGetRecord()
FOR Count := 1 TO ARRAYLEN(tmpCust) DO BEGIN
  IF Number = 1 THEN
    tmpCust[Count].FIND('-')
  ELSE
    tmpCust[Count].NEXT;
END;

And in the Section Designer for Integer, Body I put 2 TextBox with SourceExpr set to - tmpCust[tmpCustIndex]."No." and tmpCust[tmpCustIndex].Name

I know somewhere in the code there is something wrong but I've been playing around for a long time and still haven't figured out what's wrong. Any help is much appreciated. Thanks

Regards,
Nicholas