Sorting column in ExcelBuffer NAV2018

Aurore999

Hi everyone,

I'm new with dynamics NAV and I'm currently working on an Excel export using ExcelBuffer. I would like to know if it's possible to sort data in ExcelBuffer based on a specific column. My problem is that I need to process data from several sources and have calculated data on which I have to sort. So I can't sort on my datasource directly.

My code is something like this :

excelbuffer.createbook(...);
if myRecord.findset then begin
repeat
    entercell(data from myRecord);
    calculate myDate which is based on data from myRecord and another data source
    entercell(myDate);
until myRecord.next = 0;
end;

//need to sort excelbuffer on myDate

excelbuffer.writesheet(...);
excelbuffer.closebook;
excelbuffer.openexcel;
excelbuffer.giveusercontrol;

We are working with NAV2018 and VS Code

Thank you

Aurore

RockWithNAV

Do you want to sort it out from NAV itself?

Aurore999

Thanks for your reply RockWithNAV,
Yes the idea is to sort the ExcelBuffer on a specific column before calling function writesheet

Aurore

kriki

[Topic moved from 'NAV/Navision Classic Client' forum to 'NAV Three Tier' forum]

ShaiHulud

Aurore999 wrote: »

Thanks for your reply RockWithNAV,
Yes the idea is to sort the ExcelBuffer on a specific column before calling function writesheet

Aurore

I would suggest writing all the data you're calculating into a temporary table, then to sort that table and then write data from that temporary table to Excel Buffer

Aurore999

ShaiHulud wrote: »

Aurore999 wrote: »

Thanks for your reply RockWithNAV,
Yes the idea is to sort the ExcelBuffer on a specific column before calling function writesheet

Aurore

I would suggest writing all the data you're calculating into a temporary table, then to sort that table and then write data from that temporary table to Excel Buffer

HI ShaiHulud,

Thanks a lot for your answer. Do I need to choose an existing table and mark it as a temporary record or is there another way to create a temporary table and define columns I need ?

Best regards

Aurore

ShaiHulud

Aurore999 wrote: »

ShaiHulud wrote: »

Aurore999 wrote: »

Thanks for your reply RockWithNAV,
Yes the idea is to sort the ExcelBuffer on a specific column before calling function writesheet

Aurore

I would suggest writing all the data you're calculating into a temporary table, then to sort that table and then write data from that temporary table to Excel Buffer

HI ShaiHulud,

Thanks a lot for your answer. Do I need to choose an existing table and mark it as a temporary record or is there another way to create a temporary table and define columns I need ?

Best regards

Aurore

If you can find a table that would fit all the data you need (even if under incorrect field names), then you should use that, marking it as temporary. The disadvantage of that is that if anybody (Microsoft with a new CU or version; other developer; 3rd party add-on provider) make changes to that table, it might break your code.
Alternatively, you can design your own table in the custom rage (if permitted by license), still mark it as temporary to not actually put any of the calculated and processed data into the database (unless you need it for archiving purposes, like "what was exported on the 18th of June, 2020"), and use that.