呆子獅的夢想世界

QueryBuildRange qbr;
 

COC FORM BUTTON IN D365 CHAIN OF COMMAND
 

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();
}
}

加入developer VB Code
Developer Tab

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'

1. 找尋workspace, server 的資料:
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE>TF.exe workspaces /owner:* /computer:*

目的: 加financial dimension 入去excel template
1. 第一, 要先將自家的financial dimension 加入ODATA (之前有篇教學)

Table name from table id
以下語句返回數據庫中 table id  9669的名稱，如上所述，我們正在尋找field id 為0的行。

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.

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;
}
}

[DataEventHandler(tableStr(CustTable), DataEventType::Inserting)]
public static void CustTable_onInserting(Common sender, DataEventArgs e)
{
CustTable custTable = sender as CustTable;
custTable.inventSiteId = InventParameters::find().DGS_MainInventSiteId;
CustTable.InventLocation = InventParameters::find().DGS_MainInventLocationId;
if(!CustTable.CustGroup)
CustTable.CustGroup = THK_FileInterfaceParameters::find().CustGroupId;
if(!CustTable.DGS_CustNature)
CustTable.DGS_CustNature = THK_FileInterfaceParameters::find().DGS_CustNature;
}
/// <summary>
///
/// </summary>
/// <param name="args"></param>
[PreHandlerFor(tableStr(CustTable), tableMethodStr(CustTable, validateWrite))]
public static void CustTable_Pre_validateWrite(XppPrePostArgs args)
{
CustTable custTable = args.getThis() as CustTable;
if(!CustTable.CustGroup)
CustTable.CustGroup = THK_FileInterfaceParameters::find().CustGroupId;
if(!CustTable.DGS_CustNature)
CustTable.DGS_CustNature = THK_FileInterfaceParameters::find().DGS_CustNature;
}

1.網站 -- >  https://formatmysourcecode.blogspot.tw/
2.將所需要的程式碼填入其中