Form - Group by

anilkumaranilkumar Member Posts: 136
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!
Anil Kumar Korada
Technical Consultant

Answers

  • DenSterDenSter Member Posts: 8,305
    Forms can't be grouped. You can sort them by any key, but there's no grouping functionality.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • anilkumaranilkumar Member Posts: 136
    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.
    Anil Kumar Korada
    Technical Consultant
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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?
    David Singleton
  • DenSterDenSter Member Posts: 8,305
    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.
  • anilkumaranilkumar Member Posts: 136
    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.
    Anil Kumar Korada
    Technical Consultant
  • DenSterDenSter Member Posts: 8,305
    I don't understand. You are saying some things, but it's not clear to me what your question is.
  • anilkumaranilkumar Member Posts: 136
    Mr. DenSter,

    I have asked for List form to display SELECT DISTINCT, but that functionality not there.
    Anil Kumar Korada
    Technical Consultant
  • DenSterDenSter Member Posts: 8,305
    So now that you know it's not there, the question is answered right?
  • anilkumaranilkumar Member Posts: 136
    Yes, the question answered right.

    Thanks!
    Anil Kumar Korada
    Technical Consultant
  • DenSterDenSter Member Posts: 8,305
    Cool, excellent, sometimes it's not easy to tell :mrgreen:
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • awarnawarn Member Posts: 261
    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_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • awarnawarn Member Posts: 261
    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_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
Sign In or Register to comment.