Report: How to merge data with same primary key

mxlim

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

DaveT

Hi Nicholas,

Did you try

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

Also be careful - even though they have the same code they may not be the same customer details

kine

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

If you want something like this, you need to have

a) complex primary key (like "Company name","No.")
b) you need to add pre/postfix to the "No." which will be different for each company and use these pre/postfix in all fields where the No. is used... (in related tables...)

David_Singleton

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

Just create a new table as a copy of customer, but with a new field added to the primary key. Since you never will insert records in this table to the database, it does not need to be in the customers license range.

mxlim

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

iori

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

Hi Nicholas,
when in Integer - OnPreDataItem()
you write these code:
FOR Count := 1 TO ARRAYLEN(tmpCust) DO BEGIN
tmpCust[Count].RESET;
SETRANGE(Number,1,tmpCust[Count].COUNT);
END;

the SETRANGE is at last set the Number range to the last Company'Count.

iori

Hi Nicolas,
May change these code :
<!--
{
FOR Count := 1 TO ARRAYLEN(tmpCust) DO BEGIN
tmpCust[Count].RESET;
SETRANGE(Number,1,tmpCust[Count].COUNT);
END;
}
FOR ArrayIndex :=1 TO ARRAYLEN(tmpCust) DO BEGIN
tmpCust[ArrayIndex].RESET
TotalRecordCount +=tmpCust[ArrayIndex].COUNT;
END;
SETRANGE(Number,1, TotalRecordCount );
-->


and Change these code:
Integer - OnAfterGetRecord()
<!--
{
FOR Count := 1 TO ARRAYLEN(tmpCust) DO BEGIN
IF Number = 1 THEN
tmpCust[Count].FIND('-')
ELSE
tmpCust[Count].NEXT;
END;
}
//u should write code to get Current ArrayIndex at first.
//and FirstInArrayIndex:is First Record in One CustArray.
IF FirstInArrayIndex THEN
tmpCust[CurrArrayIndex].FIND('-')
ELSE
tmpCust[CurrArrayIndex].NEXT;
-->