Extension(CoC) class for DP classes in dynamics 365 finance and operations x++

Recently I gone through creating an extension class known as CoC for a DP for inserting some values for header and lines in purchase order confirmation report.

This will give you an idea how to extend methods. Please change it according to your requirement.

[ExtensionOf(classStr(PurchPurchaseOrderDP))]
final class PurchClass_PurchPurchaseOrderDP_Extension
{
    protected PurchPurchaseOrderTmp initializeOrderLine(
        PurchPurchaseOrderHeader                    _purchaseOrderHeader,
        boolean                                     _highlightUpdated,
        PurchPurchaseOrderDPOrderLineQuerySelection _orderLineSelection)
    {
        PurchPurchaseOrderTmp purchPurchaseOrderTmp = next initializeOrderLine(_purchaseOrderHeader, _highlightUpdated, _orderLineSelection);

        purchPurchaseOrderTmp.CustomInventBatchId = _orderLineSelection.parmPurchLineAllVersionsInventDim().inventBatchId;
        purchPurchaseOrderTmp.Name = InventTable::find(purchPurchaseOrderTmp.ItemId).productName('en-us');

        return purchPurchaseOrderTmp;
    }

    protected PurchPurchaseOrderHeader initializePurchaseOrderHeader(VendPurchOrderJour _vendPurchOrderJour)
    {
        PurchPurchaseOrderHeader purchPurchaseOrderHeader = next initializePurchaseOrderHeader(_vendPurchOrderJour);

        PurchTable purchTable = _vendPurchOrderJour.purchTable();
       
        purchPurchaseOrderHeader.CustomPurchId  = purchTable.PurchId;
        purchPurchaseOrderHeader.CustomVendName       = purchTable.PurchName;

        return purchPurchaseOrderHeader;
    }

}

@Rahul

Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated Dynamics 365 x++

I was getting the below error while running "Transaction list by date" report in GL module. And this error is coming when I run the report for a specific date.

INSERT INTO tempdb."DBO".t9593_59B28DE6110C4D489ED985CE004FD381 (GENERALJOURNALACCOUNTENTRY,TXT,CURRENCYCODE,POSTINGTYPE,ISCREDIT,TRANSACTIONCURRENCYAMOUNTDEBIT,ACCOUNTINGCURRENCYAMOUNTDEBIT,REPORTINGCURRENCYAMOUNTDEBIT,ACCOUNTNUM,ACCOUNTNAME,DIMENSION,MAINACCOUNT,GENERALJOURNALENTRY,TRANSDATE,JOURNALNUMBER,VOUCHER,ACKNOWLEDGEMENTDATE,GENERALJOURNALENTRYDATAAREA,RECVERSION,PARTITION) SELECT T1.RECID AS f1,T1.TEXT AS f2,T1.TRANSACTIONCURRENCYCODE AS f3,T1.POSTINGTYPE AS f4,T1.ISCREDIT AS f5,T1.TRANSACTIONCURRENCYAMOUNT AS f6,T1.ACCOUNTINGCURRENCYAMOUNT AS f7,T1.REPORTINGCURRENCYAMOUNT AS f8,T2.MAINACCOUNT AS f9,T2.NAME AS f10,T2.FULLYQUALIFIEDACCOUNT AS f11,T2.MAINACCOUNTRECID AS f12,T3.RECID AS f13,T3.ACCOUNTINGDATE AS f14,T3.JOURNALNUMBER AS f15,T3.SUBLEDGERVOUCHER AS f16,T3.ACKNOWLEDGEMENTDATE AS f17,T5.DATAAREA AS f18,1 AS RECVERSION,5637144576 AS PARTITION FROM GENERALJOURNALACCOUNTENTRY T1 CROSS JOIN MAINACCOUNTLEDGERDIMENSIONVIEW T2 CROSS JOIN GENERALJOURNALENTRY T3 CROSS JOIN LEDGER T4 CROSS JOIN DIRPARTYTABLE T5 WHERE (T1.PARTITION=5637144576) AND (((T2.PARTITION=5637144576) AND (T2.PARTITION#2=5637144576)) AND ((T2.MAINACCOUNT=N'720115') AND (T1.LEDGERDIMENSION=T2.LEDGERDIMENSIONID))) AND ((T3.PARTITION=5637144576) AND ((((T3.ACCOUNTINGDATE>={ts '2019-03-30 00:00:00.000'}) AND (T3.ACCOUNTINGDATE<={ts '2019-03-30 00:00:00.000'})) AND (T3.LEDGER=5637145326)) AND (T1.GENERALJOURNALENTRY=T3.RECID))) AND ((T4.PARTITION=5637144576) AND (T3.LEDGER=T4.RECID)) AND (((T5.PARTITION=5637144576) AND (T4.PRIMARYFORLEGALENTITY=T5.RECID)) AND (T5.INSTANCERELATIONTYPE IN (10552) )) ORDER BY T2.MAINACCOUNT

Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated.

Cannot execute a data definition language command on Ledger transaction list (LedgerTransactionListStagingTmp). Date: . The SQL database has issued an error.


Reason: After doing some research I identified that the problem is with the size of "Txt" field in the tmp table is causing the issue. The length of this field is 50 in the report(tmp table) and the "description" in the transaction is more than 50 characters and it causing the SQL field length mismatch issue.

Solution: In my case I increased the length if EDT field for "txt" in temp table to 512.


That's it.

@Rahul

Cloud based Development Environment does not have admin user on LCS, how can I get the administrator password?

I found the below post regarding the admin account changes in latest versions.

As of platform update 12, customers will no longer have access to virtual machine (VM) admin accounts on development or build environments that are running in Microsoft subscriptions. This restriction only applies to new deployments of platform update 12 (or newer) environments, meaning that environments deployed before the update, but have been updated to platform 12, will still have admin access.

You can remote desktop (RDP) to these restricted environments using a non-admin user provided on the Lifecycle Services (LCS) environment page. This blog article contains more information on how to access these VMs: https://blogs.msdn.microsoft.com/lcs/2017/10/31/restricted-admin-access-with-platform-12-updates/

This post contains a list of commonly asked questions and answers that have been compiled by our product team, solution architects and support teams. 

Frequently asked questions

Q1: How can I install an AOT deployable package from the command line without being an Administrator on the VM?

A: Install an AOT deployable package using the -devinstall option. Keep in mind that this option requires a manual database synchronization which you can perform from the Visual Studio (VS) "Dynamics 365" menu.

See also: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/deployment/install-deployable-package

Q2: Is the local runtime (web application) accessible when Visual Studio (VS) is not running?

A: Yes, IIS Express is an executable running as the current user, but when you close VS the XPPC agent will start regular IIS (not IIS Express) before it shuts down. This ensures that even when you log off or the machine reboots, the AOS and web site are accessible remotely. We recognize many people use these dev boxes as test machines and they expect the AOS to always be running, which can’t be done with IIS Express.

Q2: What about the other services?

A: You can restart Windows® services such as SQL Server, SSRS, SSIS, SSAS, Batch, MR, and IIS (the Windows® service, not iisreset.exe) when you RDP to the VM using the account provided on the LCS environment page.

Q4: Will I be able to clean up the service volume drive?

A: Yes, the user account provided in LCS has full access to the service volume drive, so you can clean up the monitoring data, etc.

Q5: What are the alternatives to the VMs that do not allow administrator access?

A: An Azure VM on a private Azure subscription or a local VHD still allow administrator access.

Q6: When using version control, a "get latest" in the VS source control explorer fails because files are blocked by the AOS. How do we start / stop IIS?

A: This is not related to the changes described in this article. You have to stop IIS Express. See the next question for more information.

Q7: What are the instructions for using IIS Express? Can I attach it to a debugger?

A: IIS Express, when started, shows an icon in the notification area (near the clock) where it shows all the running sites. You can stop if from that menu. A start in IIS Express will be triggered by some actions in VS (Such as opening the table browser or rebuilding your project), but you can also explicitly do it by selecting "Restart IIS Express" in the VS “Dynamics 365” menu (Available as of update 13).

Since IIS Express is running as the current user, the current user has the required permissions to attach it to a debugger. 

Q8: How to apply a VS license key that is not tied to the VS log-in account?

A: Entering a product key directly in Visual Studio is not supported. You should use Visual Studio subscription licensing and sign-in to Visual Studio with the email address (user account) associated with the license. Linking a Visual Studio license to a user account can be done by assigning an MSDN license to the user account or by assigning a license to the user account usinghttps://www.visualstudio.com/subscriptions-administration.

Q9: How to install additional DEV tools (e.g. Fiddler, Pepper, …)

A: This is not supported on restricted VMs, it is only supported on local dev VMs or cloud VMs running in a private Microsoft Azure subscription.

Q10: How to run PowerShell & CMD commands “as admin?”

A: This is not supported on restricted VMs, it is only supported on local dev VMs or cloud VMs running in a private Microsoft Azure subscription.

Q11: Is the Trace Parser tool supported on restricted VMs?

A: It is currently not supported but we are working on adding support for it.

Q12: Is maintenance mode supported?

A: Using Microsoft.Dynamics.AX.Deployment.Setup.exe as described in the maintenance mode article (https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/sysadmin/maintenance-mode) is not supported. LCS will be adding self-service support for all environments in the future. Until it is available in LCS, you can use the following to put the system in maintenance mode where you can change the license configuration as described in the article.

1. RDP onto the Dev machine
2. Login to SQL Server on the dev machine using the credentials from LCS for the axdbadmin user - then switch to the AXDB database and run the following command: update SQLSYSTEMVARIABLES SET VALUE = 1 where PARM = 'CONFIGURATIONMODE'
3. Restart the “World Wide Web Publishing Service” service to reset IIS
4. After it is restarted, it will be in maintenance mode
5. Reverse the steps when done with maintenance mode activities - set the value to 0 and restart the “World Wide Web Publishing Service” service
   

Q13: Is installing a license deployable package supported?

A: Install an ISV license deployable package directly from LCS, there is no need to RDP to the development VM.  This should also work with the -devinstall option (See Q1) but a problem was found with this scenario and we are working on fixing it.

Q14: Can I upgrade my database to a new release without being an administrator on the VM?

This article describes how you can upgrade a Dynamics 365 for operations database to a new application release:https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/migration-upgrade/upgrade-data-to-latest-update/

This process requires you to be a local administrator on the VM when you execute the data upgrade package from the command line.

As of the February release of LCS, you can execute the data upgrade package directly from the LCS environment page of development environments, this does not require you to be an administrator on the VM.

Q15: What do I need to know if I am developing for Retail?

If you are developing for Dynamics 365 for Retail, refer to this topic for configuration steps and other important information: https://docs.microsoft.com/en-us/dynamics365/unified-operations/retail/dev-itpro/cloud-dev-box 

For more details please see below posts.

https://community.dynamics.com/365/financeandoperations/b/newdynamicsax/archive/2018/01/06/restricted-admin-access-on-development-vms-with-platform-update-12-what-you-need-to-know

https://cloudblogs.microsoft.com/dynamics365/it/2017/10/31/restricted-admin-access-with-platform-12-updates/?source=lcs

Item allocation key or configuration not specified error dynamics 365 finance and operations

I was getting the below error while trying to upload the forecast in Dynamics 365 F&O.I'm using a custom data entity for uploading it into the system.

Item allocation key or configuration not specified.

Reason: product variants are missing.

Solution:  There are two ways to fix the issue

1. upload the forecast with product variants color, size, style and configuration.

2. Default the variants on the product master. So, that when you upload the forecast it automatically takes the variants from the setup.

Object reference not set to an instance of an object Error while copying purchase order lines from another purchase order in Dynamics 365 F&O 8.0

I was getting the below error while trying to copy the purchase order lines from another order.And also specifically I'm getting this for inter-company purchase orders.


Object reference not set to an instance of an object Error.

Reason: 

 In my case there is an extension class for InterCompanySyncPurchLineType-> synchronizeInTradeCompany.
 and I was initializing next synchronizeInTradeCompany(); code after  AxSalesLine.

[ExtensionOf(classStr(InterCompanySyncPurchLineType))] final class SWInterCompanySyncPurchLineType_Extension { protected void synchronizeInTradeCompany() { AxSalesLine axSalesLineLocal = this.axSalesLine; next synchronizeInTradeCompany(); if (create || purchLine.fieldChanged(fieldNum(PurchLine, InventDimId))) { axSalesLineLocal.axInventDim().productDimensions(axInventDim); changecompany (purchLine.company()) { this.copyPurchaseBatchNumberToSalesTable(purchLine); } } } }

Solution: AxSalesLine doesn't get initialized until after the call to synchronizeInTradeCompany.

So, I swapped the code as shown below.

[ExtensionOf(classStr(InterCompanySyncPurchLineType))] final class SWInterCompanySyncPurchLineType_Extension { protected void synchronizeInTradeCompany() { next synchronizeInTradeCompany(); AxSalesLine axSalesLineLocal = this.axSalesLine; if (create || purchLine.fieldChanged(fieldNum(PurchLine, InventDimId))) { axSalesLineLocal.axInventDim().productDimensions(axInventDim); changecompany (purchLine.company()) { this.copyPurchaseBatchNumberToSalesTable(purchLine); } } } }

That's it. It worked fine.

@Rahul

Notexists Join in AX2012/ D365 F&O X++

Below is the example for how to use notexists join in x++.

In this scenario I'm deleting unused delivery modes from DLVMODE table in Dynamics 365 F&O.


/// <summary>
/// </summary>
class DeleteUnusedModeOfDeliveries
{       
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        DlvMode    dlvMode;
        SalesTable  SalesTable;
        int i;
        ;

        ttsbegin;
        while select forupdate dlvMode notexists join SalesTable where dlvMode.Code ==   SalesTable.DlvMode
        {
            dlvMode.delete();
            i++;
        }
        Info(strFmt("Total deleted records - %1", i));

        ttscommit;
    }

}


@Rahul

Run a job/class with main method outside of visual studio D365FO

In AX 2012 we run a job from AOT in non-prod and production environments. In D365 F&O we no longer have access to server or visual studio to run a job in non prod/prod. But, we have another way to execute a job.

In order to do this we can run the following url.

[D365FO main URL]/?cmp=[company]&mi=SysClassRunner&cls=[job/runnable class name]

Example :

https://usnconeboxax1aos.cloud.onebox.dynamics.com/?cmp=USMF&mi=SysClassRunner&cls=ShowInvoicedOrderCount

Change the URL and other parameters according to your requirement and paste it in chrome/IE and hit enter.

That's it.

@Rahul

MSBUILD : error MSB1009: Project file does not exist. Switch: C:\DynamicsSDK\VSOAgent\_work\4\s\AXModulesBuild.proj ##[error]Process 'msbuild.exe' exited with code '1' in Dynamics 365 finance and operations

I got the below error while trying to builld a new deployable package on visual studio azure DevOps.

MSBUILD : error MSB1009: Project file does not exist. Switch: C:\DynamicsSDK\VSOAgent\_work\4\s\AXModulesBuild.proj ##[error]Process 'msbuild.exe' exited with code '1'.


Reason:  AXModulesBuild is missing from the branch, but it is defined in "Build the solution". And when I kick off a new build it's looking for AXModulesBuild.

Solution:In my case I copied it from another branch.

Click on 3dots-> Download

And go to the branch where you are trying to kick- off the new build and upload the file.

After that check-in the change.

That's it. Now kick-off the new build.

Error message: *** Error importing database:Could not import package. Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure AXPerf_CreateIndex

I was getting the below error while trying to import the database into my DEV box which was originally exported from sandbox environment.

Error message: 

*** Error importing database:Could not import package.

Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure AXPerf_CreateIndex, Line 1 'TRIM' is not a recognized built-in function name.

Error SQL72045: Script execution error.  The executed script:

CREATE PROCEDURE [dbo].[AXPerf_CreateIndex]

@idxStmt NVARCHAR (MAX), @bypass BIT=0, @message NVARCHAR (MAX) OUTPUT, @returnStatus INT OUTPUT, @createdIdxStmt NVARCHAR (MAX) OUTPUT

AS

BEGIN

    SET NOCOUNT ON;

    PRINT 'AXPerf_CreateIndex v1.0';

    DECLARE @DoOpenCursorExistsOnObject AS INT = 0;

    DECLARE @kstart AS INT, @kend AS INT, @istart AS INT, @iend AS INT, @isColStrIdx AS BIT, @IncColCount AS INT;

    DECLARE @iName AS VARCHAR (1000), @fullyQualifiedTName AS VARCHAR (1000), @scName AS VARCHAR (20), @tName AS VARCHAR (128), @KeyCol AS VARCHAR (MAX), @IncCol AS VARCHAR (MAX);

    SET @idxStmt = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@idxStmt, CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), ' ', '<>'), '><', ''), '<>', ' ');

    SET @kstart = CharINDEX('(', @idxStmt);

    SET @kend = CharINDEX(')', @idxStmt);

    SET @istart = CASE WHEN CharIndex('include', @idxStmt, @kend + 1) != 0 THEN CHARINDEX('(', @idxStmt, @kend + 1) ELSE 0 END;

    SET @iend = CHARINDEX(')', @idxStmt, @kend

Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure AXPerf_CreateIndex, Line 1 'TRIM' is not a recognized built-in function name.

Error SQL72045: Script execution error.  The executed script:

CREATE PROCEDURE [dbo].[AXPerf_CreateIndex]

@idxStmt NVARCHAR (MAX), @bypass BIT=0, @message NVARCHAR (MAX) OUTPUT, @returnStatus INT OUTPUT, @createdIdxStmt NVARCHAR (MAX) OUTPUT

AS

BEGIN

    SET NOCOUNT ON;

    PRINT 'AXPerf_CreateIndex v1.0';

    DECLARE @DoOpenCursorExistsOnObject AS INT = 0;

    DECLARE @kstart AS INT, @kend AS INT, @istart AS INT, @iend AS INT, @isColStrIdx AS BIT, @IncColCount AS INT;

    DECLARE @iName AS VARCHAR (1000), @fullyQualifiedTName AS VARCHAR (1000), @scName AS VARCHAR (20), @tName AS VARCHAR (128), @KeyCol AS VARCHAR (MAX), @IncCol AS VARCHAR (MAX);

    SET @idxStmt = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@idxStmt, CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), ' ', '<>'), '><', ''), '<>', ' ');

    SET @kstart = CharINDEX('(', @idxStmt);

    SET @kend = CharINDEX(')', @idxStmt);

    SET @istart = CASE WHEN CharIndex('include', @idxStmt, @kend + 1) != 0 THEN CHARINDEX('(', @idxStmt, @kend + 1) ELSE 0 END;

    SET @iend = CHARINDEX(')', @idxStmt, @kend

Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure AXPerf_CreateIndex, Line 1 'TRIM' is not a recognized built-in function name.

Error SQL72045: Script execution error.  The executed script:

CREATE PROCEDURE [dbo].[AXPerf_CreateIndex]

@idxStmt NVARCHAR (MAX), @bypass BIT=0, @message NVARCHAR (MAX) OUTPUT, @returnStatus INT OUTPUT, @createdIdxStmt NVARCHAR (MAX) OUTPUT

AS

BEGIN

    SET NOCOUNT ON;

    PRINT 'AXPerf_CreateIndex v1.0';

    DECLARE @DoOpenCursorExistsOnObject AS INT = 0;

    DECLARE @kstart AS INT, @kend AS INT, @istart AS INT, @iend AS INT, @isColStrIdx AS BIT, @IncColCount AS INT;

    DECLARE @iName AS VARCHAR (1000), @fullyQualifiedTName AS VARCHAR (1000), @scName AS VARCHAR (20), @tName AS VARCHAR (128), @KeyCol AS VARCHAR (MAX), @IncCol AS VARCHAR (MAX);

    SET @idxStmt = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@idxStmt, CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), ' ', '<>'), '><', ''), '<>', ' ');

    SET @kstart = CharINDEX('(', @idxStmt);

    SET @kend = CharINDEX(')', @idxStmt);

    SET @istart = CASE WHEN CharIndex('include', @idxStmt, @kend + 1) != 0 THEN CHARINDEX('(', @idxStmt, @kend + 1) ELSE 0 END;

    SET @iend = CHARINDEX(')', @idxStmt, @kend

Error SQL72014: .Net SqlClient Data Provider: Msg 319, Level 15, State 1, Procedure AXPerf_CreateIndex, Line 1 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a chan

Reason: The problem is with the stored procedure AXPerf_CreateIndex   

Solution: Connect to your sandbox database and drop this stored procedure before exporting it again. Once you drop it reexport the database again and import into the DEV box.

Below are the queries I used . 

--Query 1

select * from dbo.sysobjects where id = object_id(N'dbo.AXPerf_CreateIndex')

-- Query 2

DROP PROCEDURE  AXPerf_CreateIndex; GO

@Rahul Talaseela

The record already exists error in data entities in dynamics 365 finance & operations

I was keep getting the below error while exporting sales invoice lines. I created a composite data entity for exporting sales invoices with lines into XML format.

Cannot create a record in Test Sales invoice line (SalesInvoiceLineStagingV2). Item: XXXXXX.
The record already exists.

Reason:
After doing some research I realized that the cluster & staging index is missing for sales invoice header and lines.

Solution:

I added both cluster & staging index for sales invoice header and lines staging tables and added them in the properties.

Recompile the models & refresh entities.

That's it.

Hire New Worker Button Disabled in Dynamics 365 Finance & Operations

One of my functional consultants complained that she was not able to create new Employees/workers in Human resources module.

After doing some research I realized that the personal numbers are already exist in the HcmWorker table what the number sequence is using.

Ex: Personal number 131 is alreasy exist in HcmWorker, But the number sequence is assigning the same number for the new worker.

Solution:

Go to Human resources -> setup-> Human resources shared parameters -> Number sequence and find the number sequence code for Personnel number.

Open HcmWorker table and find out the latest personal number and update the number sequence with the next number.

Please note that disable continuous button before updating the next number. Enable it after the updating it.


That's it.

Posting An attempt was made to set a report parameter 'CustInvoiceJourAdjustmentRecId_TH' that is not defined in this report.

I was getting the below errors while tying to run sales invoice report in Dynamics 365 F&O .
Version: 8.0
PU: 23

Posting An attempt was made to set a report parameter 'CustInvoiceJourAdjustmentRecId_TH' that is not defined in this report.

Posting An attempt was made to set a report parameter 'CreatedTransactionId' that is not defined in this report.


Reason: 

In my case we upgraded D365 from 7.0 version to 8.0 version and Microsoft added some new parameters in the sales invoice report and those are not refreshed in the sales invoice report.


Solution: 

I refreshed the data sets in the sales invoice report. Then, automatically below parameter are added to the report. 

CustInvoiceJourAdjustmentRecId_TH


I was not able to add the "CreatedTransactionId" automatically in the parameters. So, I duplicated one of existing parameters and I renamed it to CreatedTransactionId.

Later, I made them nullable and allow blanks true.

And redeployed the report.


That's it.