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());
}
- 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=?))
- 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=?))
- 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.
Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.
Referanslar:
www.mshowto.org
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