呆子獅的夢想世界

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

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的行。

1. Open lcs.dynamics.com in your web browser.
2. Log in with your credentials.
3. Select the LCS project you want to add a Azure subscription to from the list.
4. Click the Hamburger menu button.
5. Select Cloud-hosted environments from the list.

6. On the Cloud-hosted environments page, click Add.
7. On the Select environment topology page, select Azure.
8. 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.)
9. 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.)
10. On the Deploy environment page, type  a unique name in the Environment name field. 
11. 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.)
12. Click Advanced settings.

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: