Export to excel

Horse06

Hi Expert,

I have some questons regarding the export data to excel file. The processingOnly is set to YES. If the report is based on one table, it works perfectly and I have done several of these reports.
But if I have two tables which are linked together, it does not work. Below is one line of the code

EnterCell(Row, 10, "OrderStatus.PendingOrder", FALSE, FALSE, FALSE);

This line is added to the sales header AFTERGETRECORDS(), and the sales header and Orderstatus are linked together, but I don't know why it does not work.

Thanks in advance!

DigiTecKid

What is the error? Can you more of the code you're using for this?

Horse06

Thank you! Here is the error:
You have specified an unknown variable.
OrderStatus.PendingOrder
Define the variable under 'Global/C/AL symbols'

Since Sales header and OrderStatus are two dataitem, they are linked together. Do I still have to declare the variable?

Thank you!

DigiTecKid

In the report dataitems, is the Name of the order status table, "OrderStatus"? If they are linked, then trying to use the OrderStatus.PendingOrder in the "Sales Header" OnAfterGetRecord won't work. It won't have gotten the record until the OrderStatus dataitem, OnAfterGetRecord.

Horse06

Thank you! Yes, OrderStatus is the table. But I do not quite understand what you are saying. Could you explain it in details.

In the sales Header of AftergetRecords, I have the code like this:
Row := Row + 1;
EnterCell(Row, 1, "Sell-to Customer No.", FALSE, FALSE, FALSE);
EnterCell(Row, 2, "Ship-to Name", FALSE, FALSE, FALSE);
EnterCell(Row, 3, "Ship-to Address", FALSE, FALSE, FALSE);
EnterCell(Row, 4, "Ship-to Address 2", FALSE, FALSE, FALSE);
EnterCell(Row, 5, "Ship-to City", FALSE, FALSE, FALSE);
EnterCell(Row, 6, "Ship-to Contact", FALSE, FALSE, FALSE);
EnterCell(Row, 7, "Ship-to Post Code", FALSE, FALSE, FALSE);
EnterCell(Row, 8, "Ship-to County", FALSE, FALSE, FALSE);
EnterCell(Row, 9, "OrderStatus.PendingOrder", FALSE, FALSE, FALSE);
EnterCell(Row, 10, "Ship-to Country Code", FALSE, FALSE, FALSE);RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));

Thank you!

Horse06

Thank you! I understand what you are saying now. But the data type of my pendingorder is boolean. How to retrieve the record?
It looks like all the data has no header. How to add the header to each column? Appreciate it!

DigiTecKid

First create a variable, OrderStatus:

Name: OrderStatus
DataType: Record
Subtype: Order Status

(Don't put the space in the variable name so you don't have to enclose it in double quotes everytime you use it)


Now you have to either do a 'filter and find' or a 'get' of the associated Order Status record.
OrderStatus.SETRANGE("Document No.", "No.");
IF OrderStatus.FINDFIRST THEN
Or
IF OrderStatus.GET("Document Type", "No.") THEN

The exact code will depend on what fields in the Order Status table are related to what fields in the Sales Header table.


Then change this line of code from
EnterCell(Row, 9, "OrderStatus.PendingOrder", FALSE, FALSE, FALSE);
to
EnterCell(Row, 9, OrderStatus.PendingOrder, FALSE, FALSE, FALSE);

(take out the double quotes from around the variable name and the field name all as one)

If the Order Status field name has a space in it: Pending Order
Then it will need to be enclosed in double quotes:
EnterCell(Row, 9, OrderStatus."Pending Order", FALSE, FALSE, FALSE);


As for the column headers in the Sales Header OnPreDataitem put code like this...
EnterCell(Row, 9, 'Sell-to Customer No.', FALSE, FALSE, FALSE);
EnterCell(Row, 9, 'Ship-to Name', FALSE, FALSE, FALSE);
etc...
Find out which of the EnterCell parameters is for making the column bold and make it TRUE.

Horse06

Thank you for your help! But I have an error as below:

Too many key fields were specified, So orderstatus could no be retrieved. The number of fields in the primary key is 1.

Below is the code:

Row := Row + 1;
EnterCell(Row, 1, "Sell-to Customer No.", FALSE, FALSE, FALSE);
EnterCell(Row, 2, "Ship-to Name", FALSE, FALSE, FALSE);
EnterCell(Row, 3, "Ship-to Address", FALSE, FALSE, FALSE);
EnterCell(Row, 4, "Ship-to Address 2", FALSE, FALSE, FALSE);
EnterCell(Row, 5, "Ship-to City", FALSE, FALSE, FALSE);
EnterCell(Row, 6, "Ship-to Contact", FALSE, FALSE, FALSE);
EnterCell(Row, 7, "Ship-to Post Code", FALSE, FALSE, FALSE);
EnterCell(Row, 8, "Ship-to County", FALSE, FALSE, FALSE);
IF OrderStatus.GET("Document Type", "No.") THEN
EnterCell(Row, 9, OrderStatus.Pendingstatus, FALSE, FALSE, FALSE);
EnterCell(Row, 10, "Ship-to Country Code", FALSE, FALSE, FALSE);RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));

Also, I cannot add the column header and it only retrieves one, for example Customer NO. Thank you!

DigiTecKid

What field is the primary key for the Order Status table?
What are the fields that relate the Order Status table to the Sales Header table?

Too many key fields were specified, So orderstatus could no be retrieved. The number of fields in the primary key is 1.
This error means that the GET function used with the OrderStatus variable uses too many fields.
OrderStatus.GET("Document Type", "No.")
The Order Status table has only a 1 field primary key so calling the GET with two generates the error.

Also, I cannot add the column header and it only retrieves one, for example Customer NO.

I didn't write those example code lines well, both of them write their data into column 9.

EnterCell(Row, 9, 'Sell-to Customer No.', FALSE, FALSE, FALSE);
EnterCell(Row, 9, 'Ship-to Name', FALSE, FALSE, FALSE);

The code should be:
Row += 1;
EnterCell(Row, 1, 'Sell-to Customer No.', FALSE, FALSE, FALSE);
EnterCell(Row, 2, 'Ship-to Name', FALSE, FALSE, FALSE);
EnterCell(Row, 3, 'Ship-to Address', FALSE, FALSE, FALSE);
EnterCell(Row, 4, 'Ship-to Address 2', FALSE, FALSE, FALSE);
EnterCell(Row, 5, 'Ship-to City', FALSE, FALSE, FALSE);
EnterCell(Row, 6, 'Ship-to Contact', FALSE, FALSE, FALSE);
EnterCell(Row, 7, 'Ship-to Post Code', FALSE, FALSE, FALSE);
EnterCell(Row, 8, 'Ship-to County', FALSE, FALSE, FALSE);
EnterCell(Row, 9, 'Pending Order', FALSE, FALSE, FALSE);
EnterCell(Row, 10, 'Ship-to Country Code', FALSE, FALSE, FALSE);

Remember to determine which of the EnterCell function parameters makes the cell bold and change it to TRUE.

Horse06

Thank you! After fixing the code and it works. But I will tested the column header code later.

I have another question.

I have tested the datatype which is text and it works perfectly, and I have tested a dataype which is boolean,
but have an error as below:
Status is one field with data type boolean

IF OrderStatus.GET("Sales Header"."No.") THEN
IF OrderStatus.Status = TRUE THEN
Status := 'Complete';
EnterCell(Row, 10, Status, FALSE, FALSE, FALSE);

Error:

Type conversion is not possbiel because if of the operators contains an invalid type.
text :=option

If the data type is option, how to do that? Appreciate it!

Horse06

I have figured out those with the data type of option, but the boolean still does not work and has the same error.

Horse06

I tried to code adding row header to the column,
But I have the error as below:

The Excel Buffer already exists. Identificaiton fields and values: Row No.='1', Column No.='1'

Here is the code:

TotalRecNo :="Sales Header".COUNTAPPROX;
TotalRecNo :=OrderStatus.COUNTAPPROX;
RecNo :=0;
TempExcelBuffer.DELETEALL;
CLEAR(TempExcelBuffer);
EnterCell(1, 1, Text000, TRUE, TRUE, FALSE);
Row += 1;
EnterCell(Row, 1, 'Sell-to Customer No.', FALSE, FALSE, FALSE);
EnterCell(Row, 2, 'Ship-to Name', FALSE, FALSE, FALSE);
EnterCell(Row, 3, 'Ship-to Address', FALSE, FALSE, FALSE);
EnterCell(Row, 4, 'Ship-to Address 2', FALSE, FALSE, FALSE);
EnterCell(Row, 5, 'Ship-to City', FALSE, FALSE, FALSE);
EnterCell(Row, 6, 'Ship-to Contact', FALSE, FALSE, FALSE);
EnterCell(Row, 7, 'Ship-to Post Code', FALSE, FALSE, FALSE);
EnterCell(Row, 8, 'Ship-to County', FALSE, FALSE, FALSE);
EnterCell(Row, 9, 'Pending Order', FALSE, FALSE, FALSE);
EnterCell(Row, 10, 'Ship-to Country Code', FALSE, FALSE, FALSE);

Row := 10;

Appreciate it!!!

DigiTecKid

I believe the problem is in your code here:

EnterCell(1, 1, Text000, TRUE, TRUE, FALSE);
Row += 1;
EnterCell(Row, 1, 'Sell-to Customer No.', FALSE, FALSE, FALSE);

The variable Row is probably zero at this point in the code. So adding 1 to it only makes it 1. You just entered a cell record in row one so trying to add another errors. Try this code...


