- Oct 27 Wed 2021 11:09
[D365] SysQuery
- Oct 15 Fri 2021 11:54
[D365FO] COC FORM BUTTON IN D365 CHAIN OF COMMAND
- Jun 02 Wed 2021 16:14
[Dynamics AX/365] 在Dialogs 內加入自訂Lookup 功能
class CustomizedLookup extends RunBase { DialogField fieldAccount; CustAccount custAccount; DialogField dialogField , dialogText1, dialogText2, dialogText3 , dialogText4; str refundreason, commentcs, nextOwnership; DGS_RefundType refundType; DGS_RefundRegister existingRefund; real _refundAmount, _maxRefundAmount; SalesId salesId; public Object dialog() { FormBuildStringControl _stringControl; //Dialog dialog = new Dialog(); DialogRunbase dialog = super();
str strMessage = strFmt("Are you sure you want to create refund register for %1, please provide below details.", salesId); str strTitle = "create refund register"; ; DGS_SalesTableCalculation salesTableCal = DGS_SalesTableCalculation::find(SalesId); select sum(refundamount) from existingRefund where existingRefund.salesid == SalesId; _refundAmount = (salesTableCal.TotalAmount > existingRefund.refundamount)? (salesTableCal.TotalAmount - existingRefund.refundamount):0; if(_refundAmount) { _maxRefundAmount = _refundAmount; Dialog.addText(strMessage); if(salesTableCal.TotalAmount == _refundAmount) { dialogField = dialog.addField(enumStr(DGS_RefundType)); DialogField.value(DGS_RefundType::Full); } else { dialogField = dialog.addField(enumStr(DGS_RefundType)); DialogField.value(DGS_RefundType::Partial); } // add lookup on dialog dialogText1 = dialog.addField(extendedTypeStr(ReasonComment)); _stringControl = dialogText1.control(); _stringControl.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(CustomizedLookup, refundTypelookup), this); //dialogText1 = dialog.addField(extendedTypeStr(String50), 'Refund Reason'); //dialogText1.value('Internal issue'); dialogText2 = dialog.addField(extendedTypeStr(String50), 'Comment (CS)'); dialogText2.limitText(500); dialogText3 = dialog.addField(extendedTypeStr(String50),'Next Ownership'); dialogText3.value('compliance'); dialogText4 = dialog.addField(extendedTypeStr(RealBase), 'Refund Amount'); dialogText4.value(_refundAmount); } else { Error(strFmt('%1 Full Refunded, please check via Refund Register.', SalesId)); } return dialog; } public boolean getFromDialog() { refundType = dialogField.value(); refundreason = dialogText1.value(); commentcs = dialogText2.value(); nextOwnership = dialogText3.value(); _refundAmount = dialogText4.value(); return super(); } public container pack() { return conNull(); } public void run() { if(_refundAmount > _maxRefundAmount) { Error(strFmt('Refund amount %1 over max refund amount %2.', _refundAmount , _maxRefundAmount)); } else { DGS_CreateRefundRegister::createRefundRegister(SalesId,refundType,refundreason,commentcs,nextOwnership, _refundAmount); } } public boolean unpack(container _packedClass) { return true; } public static void main(Args _args) { CustomizedLookup custAmountCalculation = new CustomizedLookup(); if (CustAmountCalculation.prompt()) { CustAmountCalculation.run(); } } public SalesId parmSalesId(SalesId _salesId = salesId) { SalesId = _salesId; return SalesId; } public void refundTypelookup(FormStringControl _control) { Query query = new Query(); QueryBuildDataSource queryBuildDataSource; SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(DGS_ReasonTable), _control); // Create lookup sysTableLookup.addLookupField(fieldNum(DGS_ReasonTable, ReasonType)); sysTableLookup.addLookupField(fieldNum(DGS_ReasonTable, Reason), true); // Setup query queryBuildDataSource = query.addDataSource(tableNum(DGS_ReasonTable)); queryBuildDataSource.addRange(fieldNum(DGS_ReasonTable,ReasonType)).value(enum2Str(DGS_ReasonType::Refund)); sysTableLookup.parmQuery(query); // Perform lookup sysTableLookup.performFormLookup(); } }
- Jan 18 Mon 2021 14:25
[Excel] 加入developer VB Code && 將幾萬條RECORD 分開不同SHEET 再分開不同XLS
- Dec 30 Wed 2020 10:48
[Dynamics AX/365] Postal Address SQL
Below are the sample queries to get the vendors and customers addresses and contact details. Change the code according to your requirement.
All Vendors
SELECT * FROM VENDTABLE WHERE VENDTABLE.DATAAREAID='CEU'
All Addresses - Vendor
SELECT * FROM DirPartyPostalAddressView JOIN VENDTABLE ON DirPartyPostalAddressView.PARTY =VENDTABLE.PARTY
WHERE VENDTABLE.DATAAREAID=''CEU'
All Addresses with Purpose
SELECT LOGISTICSLOCATIONROLE.*,DirPartyPostalAddressView.*,VENDTABLE.* FROM DirPartyPostalAddressView JOIN VENDTABLE ON DirPartyPostalAddressView.PARTY =VENDTABLE.PARTY
JOIN DIRPARTYLOCATIONROLE ON DIRPARTYLOCATIONROLE.PARTYLOCATION =DirPartyPostalAddressView.RECID
JOIN LOGISTICSLOCATIONROLE ON DIRPARTYLOCATIONROLE.LOCATIONROLE =LOGISTICSLOCATIONROLE.RECID
WHERE VENDTABLE.DATAAREAID='CEU'
(Click Organization administration > Setup > Global address book > Address and contact information purpose.)
All Contact Details - Vendor
select * from dirPartyContactInfoView JOIN VENDTABLE ON dirPartyContactInfoView.PARTY =VENDTABLE.PARTY
WHERE VENDTABLE.DATAAREAID='CEU'
--All Customers
--SELECT DIRPARTYTABLE.NAMEALIAS ,CUSTTABLE.* FROM CUSTTABLE JOIN DIRPARTYTABLE ON CUSTTABLE.PARTY =DIRPARTYTABLE.RECID
--WHERE CUSTTABLE.DATAAREAID='CEU'
--All Addresses - Customer
--SELECT DirPartyPostalAddressView.*,CUSTTABLE.PARTY FROM DirPartyPostalAddressView JOIN CUSTTABLE
--ON DirPartyPostalAddressView.PARTY =CUSTTABLE.PARTY
--WHERE CUSTTABLE.DATAAREAID='CEU'
--All Addresses with Purpose - Customer
--SELECT LOGISTICSLOCATIONROLE.NAME,DirPartyPostalAddressView.PARTY,CUSTTABLE.PARTY FROM DirPartyPostalAddressView JOIN CUSTTABLE
--ON DirPartyPostalAddressView.PARTY =CUSTTABLE.PARTY
--JOIN DIRPARTYLOCATIONROLE ON DIRPARTYLOCATIONROLE.PARTYLOCATION =DirPartyPostalAddressView.RECID
--JOIN LOGISTICSLOCATIONROLE ON DIRPARTYLOCATIONROLE.LOCATIONROLE =LOGISTICSLOCATIONROLE.RECID
--WHERE CUSTTABLE.DATAAREAID='CEU'
--All Contact Details - Customer
--select dirPartyContactInfoView.* from dirPartyContactInfoView JOIN CUSTTABLE ON dirPartyContactInfoView.PARTY =CUSTTABLE.PARTY
--WHERE CUSTTABLE.DATAAREAID='CEU'
--Bank Details Customer
--SELECT distinct CUSTTABLE.PARTY ,CUSTTABLE.DATAAREAID ENTITY, CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME,Address.ADDRESS ,
--CUSTTABLE.CURRENCY ,CUSTTABLE.CUSTGROUP ,
--CUSTTABLE.PAYMTERMID,CUSTTABLE.TAXGROUP VATGROUP ,CUSTTABLE.CASHDISC ,
--VendBankAccount.ACCOUNTID BankAccount ,VendBankAccount.NAME 'Bank Name' ,VendBankAccount.ACCOUNTNUM 'Bank account number',
--VendBankAccount.RegistrationNum 'Routing Number',VendBankAccount.SWIFTNo,VendBankAccount.BankIBAN
--from CUSTTABLE left outer JOIN VendBankAccount ON VendBankAccount.VENDACCOUNT = CUSTTABLE.ACCOUNTNUM --AND VendBankAccount.ACCOUNTID = CUSTTABLE.BANKACCOUNT
--left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY
--left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = DIRPARTYTABLE.PRIMARYADDRESSLOCATION
-- WHERE CUSTTABLE.DATAAREAID IN ('CEU') --and CUSTTABLE.ACCOUNTNUM ='test033'
--Order by CUSTTABLE.DATAAREAID,CUSTTABLE.ACCOUNTNUM
Below SQL Query to extract a quick customer contact list from Dynamics AX 2012.
REF
REF
SELECT
VENDTABLE.ACCOUNTNUM AS CUSTID,
DIRPARTYTABLE.NAME AS CUSTNAME,
CASE LOGISTICSELECTRONICADDRESS.TYPE WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' END AS CONTACTTYPE,
LOGISTICSELECTRONICADDRESS.DESCRIPTION AS CONTACTNAME,
LOGISTICSELECTRONICADDRESS.LOCATOR AS CONTACTDETAILS
FROM DIRPARTYTABLE AS DIRPARTYTABLE
INNER JOIN VENDTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY
INNER JOIN DIRPARTYLOCATION ON DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY
INNER JOIN LOGISTICSELECTRONICADDRESS ON DIRPARTYLOCATION.LOCATION = LOGISTICSELECTRONICADDRESS.LOCATION
WHERE VENDTABLE.DATAAREAID='CEU'
ORDER BY DIRPARTYTABLE.NAME
Vendor Bank Details SQL Query in AX
Vendor Bank Address in X++ Code and save it in CSV in AX
Fetch Customers Primary Address or Vendor Primary Address having Transactions (SQL SERVER Query) in AX D365
Fetch Product Master
SELECT B.DISPLAYPRODUCTNUMBER,DESCRIPTION,NAME,B.SEARCHNAME FROM ECORESPRODUCTTRANSLATION A JOIN ECORESPRODUCT B ON A.PRODUCT =B.RECID WHERE B.DISPLAYPRODUCTNUMBER in ('A0001','A0002')
--Storage Dimensions
select ECORESSTORAGEDIMENSIONGROUPITEM.ITEMID ,ECORESSTORAGEDIMENSIONGROUP.NAME from INVENTTABLE JOIN ECORESSTORAGEDIMENSIONGROUPITEM ON
INVENTTABLE.ITEMID =ECORESSTORAGEDIMENSIONGROUPITEM.ITEMID AND
INVENTTABLE.DATAAREAID =ECORESSTORAGEDIMENSIONGROUPITEM.ITEMDATAAREAID
JOIN ECORESSTORAGEDIMENSIONGROUP ON
ECORESSTORAGEDIMENSIONGROUPITEM.STORAGEDIMENSIONGROUP =ECORESSTORAGEDIMENSIONGROUP.RECID
WHERE INVENTTABLE.DATAAREAID='CEU'
--Tracking Dimensions
select ECORESTRACKINGDIMENSIONGROUPITEM.ITEMID ,ECORESTRACKINGDIMENSIONGROUP.NAME from INVENTTABLE JOIN ECORESTRACKINGDIMENSIONGROUPITEM ON
INVENTTABLE.ITEMID =ECORESTRACKINGDIMENSIONGROUPITEM.ITEMID AND
INVENTTABLE.DATAAREAID =ECORESTRACKINGDIMENSIONGROUPITEM.ITEMDATAAREAID
JOIN ECORESTRACKINGDIMENSIONGROUP ON
ECORESTRACKINGDIMENSIONGROUPITEM.TRACKINGDIMENSIONGROUP =ECORESTRACKINGDIMENSIONGROUP.RECID
WHERE INVENTTABLE.DATAAREAID='CEU'
--Item Model Group Units
select * from INVENTTABLEMODULE where DATAAREAID='CEU'
--Released Products
Select * from INVENTTABLE where DATAAREAID='CEU'
--Item Group
select * from INVENTITEMGROUPITEM where ItemDATAAREAID='CEU'
Vend Open Trans
SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount
FROM VENDTRANS
where VENDTRANS.closed = 0
AND ((VendTrans.TransType = 36)
OR (VendTrans.TransType = 3) --Purch
OR (VendTrans.TransType = 14)) --Vend
AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))
--AND VENDTRANS.ACCOUNTNUM = '1001' AND VENDTRANS.DATAAREAID = 'USMF'
SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount ,*
FROM VENDTRANS where
--where VENDTRANS.closed = 0
--AND ((VendTrans.TransType = 36) OR (VendTrans.TransType = 3)
-- OR (VendTrans.TransType = 14)) AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))
VENDTRANS.DATAAREAID = 'pui'
SELECT VENDTRANS.ACCOUNTNUM,VendTrans.VOUCHER,VendTrans.Invoice,VendTrans.TRANSDATE AS DueDate,VendTrans.AmountCur, VendTrans.AmountCur-VendTrans.SETTLEAMOUNTCUR as BalanceAmount
FROM VENDTRANS
where VENDTRANS.closed = 0 and
VENDTRANS.DATAAREAID = 'pui'
AND ((VendTrans.TransType = 36) --RTax25_BadDebtDebitAmortisation
OR (VendTrans.TransType = 3) --Purch
OR (VendTrans.TransType = 14)) --Vend
AND ((VendTrans.AmountCur<=0)) AND ((VendTrans.Approved = 1))
--AND VENDTRANS.ACCOUNTNUM = '1001' AND VENDTRANS.DATAAREAID = 'USMF'
- Dec 03 Thu 2020 14:08
[Dynamics AX/365] 簡易解開Workspace LOCK
1. 找尋workspace, server 的資料:
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE>TF.exe workspaces /owner:* /computer:*
- Nov 10 Tue 2020 15:33
[Dynamics AX/365] 如何自定義標準EXCEL模板 - Customize standard excel template
目的: 加financial dimension 入去excel template
1. 第一, 要先將自家的financial dimension 加入ODATA (之前有篇教學)
- Nov 03 Tue 2020 15:14
[Dynamics AX/365] 用SQL 尋找AXDB 內table id, field id...
- Oct 30 Fri 2020 15:21
[Dynamics AX/365] Create a Cloud Hosted D365 for Operations Environment創建用於操作環境的雲託管D365
- Open lcs.dynamics.com in your web browser.
- Log in with your credentials.
- Select the LCS project you want to add a Azure subscription to from the list.
- Click the Hamburger menu button.
- Select Cloud-hosted environments from the list.
- On the Cloud-hosted environments page, click Add.
- On the Select environment topology page, select Azure.
- On the next Select environment topology page, select DEMO. (Now as a side note, you can select DEVTEST - however this requires that you have configured Visual Studio Team Services in your LCS project, which I have not shown yet.) I will post again about the differences between the two environments.)
- On the next Select environment topology page, select Dynamics 365 for Operations - Develop (Release 1611, Platform Update 4). (This is the most current release - but obviously you can pick any version of the software you want.)
- On the Deploy environment page, type a unique name in the Environment name field.
- In the grid for the virtual machines, Use the Size drop-down to select D13 v2 for both images. (This is the biggest, fastest server you can choose. This is not required, but you can pick any size you want really. This will change your costs for hosting the environment as well.)
- Click Advanced settings.
- Oct 30 Fri 2020 10:45
[Dynamics AX/365] CompView 例子
public class InventSum_View extends common
{
private static str prepareInventPhyStr(str key)
{
#define.ViewName(InventSum_View)
#define.DataSourceName("InventSum")
#define.FieldPostedQty("postedQty")
#define.FieldPostedValue("PostedValue")
#define.FieldPhysicalValue("PhysicalValue")
str sReturn,
postedQty,
postedValue,
PhysicalValue;
DictView dictView2;
// Construct a DictView object for the present view.
dictView2 = new DictView(tableNum(#ViewName));
postedQty = dictView2.computedColumnString
(#DataSourceName,
#FieldPostedQty,
FieldNameGenerationMode::FieldList,
true);
postedValue = dictView2.computedColumnString
(#DataSourceName,
#FieldPostedValue,
FieldNameGenerationMode::FieldList,
true);
physicalValue = dictView2.computedColumnString
(#DataSourceName,
#FieldPhysicalValue,
FieldNameGenerationMode::FieldList,
true);
switch(key)
{
case "Cost":
sReturn = "CASE WHEN sum(" + postedQty + ") = 0 OR sum(" + postedQty + ") IS NULL "
+ " THEN 0 "
+ " ELSE sum(" + postedValue + ") / sum(" + postedQty + ")"
+ " END";
break;
case "PostedQty":
sReturn ="sum("
+ postedQty
+ ")" ;
break;
case "PostedValue":
sReturn ="sum("
+ PostedValue
+ ")" ;
break;
}
return sReturn;
}
private static server str compCostMethod()
{
str sReturn;
sReturn = InventSum_View::prepareInventPhyStr("Cost");
return sReturn;
}
private static server str compPosteQtyMethod()
{
str sReturn;
sReturn = InventSum_View::prepareInventPhyStr("PostedQty");
return sReturn;
}
private static server str compPostedValueMethod()
{
str sReturn;
sReturn = InventSum_View::prepareInventPhyStr("PostedValue");
return sReturn;
}
}
2. CAL ROW VIEW: