Restore .bacpac file to DEV VM D365 FO

Restore .bacpac file to Development VM

Steps:

1.       Take Backup of AxDB database from Development VM

2.       Open Command Prompt (Run as Administrator)

Go To Path (Version Folder might be differ, SQL)

cd "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin"



C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin>

3.       Command to Execute

SqlPackage.exe /a:import /sf:C:\TEMP\VWD365FUT3backup.bacpac /tsn:localhost /tdn:AxDB_ENV_DDMMYYYY /p:CommandTimeout=1200



Note: Process will take some more time

4.       Open SQL, Run this below script

CREATE USER axdeployuser FROM LOGIN axdeployuser EXEC sp_addrolemember 'db_owner', 'axdeployuser' CREATE USER axdbadmin FROM LOGIN axdbadmin EXEC sp_addrolemember 'db_owner', 'axdbadmin' CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser' EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser' CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser EXEC sp_addrolemember 'DataSyncUsersRole', 'axretaildatasyncuser' CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser EXEC sp_addrolemember 'UsersRole', 'axretailruntimeuser' EXEC sp_addrolemember 'ReportUsersRole', 'axretailruntimeuser' CREATE USER axdeployextuser FROM LOGIN axdeployextuser EXEC sp_addrolemember 'DeployExtensibilityRole', 'axdeployextuser' CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE] EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE' UPDATE T1 SET T1.storageproviderid = 0 , T1.accessinformation = '' , T1.modifiedby = 'Admin' , T1.modifieddatetime = getdate() FROM docuvalue T1 WHERE T1.storageproviderid = 1 --Azure storage DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2 GO -- Begin Refresh Retail FullText Catalogs DECLARE @RFTXNAME NVARCHAR(MAX); DECLARE @RFTXSQL NVARCHAR(MAX); DECLARE retail_ftx CURSOR FOR SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = 'COMMERCEFULLTEXTCATALOG'); OPEN retail_ftx; FETCH NEXT FROM retail_ftx INTO @RFTXNAME; BEGIN TRY WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Refreshing Full Text Index ' + @RFTXNAME; EXEC SP_FULLTEXT_TABLE @RFTXNAME, 'activate'; SET @RFTXSQL = 'ALTER FULLTEXT INDEX ON ' + @RFTXNAME + ' START FULL POPULATION'; EXEC SP_EXECUTESQL @RFTXSQL; FETCH NEXT FROM retail_ftx INTO @RFTXNAME; END END TRY BEGIN CATCH PRINT error_message() END CATCH CLOSE retail_ftx; DEALLOCATE retail_ftx; -- End Refresh Retail FullText Catalogs

ALTER DATABASE [<your AX database name>] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 6 DAYS, AUTO_CLEANUP = ON) GO

5.       Stop following services

a.       Management Reporter 2012 process Service

b.      Microsoft Dynamics 365 Unified Operations: Batch Management Service

c.       World Wide Web Publishing Service

6.       Rename DB

a.       AxDB to AxDB_Orig

b.      AxDB_DDMMYYYY (New Restored DB) to AxDB

Use Script

USE master; 
GO 
ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE AxDB MODIFY NAME = AxDB_orig ;
GO 
ALTER DATABASE AxDB_orig SET MULTI_USER
GO


USE master; 
GO 
ALTER DATABASE <Restore DB name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <Restore DB name> MODIFY NAME = AxDB ;
GO 
ALTER DATABASE AxDB SET MULTI_USER
GO
THis is one

7.       Start following services

a.       Management Reporter 2012 process Service

b.      Microsoft Dynamics 365 Unified Operations: Batch Management Service

c.       World Wide Web Publishing Service

d.      Start IIS website from IIS manager


Build Model and Sync DB

Open DEV URL and check once



Ref: https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-exportuat

SysDa API in D365 FO

SysDa API

"Da" is short for Data access. It is a set of new APIs exposing the object graph that the compiler otherwise produces from select statements. 

Problem:
On the extensibility journey in D365 FO, X++ select statements are not extensible. If developer needs to add a new range, a join, a field in the field list – it is not possible.

Pros:
1. It has the same performance characteristics as select statements
2. It is extensible for futher enhancement

3. It is available in from PU22

List:

• Select: SysDaQueryObject, SysDaSearchObject, and SysDaSearchStatement
• Update: SysDaUpdateObject and SysDaUpdateStatement
• Insert: SysDaInsertObject and SysDaInsertStatement
• Delete: SysDaQueryObject, SysDaDeleteObject, and SysDaDeleteStatement

CRUD Operation:

Select: 

        //Selection - On SYSDA API  

        VendTable   vendTab; 

        var qe = new SysDaQueryObject(vendTab);

        qe.projection()

                .add(fieldStr(VendTable, AccountNum))

                .add(fieldStr(VendTable, VendGroup));

        qe.WhereClause(new SysDaEqualsExpression(

                new SysDaFieldExpression(vendTab, fieldStr(VendTable, VendGroup)),

                new SysDaValueExpression(10)));

        //below statement to get less than or equal to field values

        //qe.WhereClause(new SysDaLessThanOrEqualsExpression(

        //        new SysDaFieldExpression(vendTab, fieldStr(VendTable, VendGroup)),

        //        new SysDaValueExpression(10)));

        //Default order by field is ASC

        qe.OrderByClause().addDescending(fieldStr(VendTable, AccountNum)); //for desc

        //Type 1

        var so = new SysDaSearchObject(qe);

        var ss = new SysDaSearchStatement();

        while (ss.nextRecord(so))

        {

            info(vendTab.AccountNum);

        }

        //Type 2 

        var fo = new SysDaFindObject(qe);

        new SysDaFindStatement().execute(fo);

        //Type 3 Using join

        PurchLine   purchLine;

        InventDim   Invdim;

        var qepl = new SysDaQueryObject(purchLine);

        var qedim = new SysDaQueryObject(Invdim);

        qepl.joinClause(SysDaJoinKind::InnerJoin,qedim);

        //Relation manual

        qedim.WhereClause(new SysDaEqualsExpression(

                new SysDaFieldExpression(purchLine, fieldStr(PurchLine, InventDimId)),

                new SysDaFieldExpression(Invdim, fieldStr(InventDim, InventDimId))));

        qepl.WhereClause(new SysDaEqualsExpression(

                new SysDaFieldExpression(purchLine, fieldStr(PurchLine, VendAccount)),

                new SysDaValueExpression("000013")));

        var so1 = new SysDaSearchObject(qepl);

        var ss1 = new SysDaSearchStatement();

        while (ss1.nextRecord(so1))

        {

            info(purchLine.ItemId + purchLine.InventDimId);

        }

Update:

      // Update - ON SysDa API

        PurchTable  purchTable;

        var updateObj = new SysDaUpdateObject(purchTable);

        updateObj.settingClause()

         .add(fieldStr(PurchTable, PurchName), new SysDaValueExpression( "SAN")); 

        updateObj.whereClause(new SysDaEqualsExpression(

        new SysDaFieldExpression(purchTable, fieldStr(PurchTable, OrderAccount)),

            new SysDaValueExpression("000013")));

        //Updating the rows.

        ttsbegin;

        new SysDaUpdateStatement().execute(updateObj);

        ttscommit;

Insert:

     // Insert - ON SysDa API

        sanLanguageTable santab;

        var Io = new SysDaInsertObject(santab);

        Io.fields()

            .add(fieldStr(sanLanguageTable, Name))

            .add(fieldStr(sanLanguageTable, Description));

        VendGroup source;

        var qe = new SysDaQueryObject(source); 

        var s1 = qe.projection()

            .Add(fieldStr(VendGroup, Name))

            .Add(fieldStr(VendGroup, VendGroup));

        Io.query(qe);

        var istate = new SysDaInsertStatement();

        ttsbegin;

        istate.executeQuery(Io);

        ttscommit;

Delete:

     // Delete - ON SysDa API

        sanLanguageTable santab;

        var qe = new SysDaQueryObject(santab); 

        var s = qe.projection()

                .add(fieldStr(sanLanguageTable, Name));

        var ds = new SysDaDeleteStatement();

        var delobj = new SysDaDeleteObject(qe);

        ttsbegin;

        ds.executeQuery(delobj);

        ttscommit;

        //To get no of rrows after deletion

        info("Number of rows after deletion: " + any2Str(t.RowCount()));

Important:

You can use the toString() method on SysDaQueryObject, SysDaUpdateObject, SysDaInsertObject, and SysDaQueryObject objects to view the statement that you're building.

Related Objects: (SysDa API)

Base Enum:

SysDaAggregateFieldType

SysDaFirstOnlyHint