Row += 1;
EnterCell(Row, 1, Text000, TRUE, TRUE, FALSE);
Row += 1;
EnterCell(Row, 1, 'Sell-to Customer No.', FALSE, FALSE, FALSE);


Use "Row += 1;" after every EnterCell function call and make sure to use the "Row" variable in the function call instead of just an integer and it will keep track of what row you're on for you.

DigiTecKid

Horse06 wrote:

Thank you! After fixing the code and it works. But I will tested the column header code later.

I have another question.

I have tested the datatype which is text and it works perfectly, and I have tested a dataype which is boolean,
but have an error as below:
Status is one field with data type boolean

IF OrderStatus.GET("Sales Header"."No.") THEN
IF OrderStatus.Status = TRUE THEN
Status := 'Complete';
EnterCell(Row, 10, Status, FALSE, FALSE, FALSE);

Error:

Type conversion is not possbiel because if of the operators contains an invalid type.
text :=option

If the data type is option, how to do that? Appreciate it!

To assign a value to a field of datatype option the best way is to use the "scope of" notation:
Status := Status::Complete;

DigiTecKid

Horse06 wrote:

I have figured out those with the data type of option, but the boolean still does not work and has the same error.

To assign a value to a boolean field just use the constant TRUE or FALSE. For example:

OrderStatus.Status := TRUE;

Horse06

Thank you! Using OrderStatus.Status = TRUE then and it works this time, but I don't know why it didn't work yesterday.

I have also tested the column header and it works, but the headers are not in one row and each header goes down the second line like a ladder pattern.
How to fix it? Appreciate it!

Horse06

Hi DigiTecKid,

How to export date since there is a datatype conversion issue?

Code:
EnterCell(Row, 3, "Order Date", FALSE, FALSE, FALSE);

Error:
Type conversion is not possible because 1 of the operators contains an invalid type.
text:=Date

Horse06

Hi, I have figured out the column header and have fixed them. But I cannot work out the date. How to export the date? Appreciate it!

DigiTecKid

Yeah, sorry, that's what I get for posting at 4am in the morning.

When I put:

Row += 1;
EnterCell(Row, 1, Text000, TRUE, TRUE, FALSE);
Row += 1;
EnterCell(Row, 1, 'Sell-to Customer No.', FALSE, FALSE, FALSE);

It should have been

Row += 1;
Col += 1;
EnterCell(Row, Col, Text000, TRUE, TRUE, FALSE);
Col += 1;
EnterCell(Row, Col, 'Sell-to Customer No.', FALSE, FALSE, FALSE);

But it seems you figured that out. The column advances with each value that should be in the same record, then advance the row.

To fix the problem with the date use the FORMAT command:

EnterCell(Row, 3, FORMAT("Order Date"), FALSE, FALSE, FALSE);

Horse06

Sorry to reply to you late and it works perfectly. Thank you!

If I need to format the date like this format yyyymmdd, how to do it? Appreciate it!

Horse06

I have another question. How to combine the sales order number with date together. I tried to add the code as below, but had an error. Appreciate it!

EnterCell(1, 35, "No." "+" "posting date", FALSE, FALSE, FALSE);

DigiTecKid

For the date format use the FORMAT command:
FORMAT("My Date", 0, '<Year4><Month,2><Day,2>');

You can't combine a code value with a date value without turning the date value to text. Try:
EnterCell(1, 35, "No." + FORMAT("Posting Date"), FALSE, FALSE, FALSE);

If that's the date you need to re-format you can do it all at once...
EnterCell(1, 35, "No." + FORMAT("Posting Date", 0, '<Year4><Month,2><Day,2>'), FALSE, FALSE, FALSE);

I'd put some kind of separator in between the "No." value and the "Posting Date" value though. Something like:
EnterCell(1, 35, "No." + "-" + FORMAT("Posting Date", 0, '<Year4><Month,2><Day,2>'), FALSE, FALSE, FALSE);

Horse06

Thank you for your great help! I will try it right away. But if I want to combine the sales order no with today's date (always) to replace the posting date, how to code it?

Also does the system have the unique document identified when creat a document each time and how to program it? Thank you!

DigiTecKid

But if I want to combine the sales order no with today's date (always) to replace the posting date, how to code it?

I'm not sure what you're asking here. So when you run the export to Excel you want it to use the current date in that function where it combines it with the "No.". Then you want the current date to replace the "Posting Date" of the document?

For the unique identifier research using the function CREATEGUID. You could also do something with formatting CURRENTDATETIME to a string, remove everything but letters and numbers, making sure the date comes out to miliseconds.

Horse06

Thank you for your great help and repose!

Horse06

How about the format of the CURRENTDATETIME like YYYYMMDDThhmmss.sssZ. I trie to follow you instructions, but it does not work correctly. Appreciate it!

Thanks a bunch!

Horse06

Sorry to reply to you so late. I have worked it out. Thank you for your great help!