Archive for Mart 25th, 2022

Dynamics 365 Finance and Operations Sorguya dinamik filtre eklemek?

Bu yazıda Dynamics 365 Finance and Operations içinde yazdığınız herhangi bir sorguya filtre eklerken eğer sadece değişken doluysa filtre eklemek istiyorsanız kullanabileceğiniz bir teknikten bahsedeceğim.

Örnek bir kodu inceleyelim. Öncelikle Generateonly ile sorgunuzu sadece hazırlamış oluyorsunuz çalıştırılmıyor bu sayede SQL çıktısını görebiliyorsunuz.

static void DmrFDQuerySQLStatment(Args _args)

{

CustAccount custAccount;

CustTable   custTable;

custAccount = “C00003″;

select generateonly custTable

where custTable.AccountNum == custAccount;

// 1. sorgu

info(custTable.getSQLStatement());

// Eğer değişkenin boş mu dolu mu olduğunu kontrol etmek istersem if kullanmalıyım. Birden çok değişken olduğunda bu çok karışık bir hal alabilir.

if(custAccount != “”)

{

select generateonly custTable

where custTable.AccountNum == custAccount;

info(custTable.getSQLStatement());

}

else

{

select generateonly custTable ;

info(custTable.getSQLStatement());

}

// Bu şekilde yazarak eğer değişken doluysa ekliyor. Eğişken boşsa hiç eklemiyor if e ihtiyaç duymadan işimizi hallediyoru.

// 3. sorgu

custAccount = “”;

select generateonly custTable

where (( custTable.AccountNum == custAccount && custAccount ) || (!custAccount));

info(custTable.getSQLStatement());

}

  1. sorgu

SELECT T1.ACCOUNTNUM,T1.INVOICEACCOUNT,T1.CUSTGROUP,T1.LINEDISC,

T1.PAYMTERMID,T1.CASHDISC,T1.CURRENCY,

T1.INTERCOMPANYAUTOCREATEORDERS,T1.SALESGROUP,T1.BLOCKED,

T1.ONETIMECUSTOMER,T1.ACCOUNTSTATEMENT,

T1.CREDITMAX,T1.MANDATORYCREDITLIMIT,T1.VENDACCOUNT,

T1.PRICEGROUP,T1.MULTILINEDISC,T1.ENDDISC,T1.VATNUM,

T1.INVENTLOCATION,T1.DLVTERM,T1.DLVMODE,T1.MARKUPGROUP,

T1.CLEARINGPERIOD,T1.FREIGHTZONE,T1.CREDITRATING,T1.TAXGROUP,

T1.STATISTICSGROUP,T1.PAYMMODE,T1.COMMISSIONGROUP,

T1.BANKACCOUNT,T1.PAYMSCHED,T1.CONTACTPERSONID,T1.INVOICEADDRESS,

T1.OURACCOUNTNUM,T1.SALESPOOLID,T1.INCLTAX,T1.CUSTITEMGROUPID,

T1.NUMBERSEQUENCEGROUP,T1.PAYMDAYID,T1.LINEOFBUSINESSID,

T1.DESTINATIONCODEID,T1.GIROTYPE,T1.SUPPITEMGROUPID,

T1.GIROTYPEINTERESTNOTE,T1.TAXLICENSENUM,T1.WEBSALESORDERDISPLAY,

T1.PAYMSPEC,T1.BANKCENTRALBANKPURPOSETEXT,T1.BANKCENTRALBANKPURPOSECODE,

T1.INTERCOMPANYALLOWINDIRECTCREATION,

T1.PACKMATERIALFEELICENSENUM,T1.TAXBORDERNUMBER_FI,T1.EINVOICEEANNUM,

T1.FISCALCODE,T1.DLVREASON,T1.FORECASTDMPINCLUDE,

T1.GIROTYPECOLLECTIONLETTER,T1.SALESCALENDARID,T1.CUSTCLASSIFICATIONID,

T1.INTERCOMPANYDIRECTDELIVERY,T1.ENTERPRISENUMBER,

T1.SHIPCARRIERACCOUNT,T1.GIROTYPEPROJINVOICE,T1.INVENTSITEID,

T1.ORDERENTRYDEADLINEGROUPID,T1.SHIPCARRIERID,

T1.SHIPCARRIERFUELSURCHARGE,T1.SHIPCARRIERBLINDSHIPMENT,

T1.SHIPCARRIERACCOUNTCODE,T1.GIROTYPEFREETEXTINVOICE,

T1.SYNCENTITYID,T1.SYNCVERSION,T1.SALESDISTRICTID,

T1.SEGMENTID,T1.SUBSEGMENTID,T1.RFIDITEMTAGGING,T1.RFIDCASETAGGING,

T1.RFIDPALLETTAGGING,T1.COMPANYCHAINID,T1.COMPANYIDSIRET,

T1.PARTY,T1.IDENTIFICATIONNUMBER,T1.PARTYCOUNTRY,T1.PARTYSTATE,

T1.ORGID,T1.PAYMIDTYPE,T1.FACTORINGACCOUNT,T1.DEFAULTDIMENSION,

T1.CUSTEXCLUDECOLLECTIONFEE,T1.CUSTEXCLUDEINTERESTCHARGES,

T1.COMPANYNAFCODE,T1.BANKCUSTPAYMIDTABLE,T1.GIROTYPEACCOUNTSTATEMENT,

T1.MAINCONTACTWORKER,T1.CREDITCARDADDRESSVERIFICATION,

T1.CREDITCARDCVC,T1.CREDITCARDADDRESSVERIFICATIONVOID,

T1.CREDITCARDADDRESSVERIFICATIONLEVEL,T1.COMPANYTYPE_MX,T1.RFC_MX,T1.CURP_MX,

T1.STATEINSCRIPTION_MX,T1.RESIDENCEFOREIGNCOUNTRYREGIONID_IT,

T1.BIRTHCOUNTYCODE_IT,T1.BIRTHDATE_IT,T1.BIRTHPLACE_IT,T1.EINVOICE,

T1.CCMNUM_BR,T1.CNPJCPFNUM_BR,T1.PBACUSTGROUPID,T1.IENUM_BR,

T1.SUFRAMANUMBER_BR,T1.SUFRAMA_BR,T1.CUSTFINALUSER_BR,T1.INTERESTCODE_BR,

T1.FINECODE_BR,T1.SUFRAMAPISCOFINS_BR,T1.TAXWITHHOLDCALCULATE_TH,

T1.TAXWITHHOLDGROUP_TH,T1.CONSDAY_JP,T1.NIT_BR,T1.INSSCEI_BR,T1.CNAE_BR,

T1.ICMSCONTRIBUTOR_BR,T1.SERVICECODEONDLVADDRESS_BR,

T1.INVENTPROFILETYPE_RU,T1.INVENTPROFILEID_RU,T1.TAXWITHHOLDCALCULATE_IN,

T1.UNITEDVATINVOICE_LT,T1.ENTERPRISECODE,T1.COMMERCIALREGISTERSECTION,

T1.COMMERCIALREGISTERINSETNUMBER,T1.COMMERCIALREGISTER,

T1.REGNUM_W,T1.ISRESIDENT_LV,T1.INTBANK_LV,T1.PAYMENTREFERENCE_EE,

T1.PACKAGEDEPOSITEXCEMPT_PL,T1.FEDNONFEDINDICATOR,

T1.IRS1099CINDICATOR,T1.AGENCYLOCATIONCODE,T1.FEDERALCOMMENTS,

T1.USEPURCHREQUEST,T1.MCRMERGEDPARENT,T1.MCRMERGEDROOT,

T1.AFFILIATED_RU,T1.CASHDISCBASEDAYS,T1.CUSTTRADINGPARTNERCODE,

T1.CUSTWHTCONTRIBUTIONTYPE_BR,T1.DAXINTEGRATIONID,

T1.DEFAULTDIRECTDEBITMANDATE,T1.DEFAULTINVENTSTATUSID,

T1.ENTRYCERTIFICATEREQUIRED_W,T1.EXPORTSALES_PL,T1.EXPRESSBILLOFLADING,

T1.FISCALDOCTYPE_PL,T1.FOREIGNRESIDENT_RU,

T1.GENERATEINCOMINGFISCALDOCUMENT_BR,T1.INVOICEPOSTINGTYPE_RU,

T1.ISSUEOWNENTRYCERTIFICATE_W,T1.ISSUERCOUNTRY_HU,

T1.LVPAYMTRANSCODES,T1.MANDATORYVATDATE_PL,T1.PASSPORTNO_HU,

T1.PDSCUSTREBATEGROUPID,T1.PDSFREIGHTACCRUED,T1.PDSREBATETMAGROUP,

T1.TAXPERIODPAYMENTCODE_PL,T1.USECASHDISC,T1.FIELD1,

T1.MODIFIEDDATETIME,T1.DEL_MODIFIEDTIME,T1.MODIFIEDBY,

T1.CREATEDDATETIME,T1.DEL_CREATEDTIME,T1.RECVERSION,T1.PARTITION,

T1.RECID,T1.MEMO FROM CUSTTABLE T1 WHERE (((PARTITION=?) AND (DATAAREAID=?)) AND (ACCOUNTNUM=?))

  1. sorgu

SELECT T1.ACCOUNTNUM,T1.INVOICEACCOUNT,T1.CUSTGROUP,T1.LINEDISC,

T1.PAYMTERMID,T1.CASHDISC,T1.CURRENCY,T1.INTERCOMPANYAUTOCREATEORDERS,

T1.SALESGROUP,T1.BLOCKED,T1.ONETIMECUSTOMER,T1.ACCOUNTSTATEMENT,

T1.CREDITMAX,T1.MANDATORYCREDITLIMIT,T1.VENDACCOUNT,

T1.PRICEGROUP,T1.MULTILINEDISC,T1.ENDDISC,T1.VATNUM,

T1.INVENTLOCATION,T1.DLVTERM,T1.DLVMODE,T1.MARKUPGROUP,T1.CLEARINGPERIOD,

T1.FREIGHTZONE,T1.CREDITRATING,T1.TAXGROUP,T1.STATISTICSGROUP,

T1.PAYMMODE,T1.COMMISSIONGROUP,T1.BANKACCOUNT,T1.PAYMSCHED,

T1.CONTACTPERSONID,T1.INVOICEADDRESS,T1.OURACCOUNTNUM,

T1.SALESPOOLID,T1.INCLTAX,T1.CUSTITEMGROUPID,T1.NUMBERSEQUENCEGROUP,

T1.PAYMDAYID,T1.LINEOFBUSINESSID,T1.DESTINATIONCODEID,

T1.SUPPITEMGROUPID,T1.TAXLICENSENUM,T1.WEBSALESORDERDISPLAY,T1.PAYMSPEC,

T1.BANKCENTRALBANKPURPOSETEXT,T1.BANKCENTRALBANKPURPOSECODE,

T1.INTERCOMPANYALLOWINDIRECTCREATION,T1.PACKMATERIALFEELICENSENUM,

T1.DLVREASON,T1.FORECASTDMPINCLUDE,T1.SALESCALENDARID,

T1.CUSTCLASSIFICATIONID,T1.INTERCOMPANYDIRECTDELIVERY,T1.SHIPCARRIERACCOUNT,

T1.INVENTSITEID,T1.ORDERENTRYDEADLINEGROUPID,

T1.SHIPCARRIERID,T1.SHIPCARRIERFUELSURCHARGE,T1.SHIPCARRIERBLINDSHIPMENT,

T1.SHIPCARRIERACCOUNTCODE,T1.SYNCENTITYID,T1.SYNCVERSION,

T1.SALESDISTRICTID,T1.SEGMENTID,T1.SUBSEGMENTID,T1.RFIDITEMTAGGING,

T1.RFIDCASETAGGING,T1.RFIDPALLETTAGGING,T1.COMPANYCHAINID,

T1.PARTY,T1.IDENTIFICATIONNUMBER,T1.PARTYCOUNTRY,T1.PARTYSTATE,

T1.DEFAULTDIMENSION,T1.CUSTEXCLUDECOLLECTIONFEE,

T1.CUSTEXCLUDEINTERESTCHARGES,T1.MAINCONTACTWORKER,

T1.CREDITCARDADDRESSVERIFICATION,T1.CREDITCARDCVC,

T1.CREDITCARDADDRESSVERIFICATIONVOID,T1.CREDITCARDADDRESSVERIFICATIONLEVEL,

T1.PBACUSTGROUPID,T1.FEDNONFEDINDICATOR,T1.IRS1099CINDICATOR,

T1.AGENCYLOCATIONCODE,T1.FEDERALCOMMENTS,T1.USEPURCHREQUEST,

T1.MCRMERGEDPARENT,T1.MCRMERGEDROOT,T1.CASHDISCBASEDAYS,

T1.CUSTTRADINGPARTNERCODE,T1.DAXINTEGRATIONID,T1.DEFAULTDIRECTDEBITMANDATE,

T1.DEFAULTINVENTSTATUSID,T1.ENTRYCERTIFICATEREQUIRED_W,

T1.EXPRESSBILLOFLADING,T1.ISSUEOWNENTRYCERTIFICATE_W,T1.PDSCUSTREBATEGROUPID,

T1.PDSFREIGHTACCRUED,T1.PDSREBATETMAGROUP,T1.USECASHDISC,

T1.FIELD1,T1.MODIFIEDDATETIME,T1.DEL_MODIFIEDTIME,T1.MODIFIEDBY,T1.CREATEDDATETIME,

T1.DEL_CREATEDTIME,T1.RECVERSION,T1.PARTITION,T1.RECID,

T1.MEMO FROM CUSTTABLE T1 WHERE ((PARTITION=?) AND (DATAAREAID=?))

  1. Sorguda gördüğünüz gibi AccountNum filtresi verilmiyor. Değişkene değer verip tekrar çalıştırırsanız filtrenin eklendiğini görebilirsiniz. Değerlerin görünmemesi normal. SQL üzerinden bakarsanız değerleri de görebilirsiniz.

Selamlar.

www.fatihdemirci.net

TAGs: Microsoft Life Cycle Services, LCS, Azure, Azure DevOps, Microsoft Dynamics 365, MsDyn365FO, MsDyn365CE, MsDyn365, Dynamics 365 nedir, Dynamics 365 ERP, Dynamics 365 CRM, X++, Query, Filter