Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / How to filter big Nav ...
Small and medium business | Business Central, N...
Suggested answer

How to filter big Nav tables in ODATA or make them fold in Power Query

(1) ShareShare
ReportReport
Posted on by Community member 6
Hi
Created a webservice in Nav with a very big table which has our items quantities to query w/ ODATA
The goal is to lookup quantities for a much smaller list of some items using the Power Query through ODATA in Excel (so I can upload our inventory feed in eCommerce channels)
https://somedomain.com:7048/Something/OData/Company('Comp')/ItemListODATA?$filter=Gen_Prod_Posting_Group eq 'AAA' and (Status_Code eq 'BBB' or Status_Code eq 'CCC' or Status_Code eq 'DDD')&$format=json&$select=No,Qty1
The problem is that ItemList is very big, has too many rows. Even with filters, it doesn't load to the power query. It loads in the browser after a while but paginates after 100,000 lines. I need to load everything in order to vlookup(xlookup) or /merge/ queries in Power Query for it to fold somehow, but it seems that it doesn't fold.
How do you query these big tables in Nav (for a person who doesn't have much access or understanding of Nav)?
Thanks
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,647 Super User 2024 Season 2 on at
    How to filter big Nav tables in ODATA or make them fold in Power Query
    Unfortunately, as far as I know, there is no good way. . . .
     
    Hopefully other experts can give you better advice.
     
     
    Thanks.
    ZHU
     
  • Community member Profile Picture
    Community member 6 on at
    How to filter big Nav tables in ODATA or make them fold in Power Query
    So no way to optimize ODATA link, or do it from other available-standard queries/pages?
     
    When I write my ODATA URL in the browser, it paginates.. After some 15-20 minutes, it stops and gives a link to the next page.
        "odata.nextLink": "https://...
     
    But Power Query seems not to be aware of this functionality and doesn't automatically load the next page, or it does, and this is why it doesn't end...
    I won't mind running it overnight and having a pulled table in PQ in the morning if there is a way.
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,647 Super User 2024 Season 2 on at
    How to filter big Nav tables in ODATA or make them fold in Power Query
    Sorry, all the points I mentioned require a development license. I think you could contact your partner to discuss this.
     
    Thanks
    ZHU
  • Community member Profile Picture
    Community member 6 on at
    How to filter big Nav tables in ODATA or make them fold in Power Query
    Don't know how to create the query.. I've tried to install "Dynamics Nav Development Environment," but it wants an SQL server connection. I'm guessing the SQL server runs on Azure or some cloud and Dynamics credential/server is in another place. I can install the client Nav and connect to its server, but not the SQL server.
    So there were many prebuilt queries in the "Web Services," but I couldn't see which one was responsible for the Quantities.
    I've managed to query by breaking the ODATA URL with a Parameter and "Invoke Custom Function" in "Add Column" in Power Query, but looping it that way is very-very slow, approximately 1 second per ODATA query (even if I limit it to two columns by using $select=sku,qty)
    I have client Nav user privileges that allow me to create Web Services. Do you know of any way I can do what you've suggested me to do? To create a query or a new list
     
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,647 Super User 2024 Season 2 on at
    How to filter big Nav tables in ODATA or make them fold in Power Query
    Hi, there are several solutions to improve performance, hopefully this will give you some tips.
    1. Please do not use the standard Item List page, create a new one that does not contain any standard logic.
    2. Reduce the fields on the page and only include those that need to be queried and filtered, which will improve efficiency.
    3. Please do not use page object. Using Query object will be more efficient. https://learn.microsoft.com/en-us/dynamics-nav/queries?wt.mc_id=DX-MVP-5004336
     
     
    Thanks.
    ZHU

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 Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans