How to realize GROUP BY in Navision

Polar

Does anyone know how to realize in Navision constuction like
SELECT SUM(field17) FROM table32 GROUP BY field2 ?

Polar

I need to find unique values (price for instance, all sales with price and sum amounts). Or I should always prepare before FlowFields or Totals in reports? GROUP BY would be beautiful and simple solution. :?

bruno77

Polar,

I am not sure I fully understand what you are trying to do but maybe CALCSUMS could help you:

"Cust. Ledger Entry".SETCURRENTKEY("Customer No.","Date");
"Cust. Ledger Entry".SETRANGE("Customer No.", 'AAA 1050');
"Cust. Ledger Entry".SETRANGE("Date", 010196D, 123196D);
"Cust. Ledger Entry".CALCSUMS("Amount");

/Bruno

Polar

Hi, bruno77.

I'll try to explain.
For example , one company sales oil (sales by volume). In every tank
tankage is const, but price depends on density (density depends on temperature). In summer prices quite defferent than in winter.
After end of period I must print an application to agreement with customer
(in this period company sales to customer this Item this volume by this price, the same Item the same Customer but another volume and another price, etc.), but make it basing on fact sales and prices.

I don't know all prices of this item, I have to find price and volume sold by this price. So I can't do SETRANGE, 'cause I don't know values.

P.S. I've solved a problem but with a lot of code, C/AL Locals and nested loops. And I'm not sure i'd understand my own code in a couple of month.
(or i'll waste a lot of time for making a little correction).
If here was a GROUP BY , I'd make a cursor with unique prices easily.

bruno77

Hi Polar,

You could perhaps use a temporary table and fill it while looping through the ledgers (or whatever you are basing the calculations on).

Another option if you are on a Navision SQL db is to create an external View in SQL with your GROUP BY and then link it to a Navision table and be able to access it from Navision however I guess you are not on a Navision SQL db though :P .

/Bruno

Polar

Bruno,
I am on Navision SQL.

Could you, please explain, how to link external view to Navision table :?:

awarn

Hi,

I think I am missing something because this seems to easy... If what you need is that simple then you shouldnt have to write any more than 15 lines of code, and if you wanted this in a report I dont think you would need any code at all that the wizard wouldn't write for you:


SELECT SUM(field17) FROM table32 GROUP BY field2

in Navision is:

table.setcurrentkey(field2)
if table.find('-') then repeat until
if tLastField2 <> field 2 then begin
message(field2 + ' total = ' +format(dtotal));
dTotal := 0;
end;
tLastField2 := field2;
dTotal := dtotal + field 17;
table.next = 0

Of course you don't really want a message box! You can store the total in an array, or perform whatever action on it, or make this into a really simple report.

The 'key' is of course picking the right key, whether you are looping in code or in a report. If you run tha table, and sort by the correct key, you can see where in the code or report the change in the field happens, this is where you want to print / act on the total.


-A

Polar

Thanx,

I did it in the same.

P.S. I've solved a problem but with a lot of code, C/AL Locals and nested loops. And I'm not sure i'd understand my own code in a couple of month.


It was complex task ( not one GROUP BY to realize).
:roll:

PEzechiels

a GROUP BY in SQL is the same as SETCURRENTKEY in Navision

kine

PEzechiels wrote:

a GROUP BY in SQL is the same as SETCURRENTKEY in Navision

It is not true... 8)

SETCURRENTKEY is only part of job, what is GROUP BY doing. Second part - grouping - is not existing in Navision. You must do it throug some loop.

PEzechiels

i wouldn't say that kine

as you know if you want to accomplish grouping in for instance a report, the wizard enables you to group by selecting a key.

it may not be quite the results you get from SQL but nevertheless your report is neathly divided in to groups op your choice.

if you want some special grouping for which there is no key then that is a whole different story.

and please......feel free to correct me if i'm wrong

dmccrae

Yes, this is SETCURRENTKEY combined with the report writer though, which does grouping as part of its job.

You cannot achieve grouping using SETCURRENTKEY sitting as plain code in a code unit etc. SETCURRENTKEY corresponds only to ORDER BY in the SQL version.

jaisa

I'm working with totals by group in navision.
I have made this.

RecordVariable.SETCURRENTKEY(field1, field2);
RecordVariable.SETRANGE(field1,RangeValue);
RecordVariable.SETRANGE(field2,RangeValue);
RecordVariable.CALCSUMS(field3);
LocalDecimalVariable:=RecordVariable.field3;

NOTES:
In the table of Record Variable you must define a key with at least field1 and field2.
The field3 is a SumIndexField for this key.

For me this works fine.

Miklos_Hollender

If one is using SQL and needs many level of groupings, it's often easier to do it from SQL and link it to an Excel Pivot table - it's a kind of poor man's business intelligence:

- Take a Navision installed on an SQL server
- Take one and only one transaction table, usually lowest level (Value Entry, Detailed Customer Ledger Entry etc.)
- Join it with every reasonable table, but NOT transaction tables (Customer, Vendor, Salesperson, Location etc.)
- Group it by everything reasonable, including datepart of Posting Date by year, month, week, and Posting Date itself, maybe with a well-formatted convert to varchar
- make a new table
- insert these stuff into the new table with an SQL procedure every night
- take Excel, install Microsoft Query and make Pivot Tables with a select * from the former table

Savatage

Miklos Hollender wrote:

If one is using SQL and needs many level of groupings, it's often easier to do it from SQL and link it to an Excel Pivot table - it's a kind of poor man's business intelligence:

http://www.mibuso.com/dlinfo.asp?FileID=484