locked
Retrive records using ODATA with SQL functions RRS feed

  • Question

  • Hi,

    I want to use SQL functions (for instance replace function) while retrieving records using ODATA endpoint using javascript. Like this:

        parent.SDK.REST.retrieveMultipleRecords(
            "$select=AccountId,Name,Telephone1&$filter=" + "replace(Telephone1,'-','') eq '12345678'",
    
            function (accountresults) {
    
            },
            errorHandler,
    
            function () {
    
            });

    But this is ending up with 404 bad request. Is there any way to achieve the same using any other technique?


    blog: <a href="http://technologynotesforyou.wordpress.com">http://technologynotesforyou.wordpress.com</a> | skype: ali.net.pk


    • Edited by Khadim Ali Thursday, February 26, 2015 1:56 PM
    Thursday, February 26, 2015 1:55 PM

Answers

  • ODATA only supports simple comparison operators, and startswith, endswith and substringof. There are 2 approaches you could use without modifying the data:

    1. Have a less selective filter in ODATA, then filter the rest of the data in javascript. In this example, you could use ODATA to find all records where the telephone1 field has each of '1', '2', '3' etc, then use javascript to do the replace comparison. It depends how many records you have as to whether this is practical
    2. Write a plugin on RetrieveMultiple that applies the functions you want. This is not necessarily simple, and you'd need to determine how you identify in the query that you want to use a function like replace

    Another option could be to use a plugin when data is saved to strip out the characters you don't want, and store the results in a different field that you could then query on


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, February 26, 2015 9:58 PM
    Moderator

All replies

  • it's not possible (neither in C# with a RetrieveMultiple)

    My blog: www.crmanswers.net - Rockstar 365 Profile

    • Proposed as answer by Guido PreiteMVP Thursday, February 26, 2015 2:06 PM
    Thursday, February 26, 2015 2:05 PM
  • Hello Guido,

    Thanks for your reply.

    Any other technique to use this?

    One possible way I can foresee is using LINQ objects in C# and returning the data in JSON/XML Format to the JavaScipt. If so, any possible documentation for this?


    blog: <a href="http://technologynotesforyou.wordpress.com">http://technologynotesforyou.wordpress.com</a> | skype: ali.net.pk

    Thursday, February 26, 2015 2:12 PM
  • ODATA only supports simple comparison operators, and startswith, endswith and substringof. There are 2 approaches you could use without modifying the data:

    1. Have a less selective filter in ODATA, then filter the rest of the data in javascript. In this example, you could use ODATA to find all records where the telephone1 field has each of '1', '2', '3' etc, then use javascript to do the replace comparison. It depends how many records you have as to whether this is practical
    2. Write a plugin on RetrieveMultiple that applies the functions you want. This is not necessarily simple, and you'd need to determine how you identify in the query that you want to use a function like replace

    Another option could be to use a plugin when data is saved to strip out the characters you don't want, and store the results in a different field that you could then query on


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, February 26, 2015 9:58 PM
    Moderator