" The Excel buffer already exist identification field and value Row No =2 Column No = 1" error why and how solve

Business Central/NAV Developers Forum
1

Hi guys i have problem i grouping report to item ledger entry table data item as following :

item no quantity cost amount(actual)

ax 5 3000

az 2 4000

grouping by item no until now is successfully.

I try to print to excel sheet using excel buffer table so that i write following code :

item ledger entry -on after get record trigger
IF PrintToExcel THEN
MakeExcelDataBody;

CreateExcelBook() function

ExcelBuf.CreateBook;
ExcelBuf.CreateSheet(Text002,Text001,COMPANYNAME,USERID);
ExcelBuf.GiveUserControl;
ERROR(’’);

MakeExcelInfo() function

ExcelBuf.SetUseInfoSheed;
ExcelBuf.AddInfoColumn(FORMAT(Text005),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(COMPANYNAME,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text007),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(FORMAT(Text001),FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text006),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(REPORT::“TotalQuantity-Cost”,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text008),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(USERID,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text009),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(TODAY,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;

ExcelBuf.AddInfoColumn(“Item Ledger Entry”.GETFILTER(“Item No.”),FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.ClearNewRow;
MakeExcelDataHeader;

MakeExcelDataHeader() function

ExcelBuf.AddColumn(“Item Ledger Entry”.FIELDCAPTION(“Item No.”),FALSE,’’,TRUE,FALSE,TRUE,’’);
ExcelBuf.AddColumn(“Item Ledger Entry”.FIELDCAPTION(Quantity),FALSE,’’,TRUE,FALSE,TRUE,’’);
ExcelBuf.AddColumn(“Item Ledger Entry”.FIELDCAPTION(“Cost Amount (Actual)”),FALSE,’’,TRUE,FALSE,TRUE,’’);

MakeExcelBody()

ExcelBuf.NewRow;
ExcelBuf.AddColumn(“Item Ledger Entry”.“Item No.”,FALSE,’’,FALSE,FALSE,FALSE,’@’);
ExcelBuf.AddColumn(“Item Ledger Entry”.Quantity,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.AddColumn(“Item Ledger Entry”.“Cost Amount (Actual)”,FALSE,’’,FALSE,FALSE,FALSE,’’);

Report - onprereport()

itemFilter := “Item Ledger Entry”.GETFILTERS;
PeriodText := “Item Ledger Entry”.GETFILTER(“Posting Date”);

IF PrintToExcel THEN
MakeExcelInfo;

Report-onpostreport()
IF PrintToExcel THEN
CreateExcelbook;

Then when i run report i found error tell me as following :

" The Excel buffer already exist identification field and value Row No =2 Column No = 1"

How i solve this problem.

thanks

2

You can search the forum with error.

it means that row no. is not incrementing in your code…

3

how i make this increment can you show me please

4

Hi

Look at the folllowing posts

http://dynamicsuser.net/forums/t/54227.aspx

http://dynamicsuser.net/forums/p/25998/140880.aspx

You will get an idea how to write a code

5

Thanks i solved the problem by deleting all records from excel buffer table 370

Now i face another problem when i export data it show records but not show grouping as following :

report show in normal way as

item qty cost amount actual

opc 5 2000

but in excel show details as following

opc 1 400

opc 1 400

opc 2 800

opc 1 400

how i solve this if possible

what i need to show grouping in excel as report

thanks

6

Did you set Temporary property to yes for ExcelBuf record variable?

7

yes but not affect

8

This is not for grouping issue.

this is for records inserting into table.

You can write ExcelBuffer.DELETEALL; in the begining the code also.

For grouping, you need to gropu your report as in normal reports and write code in Groupfooter/header sections…

9

I using

excel buffer .delete all;

in pre data item first then no result and after get record triggers also not result .

but no benefit i made as report number 6 in object designer and his name trial balance

why this not give me result as in report.

my report grouping data based on item no and successfully in that but

Not successfully to group data in excel why

10

Is data is grouping in Report ?

Have you try to write code on Report Section ???

11

in report grouping already found and report work affectedly but in excel not work grouping

in which section i write and what i write to do that in excel if possible

thanks

12

I think you need to add code on Report Section for the same.

13

what this code i added to section

14

thank you very much

it solved as you tell me by putting code in section of report

thanks

15

Welcome…

16

what code u have added can u tell me as i’m getting the same issue