Form - Group by

anilkumar

Hi Nav Experts!

SQL query is :
SELECT Customer FROM Orders GROUP BY Customer

I want to create a List form and display customers as per above query.

what will be the logic ??

Can any expert give some tip.

Thanks!

DenSter

Forms can't be grouped. You can sort them by any key, but there's no grouping functionality.

David_Singleton

anilkumar wrote:

Hi Nav Experts!

SQL query is :
SELECT Customer FROM Orders GROUP BY Customer

I want to create a List form and display customers as per above query.

what will be the logic ??

Can any expert give some tip.

Thanks!

Hi Anilkumar,

what you are attempting, is to solve a business issue with programming. This is not what Navision is all about. You need to sit with the client and identify the business need and suggest a NAVISION solution, not a SQL solution. If you don't do this, then you will just be giving the client a solution to a problem that does not exist. This will just waste the customers money, and is generally a major reason for budget over runs in Navision implementations.

anilkumar

Hi DenSter/ David,

Thanks for your suggestions!


If I want to display only customername in a table in simple list form, can I expect any logic or sample??

Example: records in a order table as follows:


Table: Orders

Fields to display: CustomerName (Field)

Example data available in Orders table for customername field:

001 ABC
001 ABC
001 ABC
002 XYZ
002 XYZ
003 ASD
003 ASD

Wants to display only Customername in listform as follows:
ABC
XYZ
ASD


Thanks for the help in Advance.

David_Singleton

Anil, please explain what you are tried so far, and what errors you are getting.

What are you trying to achieve, what is the business requirement?

DenSter

So you don't want data to be grouped, you want a SELECT DISTINCT. Unfortunately NAV does not have this capability. Please answer Davids question and explain what the requirement is.

anilkumar

Mr. David,

Thanks for the support. I have done this list form with using temporary table. Through this discussion I learned few things, As what are limitations in Nav, what we can commit to client and all. more over I should also clear what are the standard approach to handle this type of situations to became a good developer.


Mr. DenSter,

Thanks for your guidence, as I came to know Nav standards, what we can commit to client, related to this type of customizations.

DenSter

I don't understand. You are saying some things, but it's not clear to me what your question is.

anilkumar

Mr. DenSter,

I have asked for List form to display SELECT DISTINCT, but that functionality not there.

DenSter

So now that you know it's not there, the question is answered right?

anilkumar

Yes, the question answered right.

Thanks!

DenSter

Cool, excellent, sometimes it's not easy to tell

David_Singleton

anilkumar wrote:

Mr. David,

... more over I should also clear what are the standard approach to handle this type of situations to became a good developer.

Well its going to be hard to give you advise if you simply refuse to listen to the advise given.

The solution here is to define the business needs in Navision terms, that's what consultants and analysts are for.

If you were a good developer you would go back to the consultant and tell them that they need to rethink this and define a business need, not a code requirement.

awarn

Well,

It is not hard to think about what the customer may have wanted here:

-a form showing all customers who have had orders in the past week
-a form showing all customers who had orders shipped from a certain location
-etc...

I thought he was being pretty clear - WHY the customer wanted it - well why do customers want anything


But the correct solution to 'group by' or 'select distinct' is to use a temporary variable.

SalesOrder.RESET:
//set filters
IF SalesOrder.FINDSET THEN REPEAT
IF Customer.GET(SalesOrder."Sell-to Customer Code") THEN BEGIN
tmpCustomer := Customer;
IF tmpCustomer.INSERT THEN;
END;
UNTIL SalesOrder.NEXT = 0;

//now your tmpCustomer variable has the list of customers, and display it in a form .

FORM.RUNMODAL(22,tmpCustomer);

David_Singleton

awarn wrote:

It is not hard to think about what the customer may have wanted here:

Unless you sat with the customer and had a clear understanding of the business need and worked through the options and discussed them in terms of how they work, then you don't have even the vaguest clue what the customer wanted. This is pure conjecture and guess work.

I work in recovering Navision implementations gone wrong, and by far the biggest cause of failure I see in Navision projects, is bad expectation management. Normally this is where the Partner ASSUMED they understood the customers need, but didn't.

You may guess right 99% of the time, but that 1 in a hundred that you screw up will cost the customer a lot of money and you a lot of good will.

awarn

Hi, I get that, but what I see in this topic was a guy asking a pretty simple question - and the responders giving him a bit of a hard time about it.

I was able to figure out from his first post what he wanted, and perhaps due to language noone else was and he did not understand what you wanted him to describe to you.

All I am saying is that the solution seemed to be pretty simple, and has been described here 1000 times before.

And boy oh boy I know that many times the best solution is not to code at all but to figure out how to make tha program work for you - I ask my consultants every day to err on the side of not coding - but in this case I though he was quite clear in what he wanted and why didn't really matter.

David_Singleton

awarn wrote:

...but in this case I thought he was quite clear in what he wanted and why didn't really matter.

You must KNOW not think. It DOES matter WHY. ](*,)

In all honesty though I am losing interest in helping people to get the right solutions these days. If someone wants to destry their customers system, they will eventually get the bad answer they want. So sometime I think its easier just to fix up the mess than to try to stop people from messing up in the first place.