SysDaJoinKind

Class:

SysDaAggregateProjectionField

SysDaAndExpression

SysDaAvgOfField

SysDaBinaryExpression

SysDaCountOfField

SysDaCrossCompany

SysDaCrossCompanyAll

SysDaCrossCompanyContainer

SysDaDataAccessStatement

SysDaDeleteObject

SysDaDeleteStatement

SysDaDivideExpression

SysDaEqualsExpression

SysDaFieldExpression

SysDaFindStatement

SysDaGreaterThanExpression

SysDaGreaterThanOrEqualsExpression

SysDaGroupBys

SysDaInsertObject

SysDaInsertStatement

SysDaIntDivExpression

SysDaLessThanExpression

SysDaLessThanOrEqualsExpression

SysDaLikeExpression

SysDaMaxOfField

SysDaMinOfField

SysDaMinusExpression

SysDaModExpression

SysDaMultiplyExpression

SysDaNotEqualsExpression

SysDaOrderBys

SysDaOrExpression

SysDaPlusExpression

SysDaProjectionField

SysDaQueryExpression

SysDaQueryObject

SysDaSearchObject

SysDaSearchStatement

SysDaSelection

SysDaSettingsList

SysDaSumOfField

SysDaUpdateObject

SysDaUpdateStatement

SysDaValueExpression

SysDaValueField

Customizing Filter Capability option on GRID QUICK FILTER in D365 FO

Customizing Filter Capability option  on GRID QUICK FILTER in Dynamics 365 Finance and Operations


Class: GridQuickFilterProvider)
Method: applyFilter

Created new class

/// <summary>
/// The <c>GridQuickFilterProvider</c> provides an interface used to manage the interactions between a <c>QuickFilterControl</c> and the <c>FormGridControl</c> that it filters.
/// </summary>
[ExtensionOf(classStr(GridQuickFilterProvider))]
final class SANGridQuickFilterProvider_Extension
{
    /// <summary>
    /// Apply the specified filter value on the field.- COC
    /// </summary>
    /// <param name = "fieldName">The name of the field to filter.</param>
    /// <param name = "filterValue">The value of the filter.</param>
    public void applyFilter(str fieldName, str filterValue)
    {
        str filterValueCoC;
        filterValueCoC = SysQuery::ValueLike(filterValue);
        info(strFmt("%1-%2",filterValue,filterValueCoC));
        next applyFilter(fieldName, filterValueCoC);
    }

}

Maps Navigation in D365 FO/ AX 2012

      //MAPS Navigation in D365 FO , AX 2012     

       LogisticsPostalAddress postalAddress;
        const str comma = ',';
        const str newLine = '\n';

        str     address,;
        str mapUrl = 'https://www.bing.com/maps/default.aspx?where1=\%1'; 

        select firstonly postalAddress; //For testing Selected first record

        address = postalAddress.Street + comma +
                      postalAddress.City + comma +
                      postalAddress.State + comma +
                      postalAddress.ZipCode + comma +
                      postalAddress.CountryRegionId;
        address = strReplace(address, newline, comma);
        address = System.Web.HttpUtility::UrlEncode(address);

        Browser br = new Browser();
        br.navigate((strFmt(mapUrl, address)), true); //for Bing

        str     addressGoogle;
        str  mapUrlGoogle = 'http://maps.google.com?q=\%1';
        const str comma = ',';
        const str newLine = '\n';
         mapUrlGoogle = 'http://maps.google.com?q=\%1';
        addressGoogle = postalAddress.Street + comma +
                      postalAddress.City + comma +
                      postalAddress.State + comma +
                      postalAddress.ZipCode + comma +
                      postalAddress.CountryRegionId;
        addressGoogle = strReplace(addressGoogle, newline, comma);
        addressGoogle =  System.Web.HttpUtility::UrlEncode(addressGoogle);
        Browser brGoogle = new Browser();
        brGoogle.navigate((strFmt( mapUrlGoogle, addressGoogle)), true); for Google maps

Get all records from AOT Query by enabling validTimeStateDateTimeRange in D365 FO/AX 2012

utcdatetime minDateTime = DateTimeUtil::minValue() , maxDateTime = DateTimeUtil::maxValue();


qry = new Query(QueryStr(TestQuery));
qry.validTimeStateDateTimeRange(minDateTime, maxDateTime); //In order display all records from ValidTimeState stamp table

Example: (Table Names)
HcmEmployment
HcmWorkerPositionAssignment
HcmWorkerEnrollBenefit

Get workflow delegate User for current user in D365 FO

WorkflowWorkItemDelegationParameters    delegationParameters;
        utcdatetime delegateDate = DateTimeUtil::utcNow();
        userId delegateUser;

        WorkflowTable                           workflowTable;
        SysWorkflowTable                        sysWorkflowTable;

       sysWorkflowTable = SysWorkflowTable::find(_workflowElementEventArgs.parmWorkflowContext().parmWorkflowCorrelationId());

        select firstonly workflowTable
            where workflowTable.Type == WorkflowConfigurationType::Definition
            && workflowTable.SequenceNumber == sysWorkflowTable.ConfigurationSequenceNumber;

        while select delegationParameters
            where delegationParameters.User == _currentUser
            && delegationParameters.Enabled == NoYes::Yes
                && delegationParameters.FromDate < delegateDate
                    && delegationParameters.ToDate > delegateDate
        {
            switch (delegationParameters.Type)
            {
                case WorkflowWorkItemDelegationType::All:
                    info("All "+delegationParameters.DelegateTo);
                    break;

                case WorkflowWorkItemDelegationType::Category:
                    if (delegationParameters.CategoryName == workflowTable.CategoryName)
                    {
                        info("module "+delegationParameters.DelegateTo);
                    }
                    break;

                case WorkflowWorkItemDelegationType::Configuration:
                    if (delegationParameters.ConfigurationSequenceNumber == workflowTable.SequenceNumber)
                    {
                        info("worklfow "+delegationParameters.DelegateTo);
                    }
                    break;

                default:
                    throw error(strFmt("@SYS122124", enum2str(delegationParameters.Type)));
            }
        }

Get workflow participant User by document in D365 FO

PurchReqTable                               purchReqTable = PurchReqTable::findPurchReqId("002154");
        PurchReqLine                                purchReqLine;
        WorkflowParticipantExpenToken               workflowParticipantExpenToken;
        WorkflowParticipantExpenTokenLine           workflowParticipantExpenTokenLine;
        WorkflowUserList                            userList = WorkflowUserList::construct();       

        while select workflowParticipantExpenToken         
            where workflowParticipantExpenToken.ExpenditureDocumentType == WorkflowParticipantExpenDocumentType::PurchReq
                && workflowParticipantExpenToken.Name == "Project Manager"
        {
            info(strFmt("%1", workflowParticipantExpenToken.Name));

            while select ProjId, SourceDocumentLine, BuyingLegalEntity, RecId
                from purchReqLine
                where purchReqLine.PurchReqTable == purchReqTable.RecId
            {
                changecompany(CompanyInfo::findRecId(purchReqLine.BuyingLegalEntity).DataArea)
                {
                    workflowParticipantExpenTokenLine = WorkflowParticipantExpenTokenLine::findTokenLine(workflowParticipantExpenToken.RecId, purchReqLine.BuyingLegalEntity);

                    PurchReqWFExpendiParticipantProvider provider = PurchReqWFExpendiParticipantProvider::construct();
                    provider.documentType();

                    RefRecId    dimensionAttributeSetRecId;
                    MarkupTrans markupTrans;
                    if (purchReqLine.ProjId)
                    {
                        provider.resolveProjectDistributions(purchReqLine.SourceDocumentLine, workflowParticipantExpenTokenLine, userList);
                        dimensionAttributeSetRecId  = workflowParticipantExpenTokenLine.ProjectDimensionAttributeSet;
                    }
                    else
                    {
                        dimensionAttributeSetRecId  = workflowParticipantExpenTokenLine.OrganizationDimensionAttributeSet;
                    }

                    if (dimensionAttributeSetRecId)
                    {
                        provider.resolveDimensions(userList, purchReqLine.SourceDocumentLine, dimensionAttributeSetRecId);

                        while select SourceDocumentLine from  markupTrans where markupTrans.TransTableId == purchReqLine.TableId &&
                                                                            markupTrans.TransRecId   == purchReqLine.RecId
                        {
                            provider.resolveDimensions(userList, markupTrans.SourceDocumentLine, dimensionAttributeSetRecId);
                        }
                    }
                    //provider.Get
                }
            }
        }

        int i = 1;
        for(i=1;i<=userList.getCount();i++)
        {
            info(userList.get(i));
        }