Options

Export to excel

Horse06Horse06 Member Posts: 496
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!

Comments

  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    What is the error? Can you more of the code you're using for this?
  • Options
    Horse06Horse06 Member Posts: 496
    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!
  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    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.
  • Options
    Horse06Horse06 Member Posts: 496
    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!
  • Options
    Horse06Horse06 Member Posts: 496
    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!
  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    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.
  • Options
    Horse06Horse06 Member Posts: 496
    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!
  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    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.
  • Options
    Horse06Horse06 Member Posts: 496
    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!
  • Options
    Horse06Horse06 Member Posts: 496
    I have figured out those with the data type of option, but the boolean still does not work and has the same error.
  • Options
    Horse06Horse06 Member Posts: 496
    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!!!
  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    edited 2010-12-10
    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.
  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    edited 2010-12-10
    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;
  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    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;
  • Options
    Horse06Horse06 Member Posts: 496
    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!
  • Options
    Horse06Horse06 Member Posts: 496
    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
  • Options
    Horse06Horse06 Member Posts: 496
    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!
  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    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);
    
  • Options
    Horse06Horse06 Member Posts: 496
    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!
  • Options
    Horse06Horse06 Member Posts: 496
    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);
  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    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);
  • Options
    Horse06Horse06 Member Posts: 496
    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!
  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    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.
  • Options
    Horse06Horse06 Member Posts: 496
    Thank you for your great help and repose!
  • Options
    Horse06Horse06 Member Posts: 496
    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!
  • Options
    Horse06Horse06 Member Posts: 496
    Sorry to reply to you so late. I have worked it out. Thank you for your great help!
Sign In or Register to comment.