How to group in Processing only report?

Aravindh_NavisionAravindh_Navision Member Posts: 258

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,


  • Options
    DenSterDenSter Member Posts: 8,304
    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.
  • Options
    Aravindh_NavisionAravindh_Navision Member Posts: 258
    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?
  • Options
    DenSterDenSter Member Posts: 8,304
    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?
  • Options
    Aravindh_NavisionAravindh_Navision Member Posts: 258

    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.
  • Options
    DenSterDenSter Member Posts: 8,304
    Sounds great, let us know when you have a specific question.
  • Options
    Miklos_HollenderMiklos_Hollender Member Posts: 1,598
    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
    IF TempGroupBy.GET(Acc."No.") THEN BEGIN
        TempGroupBy.Dec1 += Acc."Amount";
      TempGroupBy.Code1 := Acc."No.";
      TempGroupBy.Dec1 := Acc."Amount";

    Then just loop through it and process the results.

    This saved my backside so many times...
  • Options
    Aravindh_NavisionAravindh_Navision Member Posts: 258
    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?
  • Options
    Aravindh_NavisionAravindh_Navision Member Posts: 258
    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.

    NFILE (Datatype: File)
    WriteStream (Datatype: OutStream

    WriteStream.WRITETEXT(Text1 + Text2 + Text3+...+Text n);

    Can anyone point out me, where am I doing wrong?
  • Options
    Aravindh_NavisionAravindh_Navision Member Posts: 258
    Moving the above mentioned to new topic heading since it is a different one.

  • Options
    Aravindh_NavisionAravindh_Navision Member Posts: 258
    My special thanks to Miklos and Denster for the valuable inputs and help. It worked. :thumbsup:
Sign In or Register to comment.