CODEWITHSUNDEEP
axaptax++dynamics-ax-2009
Aitor Ramos Pajares
Aitor Ramos Pajares

Reputation: 361

Expressions in a querybuildRange

Hi I am trying to do this query:

Select ProjInvoiceJour 
where NOT (ProjInvoiceJour.ProjInvoiceType == ProjInvoiceType::Onaccount && ProjInvoiceJour.CountryRegionID != "ES")

But I need to do it whit querybuilder:

qbds.addRange(fieldnum(ProjInvoiceJour, ProjInvoiceType)).value(strfmt('!%1',
                           strfmt("((%1.%2 == %3) && (%4.%5 != '%6'))",
                                    tablestr(ProjInvoiceJour),
                                    fieldstr(ProjInvoiceJour, ProjInvoiceType),
                                    any2int(ProjInvoiceType::OnAccount),
                                    tablestr(ProjInvoiceJour),
                                    fieldstr(ProjInvoiceJour, CountryRegionID),
                                    queryvalue("ES"))));

But the query has some error:

SELECT * FROM ProjInvoiceJour WHERE ((NOT (ProjInvoiceType = 255)))

Thanks

Upvotes: 1

Views: 482

Answers (1)

Jan B. Kjeldsen
Jan B. Kjeldsen

Reputation: 18051

The law of De Morgan comes to rescue:

select ProjInvoiceJour 
    where !(ProjInvoiceJour.ProjInvoiceType == ProjInvoiceType::Onaccount && 
            ProjInvoiceJour.CountryRegionID != 'ES')

is equivalent to: 

select ProjInvoiceJour 
    where ProjInvoiceJour.ProjInvoiceType != ProjInvoiceType::Onaccount ||
          ProjInvoiceJour.CountryRegionID == 'ES'

Or in a query:

qbds.addRange(fieldnum(ProjInvoiceJour, ProjInvoiceType)).value(strfmt('((%1.%2 != %3) || (%4.%5 == "%6"))',
                                tablestr(ProjInvoiceJour),
                                fieldstr(ProjInvoiceJour, ProjInvoiceType),
                                0+ProjInvoiceType::OnAccount,
                                tablestr(ProjInvoiceJour),
                                fieldstr(ProjInvoiceJour, CountryRegionID),
                                'ES'));

Upvotes: 1

Related Questions