D365 BC OData: Filtering for boolean fields in the correct way

Somehow, Microsoft’s documentation for using OData service in Business Central does not give enough information for the proper usage of the OData’s filter for boolean fields. That’s when Postman becomes your best friend, and you are starting arguing about how to compare equality to a boolean field using filters. Oh, have you looked up stackoverflow already? Some say, that you will need to make a new column in the page, and put the boolean value as a string, to be able to use it in filter with string compare!! 😀 Others say, you will need to send the equality compare statement with binary marks, 1 for true and 0 for false.
None of the solutions mentioned will work. I mean the string compare method should work, but I recommend to use it for no one.

My test results

// Request 1
https://<<HOST>>/ODataV4/Company('CRONUS International Ltd.')/<<TABLENAME>>?$select=Entry_No,Item_No,Lot_No,Positive,Qty_to_Handle_Base,Quantity_Base,Serial_No,Source_ID,Source_Ref_No&$filter=Entry_No eq 10 and Positive eq 'Yes'

"code": "BadRequest",
        "message": "A binary operator with incompatible types was detected. Found operand types 'Edm.Boolean' and 'Edm.String' for operator kind 'Equal'.  CorrelationId:  c409ac25-b53a-4fcd-ac7d-c87fe6999666."

// Request 2
https://<<HOST>>/ODataV4/Company('CRONUS International Ltd.')/<<TABLENAME>>?$select=Entry_No,Item_No,Lot_No,Positive,Qty_to_Handle_Base,Quantity_Base,Serial_No,Source_ID,Source_Ref_No&$filter=Entry_No eq 10 and Positive eq Yes

 "code": "BadRequest",
        "message": "Could not find a property named 'Yes' on type 'NAV.<<TABLENAME>>'.  CorrelationId:  beb9f4bb-cec5-4d40-84b9-9802d45a5fa9."

// Request 3
https://<<HOST>>/ODataV4/Company('CRONUS International Ltd.')/<<TABLENAME>>?$select=Entry_No,Item_No,Lot_No,Positive,Qty_to_Handle_Base,Quantity_Base,Serial_No,Source_ID,Source_Ref_No&$filter=Entry_No eq 10 and Positive eq yes

        "code": "BadRequest",
        "message": "Could not find a property named 'yes' on type 'NAV.<<TABLENAME>>'.  CorrelationId:  3f96be5e-f489-43fe-a24c-ea8255cc1cc1."

// Request 4
https://<<HOST>>/ODataV4/Company('CRONUS International Ltd.')/<<TABLENAME>>?$select=Entry_No,Item_No,Lot_No,Positive,Qty_to_Handle_Base,Quantity_Base,Serial_No,Source_ID,Source_Ref_No&$filter=Entry_No eq 10 and Positive eq 1

 "code": "BadRequest",
        "message": "A binary operator with incompatible types was detected. Found operand types 'Edm.Boolean' and 'Edm.Int32' for operator kind 'Equal'.  CorrelationId:  d9f9a696-0f6d-48c4-89f5-60d784126810."

// Request 5
https://<<HOST>>/ODataV4/Company('CRONUS International Ltd.')/<<TABLENAME>>?$select=Entry_No,Item_No,Lot_No,Positive,Qty_to_Handle_Base,Quantity_Base,Serial_No,Source_ID,Source_Ref_No&$filter=Entry_No eq 10 and Positive eq True

    "error": {
        "code": "BadRequest",
        "message": "Could not find a property named 'True' on type 'NAV.<<TABLENAME>>'.  CorrelationId:  b567a22c-6fa8-4333-801d-1b3deb42a7fb."

// Request 6
https://<<HOST>>/ODataV4/Company('CRONUS International Ltd.')/<<TABLENAME>>?$select=Entry_No,Item_No,Lot_No,Positive,Qty_to_Handle_Base,Quantity_Base,Serial_No,Source_ID,Source_Ref_No&$filter=Entry_No eq 10 and Positive eq 'True'

        "code": "BadRequest",
        "message": "A binary operator with incompatible types was detected. Found operand types 'Edm.Boolean' and 'Edm.String' for operator kind 'Equal'.  CorrelationId:  94ae066f-8a9f-4c39-8c4e-667c8d15579f."

// Request 7
https://<<HOST>>/ODataV4/Company('CRONUS International Ltd.')/<<TABLENAME>>?$select=Entry_No,Item_No,Lot_No,Positive,Qty_to_Handle_Base,Quantity_Base,Serial_No,Source_ID,Source_Ref_No&$filter=Entry_No eq 10 and Positive eq 'true'

        "code": "BadRequest",
        "message": "A binary operator with incompatible types was detected. Found operand types 'Edm.Boolean' and 'Edm.String' for operator kind 'Equal'.  CorrelationId:  f08024a2-00f1-474c-957d-8ce1c15bf452."

The solution

The boolean operators in OData filters are CASE SENSITIVE, and marked with true and false. You won’t need to put in single quotation marks, or use binary marks like 1 or 0. The solution is simple:

// Request 8: 

https://<<HOST>>/ODataV4/Company('CRONUS International Ltd.')/<<TABLENAME>>?$select=Entry_No,Item_No,Lot_No,Positive,Qty_to_Handle_Base,Quantity_Base,Serial_No,Source_ID,Source_Ref_No&$filter=Entry_No eq 10 and Positive eq true

// Response 8:

    "value": [
        {
            "Entry_No": 10,
            "Positive": true,
            "Serial_No": "555",
            "Lot_No": "555",
            "Quantity_Base": 1,
            "Qty_to_Handle_Base": 1,
            "Item_No": "1",
            "Source_Ref_No": 10000,
            "Source_ID": "1001"
        }

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.