Group and SUM in FORM of Navision --- Help me !

huungan040390

Dear all expert,

I have the data same below data :

UserName Salary
NHNGAN 1000
NHNGAN 2000
NHNGAN 3000
NHNGAN 4000

---

> MY question is : how to group by UserName and sum Salary in the FORM of NAVISION.

Thanks & Best Regards !

mohana_cse06

did you try with Flowfield?

huungan040390

I get data from 2 tables, Sales Invoice Header and Sales Invoice Line, so what should i do ?

huungan040390

I want to get data of 2 fields : Salesperson Code and Amount from 2 tables : Sales Invoice Header and Sales Invoice Line.
And then i want group by Salesperson Code field, sum Amount field.
So I must create new flow field in what table ?

GRIZZLY

If you want to know how much did your managers sold, then you should investigate Customer Ledger Entry table. Here you've got Salesperson Code and Amount. I don't remember, but probably it contains keys with calculated fields. If it not exists, then you could create it. After it you could organize REPEAT UNTIL statements on Salesperson table and CALCSUMS on Customer Ledger Entry.
Your approach is definitely wrong, because of Credit Memos, which amount should be deducted.

huungan040390

Example my data :

User Name Amount
ntkdung 20000
ntkdung 30000
nhngan 10000
nmquan 40000
cclee 50000
nhngan 60000



How to group by User Name and sum Amount by using "REPEAT UNTIL" ?
My group code :
I think it's wrong about "REPEAT UNTIL" construct and TEMP variable, how to fix it ?

---

_TIENVN := 0;
_TIENTHUE := 0;
_TTIEN := 0;
_SalespersonCode := '';
_SalespersonCodeTEMP := '';
_SalespersonCodeTEMP2 := '';
_Pos := STRPOS(_G_SalesInvoiceHeader."Salesperson Code",'-');

_G_SalesInvoiceHeader.RESET;
_G_SalesInvoiceHeader.SETCURRENTKEY("Salesperson Code");

IF _G_SalesInvoiceHeader.FINDFIRST THEN BEGIN
_SalespersonCodeTEMP := COPYSTR(_G_SalesInvoiceHeader."Salesperson Code",_Pos+1);
REPEAT
_SalespersonCodeTEMP2 := COPYSTR(_G_SalesInvoiceHeader."Salesperson Code",_Pos+1);

REPEAT
IF _SalespersonCodeTEMP = _SalespersonCodeTEMP2 THEN BEGIN
_SalespersonCode := _SalespersonCodeTEMP2;
_TIENVN := "Line Amount";
_TIENTHUE := ROUND(_TIENVN * 10 / 100);
_TTIEN := _TIENVN + _TIENTHUE;
END;

IF _SalespersonCodeTEMP <> _SalespersonCodeTEMP2 THEN BEGIN
_TTIEN := 0;
END;


UNTIL _G_SalesInvoiceHeader.NEXT=0;
_SalespersonCodeTEMP := _SalespersonCodeTEMP2;

UNTIL _G_SalesInvoiceHeader.NEXT=0;
END;

---

GRIZZLY

What about using Salesperson Temporary table and using some of its field for cummulative amount?

_G_SalesInvoiceHeader.RESET;

IF _G_SalesInvoiceHeader.FINDFIRST THEN REPEAT
  _G_SalesInvoiceHeader.CALCFIELDS("Amount");
  IF NOT _TmpSalesperson.GET(_G_SalesInvoiceHeader."Salesperson Code") THEN BEGIN
      _TmpSalesperson.INIT;
      _TmpSalesperson.Code := _G_SalesInvoiceHeader."Salesperson Code";
      _TmpSalesperson."Estimated Value (LCY)"= _G_SalesInvoiceHeader."Amount";
      _TmpSalesperson.INSERT;
  END  ELSE BEGIN
      _TmpSalesperson."Estimated Value (LCY)" := _TmpSalesperson."Estimated Value (LCY)" + _G_SalesInvoiceHeader."Amount";
      _TmpSalesperson.MODIFY;
  END;
UNTIL _G_SalesInvoiceHeader.NEXT=0;

huungan040390

Dear GRIZZLY,

Thank you very much, but i can not understand about it, can you explain more and more, i can not see the C/AL Global variable. can you show me all ?

Thanks & Best Regards !

GRIZZLY

You should define _TmpSalesperson as local or global variable with Temporary property set to Yes.

huungan040390

Hi !

what is the DataType & SubType of _TmpSalesperson ?

Thanks !

GRIZZLY

DataType = Record,
SubType = Salesperson/Purchaser
Temporary = Yes

huungan040390

Hi !

Thank you very much, i will try, and if has any problem, can i ask you ?

Thanks & Best Regards !

GRIZZLY

U'r welcome!

huungan040390

Hi GRIZZLY,

I'm new in Navision Classic 2009, and I can not imagine where the code write down.
So can I ask you a question ?
+ The above code is write in CodeUnit or Form or Report ?
+ If i not use Salesperson/Purchase table, so i must create new table to used with above code or not ?

***** I can not imagine step by step ? ](*,) ](*,) ](*,) ](*,) ](*,)

Thanks & Best Regards !

GRIZZLY

It depends on where you would like to see manager sales amount values...
If you want to see it in Report object, then code should be corrected, because Report couldn't show values in Temporary table...

huungan040390

Hi GRIZZLY,

If I write the above code in the form and I don't want use the Salesperson/Purchaser table => What should i do ?

1/. Should I create the new table contains 2 fields (username, amount).

2/. The new table should contain the primary key or not ?

3/. What trigger area the above code are written ?

Thanks & Best Regards !

GRIZZLY

You could create new form, based on Salesperson/Purchaser table (SourceTable). In that form you could set visible fields: Code, Name, and a new one, which SourceExpression is related to some Field.
After that you could put some code in OnFormat trigger for this new column. In that trigger you could get the Code of current salesperson and filter by him the corresponding Sales Invoice Headers, then summ the Amounts (like in that code below) and return the value from OnFormat trigger, which is equal to text presentation of the cummulative Amount.

huungan040390

Hi GRIZZLY,

You are very good :thumbsup: =D> , I can not imagine ](*,) :?:
Can you show me the demo ?

Thanks & Best Regards !

GRIZZLY

I think you should start with some manuals about programming. It's impossible to tell about NAV in few words.
If you have a concrete question then people here can give you the answer...