web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / How to make filters on...
Microsoft Dynamics NAV (Archived)

How to make filters on odata url in Excel

(0) ShareShare
ReportReport
Posted on by Anders Hedegaard 285

Hello,

How can I put a date filter on the odata uri when using Excel?

I can make a connection and get the G/L Accounts into Excel. But I also want the user to be able to make date filters.

But when I try to add ?filter= to the connection command text Excel gives an error "We cannot find the table 'G/L Account?filter'... in the datamodel.

Is it possible to make an uri filter on the odata connection in Excel?

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Tharanga Chandrasekara Profile Picture
    Tharanga Chandrasekara 23,118 on at

    Refer this link

    http://dev.socrata.com/odata/

    http://technet.microsoft.com/en-us/library/jj873965(v=office.15).aspx#part2c

    http://dev.socrata.com/docs/filtering.html

  • Anders Hedegaard Profile Picture
    Anders Hedegaard 285 on at

    Hi Tharanga,

    thank you for the response.

    I still do not see a solution in the links you sent.

    If the dataset is large, then I want to make filters before I retrieve the data.

    For example this uri does not Work in Excel.

    soda.demo.socrata.com/.../4tka-6guv Islands region&source=pr

    Excel will give the error that the Table 4tka-6guv?region does not exist.

    1. I cannot add the uri to the connection string

    2. I cannot add the 4tka-6guv?region=Virgin Islands region&source=pr" part in the command text.

    Thank you

    Anders

  • Suggested answer
    keoma Profile Picture
    keoma 32,729 on at

    hi,

    i published the Item List as webservice and tried the following sample "all items with last modified date equal to 2013-11-07":

    localhost/.../Company('CRONUS%20AG')/ItemsList?$filter=Last_Date_Modified eq datetime'2013-11-07'

    when executed in the browser it is translated to:

    localhost/.../Company('CRONUS%20AG')/ItemsList?$filter=Last_Date_Modified%20eq%20datetime%272013-11-07%27

    this worked fine.

    the same with "greater than" instead of "equal":

    localhost/.../Company('CRONUS%20AG')/ItemsList?$filter=Last_Date_Modified%20gt%20datetime%272013-11-07%27

    worked also fine

  • Anders Hedegaard Profile Picture
    Anders Hedegaard 285 on at

    Hi Jonathan,

    thank you for helping.

    I understand that I can make filters in the url and that works just fine in the brower in my setup as well.

    But I cannot use these urls in Excel. I get an error saying "The table does not exist".

    NB: I am not using Power Query.

    thank you

    Anders

  • Suggested answer
    keoma Profile Picture
    keoma 32,729 on at

    hi,

    which excel version do you use?

    did you try to import using "Data tab" --> "Get External Data" --> "From Other Sources" --> "From OData Data Feed"?

    i tried additional following (using excel 2010):

    * On the Data tab --> From Web: giving odata url --> imported --> got a ready to use table with the received data

    * "Data tab" --> "Get External Data" --> "From Xml Data": giving the odata url --> imported --> resulted in a textual import of the resulting xml doc.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

#1
HoangNam Profile Picture

HoangNam 7

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans