Skip to main content

Notifications

Announcements

No record found.

Small and medium business | Business Central, N...
Suggested answer

Getting only distinct values

(0) ShareShare
ReportReport
Posted on by 382

Hey community, I want to ask that how can I only get unique Production ID i.e. 1000, 1330 and 1600 and I don't want it to be repetitive like it is in the picture below. It should consist of only three lines each for one Production ID. Kindly suggest how can I do that.

pastedimage1673445852352v1.png

  • Suggested answer
    TeddyH Profile Picture
    TeddyH 12,868 Super User 2024 Season 1 on at
    RE: Getting only distinct values

    The query should retrieve the distinct records for all the fields specified in the query object, not the ones specified in your procedure logic.

    You didn't post your query, but I can see that you commented out some of the query fields on your screenshot.

    Things like uerMember or Description also being considered as part of distinct value.

  • RE: Getting only distinct values

    Yes teddy I have used Method=Sum but it's not working. I tried to use count but my fields are integer and not code.

  • RE: Getting only distinct values

    Hey, still unclear. Can you show me the code for this?

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 74,115 Super User 2024 Season 2 on at
    RE: Getting only distinct values

    Hi, You can also directly add a loop that inserts a new row if it doesn't exist and modifies the record if it exists, which is also more convenient.
    But defining in Query is the best performance.

    Hope this helps as well.

    Thanks.

    ZHU

  • Suggested answer
    TeddyH Profile Picture
    TeddyH 12,868 Super User 2024 Season 1 on at
    RE: Getting only distinct values

    Check if your query has at least one aggregate column to trigger the distinct.

    Refer to below link for more information.

    thatnavguy.com/.../

  • Suggested answer
    Ayala Profile Picture
    Ayala 110 on at
    RE: Getting only distinct values

    HI,

    On query to get unique values you must group only for those unique fields you need to obtain.

  • Ayala Profile Picture
    Ayala 110 on at
    RE: Getting only distinct values

    Hi,

    If you have the option to work with query object. You can create some like this:

    query 60100 DistinctValues
    {


        elements
        {
            dataitem(SalesLine; "Sales line")
            {
                column(LineNo; "Line No.")
                {

                }

                column(Count)
                {
                    Method = Count;
                }
            }
        }


    }
    It will return dataset with unique line no. You can find more information on Aggregate and sort data in a query - Training | Microsoft Learn
    I hope this will help you
  • RE: Getting only distinct values

    Actually I am getting the data from a query. and yes I'm inserting it in a custom table. the query contains repetitive values. this is how I'm getting values but they are repetitive as shown in the above image

    pastedimage1673448290353v1.png

  • Suggested answer
    Mohana Yadav Profile Picture
    Mohana Yadav 59,194 Super User 2024 Season 2 on at
    RE: Getting only distinct values

    Is it a custom table/page?

    How are you filling this table now?

    can you show the code?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans