Dynamics Community Forum Thread Details

Creating Query object / SysDa API / SQL queries using Json structure

Is there anyway we can convert below dynamic json structure to either Query/SysDa object or SQL query using x++ ?

{
           "name": "CustInvoiceJour"
           "select": [
               {
                   "name": "InvoiceID"
               },
               {
                   "name": "InvoiceDate"
               }
           ],
           "fromTable": {
               "name": "CustInvoiceJour"
           },
           "where": [
               {
                   "booleanOperator": "OR",
                   "listOfExpr": [
                       {
                           "booleanOperator": "AND",
                           "listOfExpr": [
                               {
                                   "expr": {
                                       "column": {
                                           "name": "InvoiceID"

                                       },
                                       "operator": "=",
                                       "value": [
"1000",
"2000"
]
                                   }
                               },
                               {
                                   "expr": {
                                       "column": {
                                           "name": "InvoiceDate"
                                       },
                                       "operator": "=",
                                       "value": [
"06/04/2024"
]
                                   }
                               }
                           ]
                       },
                       {
                           "expr": {
                               "column": {
                                   "name": "InvoiceID"
                               },
                               "operator": "IN",
                               "value": [
"1000",
"2000"
]
                           }
                       },
                       {
                           "booleanOperator": "AND",
                           "listOfExpr": [
                               {
                                   "expr": {
                                       "column": {
                                           "name": "Voucher"
                                       },
                                       "operator": "NOT IN",
                                       "value": [
                                           "VF11"
                                       ]
                                   }
                               },
                               {
                                   "expr": {
                                       "column": {
                                           "name": "VoucherDate"
                                       },
                                       "operator": "=",
                                         "value": [
"06/04/2024"
]
                                   }
                               }
                           ]
                       }
                   ]
               }
           ],
           "order_by": [
               {
                   "column": {
                       "name": "InvoiceID"
                   },
                   "order": "DESCENDING"
               },
               {
                   "column": {
                       "name": "InvoiceDate"
                   },
                   "order": "DESCENDING"
               }
           ]
       }

The sql query should be constructed something like below based on the above json via X++:-

select invoiceid,invoicedate from custinvoicejour where ((invoiceId in ("1000","2000")
and invoicedate = '06/04/2024') or invoiceid in ("1000","2000")
or (voucher not in ("VF11") and voucherDate = "06/04/2024"))
order by invoiceID,invoiceDate desc

Also curious if the query can be constructed using SysDa api / Query object in x++.

Thanks,

Quick Links

Leaderboard