Forum Discussion

The_Doc's avatar
The_Doc
Ultimate Cover User
2 months ago
Solved

OData queries are not working

Hi Folks

Despite years of running MYOB's APIs I have never been successful at getting any ODATA queries to work.

My APIs work - fine but if I need Sales Orders - I have to get them all and then sort.

It works but now this no longer is the best option especially when the download pages start heading towards over a 100.

"/Sale/Invoice/?$Filter=startswith(Number,'RPWeb')"      - doesn't work

"/Sale/Invoice/Item/?$Filter=startswith(Number,'RPWeb')"  doesn't work

"/Sale/Invoice/?$Filter=Date gt dateTime'2024-11-01'" - doesn't work

"/Sale/Invoice/Item/?$Filter=Date gt dateTime'2024-11-01'" - doesn't work

 

I can say I have never got ODATA queries working.

 

I use MSAccess - my API calls are not through MYOB's SDK but through my own code I have created from scratch - my apps work fine - but I end up downloading the entire payload.

 

Be interesting to see if the same occurs with a VB.NET or VC#.NET - I have test applications - but was wondering if someone can spot something obvious. 

I know queries in ODATA are extremely finnicky - as they are in SQL - but ODATA seems to have beaten me.

I can no longer ignore this problem as I need to now download a selection of Sales Invoices after 1/11/24 else I end up with a ginormous download number of pages.

Interestingly - I don't get any errors - just ALL the invoices.

The Doc

 

  • The_Doc's avatar
    The_Doc
    2 months ago

    Hi Folks

    Don't you know it - as soon as I posted I went back to trying ODATA - and again nothing worked and then I spotted what seemed to be an insignificant error in the code

    "/Sale/Invoice/Item/?$Filter=startswith(Number,'RPWeb')"  doesn't work - this is from MYOB API developers web in which I have found many many errors.

    So I went to the link that takes me to the official ODATA 2.0 site and tried this line

    "/Sale/Invoice/Item/?$Filter=startswith(Number,'RPWeb') eq true"  doesn't work 

    but no joy

    "/Sale/Invoice/Item/?$filter=startswith(Number,'RPWeb') eq true"  WORKED!!!!!!

     

    I starred at this for hours and missed the obvious - 'Filter' - doesn't work - 'filter' does - ODATA is case sensitive.

    VB.NET - is not case sensitive

    VC#.NET is pedantically case sensitive to the extent that a variable named "Filter' is totally different to 'filter

     

    VBasic in Access is NOT

    ODATA it seems is - 

     

    I resisted posting for weeks - and as soon as I did - the solution popped out.

    Hope my dive into ODATA helps someone - they are fiddly though way, way more than SQL queries.

     

    The Doc

     

     

3 Replies

Sort By
  • The_Doc's avatar
    The_Doc
    Ultimate Cover User
    2 months ago

    Hi Folks

    Don't you know it - as soon as I posted I went back to trying ODATA - and again nothing worked and then I spotted what seemed to be an insignificant error in the code

    "/Sale/Invoice/Item/?$Filter=startswith(Number,'RPWeb')"  doesn't work - this is from MYOB API developers web in which I have found many many errors.

    So I went to the link that takes me to the official ODATA 2.0 site and tried this line

    "/Sale/Invoice/Item/?$Filter=startswith(Number,'RPWeb') eq true"  doesn't work 

    but no joy

    "/Sale/Invoice/Item/?$filter=startswith(Number,'RPWeb') eq true"  WORKED!!!!!!

     

    I starred at this for hours and missed the obvious - 'Filter' - doesn't work - 'filter' does - ODATA is case sensitive.

    VB.NET - is not case sensitive

    VC#.NET is pedantically case sensitive to the extent that a variable named "Filter' is totally different to 'filter

     

    VBasic in Access is NOT

    ODATA it seems is - 

     

    I resisted posting for weeks - and as soon as I did - the solution popped out.

    Hope my dive into ODATA helps someone - they are fiddly though way, way more than SQL queries.

     

    The Doc

     

     

  • Mike_James's avatar
    Mike_James
    Ultimate Cover User
    2 months ago

    Hi The_Doc , if not resolved with filter, maybe lower case datetime will resolve those queries too?

  • The_Doc's avatar
    The_Doc
    Ultimate Cover User
    2 months ago

    Thanks Mike - ODATA is now making my life so much easier after I got them going - so simple yet, missed for so long. Have re-written all my API downloads to include ODATA  filter - just like Stored procedures - they cut your download payload and download times significantly - yep - I know I am preaching to the choir but - like finding a hidden overdrive button.

    So use to VBA being NOT case sensitive - and then VC#.NET being so pedantically case sensitive and then VB.NET not being. Wasn't sure whether I Arthur or Martha!.

    Regards The Doc

Looking for something else?

Search the Community Forum for answers or find your topic and get the conversation started!

Community home

Dig into MYOB Academy for free courses, learning paths and live events to help build your business with MYOB.