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.