How to group in Processing only report?

Aravindh_Navision

Hi,

I am designing one report which is a processing only report.

I have the following records in the table.

Acc. No.   - Amount
```````````````````
1. A12345 - 50.00
2. A12345 - 100.00
3. A12345 - 25.00
4. A12222 - 24.00
5. A12222 - 24.00
6. A32154 - 120.00

I need to total the Amount field based on the Acc. No.

My final output should be:
``````````````````````````
1. A12345 - 175.00
2. A12222 - 48.00
3. A32154 - 120.00

And I need to write this output in text file using the File datatype in 3 lines by grouping according to Acc. No.

Can anyone guide me how to proceed with this?

Thanks and Regards,
Aravindh

DenSter

Keep track of the account number and only create output when it changes. You could also create a dataitem for the account itself and total the numbers in a variable.

Lots of different ways, take your pick. Be creative.

Aravindh_Navision

Thank you DenSter for your reply.

I can do the second part, that is writing in text pad. But got struck in the first part. ](*,) Can you help me?

DenSter

You could write some code yourself and I'd be happy to answer any specific questions you may have. However I don't write code for other people.

Why would you use textpad, do you have something against the C/AL editor?

Aravindh_Navision

DenSter,

From the text file that is generating from Navision, will be send to other third party tool as an input for some processing. I could do code for this using File datatype.

DenSter

Sounds great, let us know when you have a specific question.

Miklos_Hollender

Create a new table. Give it whatever number - it will be only used for temptable which do not need to be in the licence range. The primary key should be 5 Code20 fields like Key1, Key2, Key3,Key4,Key5 allowing you 5 levels of grouping, usually enough. Also add fields like Dec1, Dec2...Dec10, Int1,Int2, Int3, Date1,Date2,Date3,Text1,Text2,Text3. Call it Reporting Table and save it. (I actually called the table GroupBy because boo C/AL, SQL rocks! :-) )

Use this as a general tool for grouping i.e. put data into it (temptable) like

CLEAR(TempGroupBy);
IF TempGroupBy.GET(Acc."No.") THEN BEGIN
    TempGroupBy.Dec1 += Acc."Amount";
    TempGroupBy.MODIFY;
END ELSE BEGIN
  TempGroupBy.Code1 := Acc."No.";
  TempGroupBy.Dec1 := Acc."Amount";
  TempGroupBy.Insert;
END;

Then just loop through it and process the results.

This saved my backside so many times...

Aravindh_Navision

Thank you Mikalos for your kind favour..!

This really helped me to achieve what I need to. Instead of CLEAR(TempGroupBy), is it right using TempGroupBy.DELETEALL in OnPreDataitem?

Aravindh_Navision

I need a clarification in the below code. Before that I just explain what I am doing. I have 5 dataitems and those dataitems are NOT indented. I am executing the dataitem one-by-one and writing the output into text file using FILE datatype. While doing so, the previous dataitem's output is overwritten by the later one. I have written the same code in all the dataitem's OnPostDataitem trigger.

Variables:

NFILE (Datatype: File)
WriteStream (Datatype: OutStream

OnPostDataitem():

NFile.CREATE('C:\Sample.txt');
NFile.CREATEOUTSTREAM(WriteStream);
WriteStream.WRITETEXT(Text1 + Text2 + Text3+...+Text n);
WriteStream.WRITETEXT;
NFile.CLOSE;

Can anyone point out me, where am I doing wrong?

Aravindh_Navision

Moving the above mentioned to new topic heading since it is a different one.

viewtopic.php?f=23&t=54588

Aravindh_Navision

My special thanks to Miklos and Denster for the valuable inputs and help. It worked. :thumbsup: