SQL related

Restart UAT services. https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/deployment/maintenanceoperationsguide-newinfrastructure#restart-services https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-debugdiag DevTest environment to connect to the UAT database https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/database/dbmovement-scenario-debugdiag ================================================================================================= NET START "MR2012ProcessService" NET START "DynamicsAxBatch" NET START "Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe" NET START "W3SVC" PAUSE NET STOP "MR2012ProcessService" NET STOP "DynamicsAxBatch" NET STOP "Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe" NET STOP "W3SVC" PAUSE ==================================================================================================== Shrink log file https://www.linkedin.com/pulse/transaction-log-database-full-easy-steps-resolve-ashish-kale/ ALTER DATABASE AXDB SET RECOVERY SIMPLE GO DBCC SHRINKFILE (PreProdDBBACKUP02_log, 1000) GO ALTER DATABASE AXDB SET RECOVERY FULL //to get log file name and keep the file name above USE AxDB GO EXEC sp_helpfile GO ===================================== K:\AOSService\webroot\bin\Microsoft.Dynamics.AX.Deployment.Setup.exe -bindir "K:\AosService\PackagesLocalDirectory" metadatadir "K:\AosService\PackagesLocalDirectory" -sqluser "axdbadmin" -sqlserver "." -sqldatabase "AxDB" -setupmode "sync" -syncmode "fullall" -isazuresql "false" -sqlpwd "**axdbadmin p/w from LCS env page***" pause update userinfo set ENABLE=1 where ID = 'Nicolas.Ramirez' Power Shell command CD "C:\Users\Admin281589b1f6\Desktop\SPE\sqlpackage-win7-x64-en-16.1.6374.0" .\SqlPackage.exe /a:import /sf:K:\Newfolder\me-uatbackup.bacpac /tsn:localhost /tdn:AxDB_UAT0222 /p:CommandTimeout=1200 /TargetTrustServerCertificate:True How to Backup And Restore Database In SQL Server https://www.systoolsgroup.com/updates/backup-and-restore-database-in-sql-server/ ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE AxDB MODIFY NAME = ORIG_4thJan2022; ALTER DATABASE ORIG_4thJan2022 SET MULTI_USER ============================================================================== DB properties >> Options >> Recovery model- Simple Shrink file >> change file type to Log DB backup >> Backup Options >> set backup compression - "Compress backup" To check which tables are occupying more data , RC on DB >> Reports >> Standard Reports>> Disk usage by top tables. Change DB in single user to multiuser Use master GO select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid where d.name = 'AXDB' Get the spid, use side to kill. , execute below both statements at a time. GO Kill 59 GO ALTER DATABASE AXDB SET MULTI_USER WITH ROLLBACK IMMEDIATE GO ========================================================================================================================= ALTER DATABASE AXDB SET RECOVERY SIMPLE GO DBCC SHRINKFILE (PreProdDBBACKUP02_log, 1000) GO ALTER DATABASE AXDB SET RECOVERY FULL //to get log file name and keep the file name above USE AxDB GO EXEC sp_helpfile GO ======================================================================================================================== --Find the logical name of database’s log file-- USE AXDB GO SELECT Name AS LogicalName, filename AS PhysicalFile FROM sys.sysfiles GO --SHRINKFILE command-- USE AXDB GO -- Shrink the truncated log file to 8 MB-- DBCC SHRINKFILE (AXDB_UAT2402, 8); GO USE AXDB GO -- Shrink the truncated log file to 8 MB-- DBCC SHRINKFILE (AXDB_UAT2402_log, 8); GO USE AxDB; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AxDB SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (AXDB_UAT2402_log, 1); GO -- Reset the database recovery model. ALTER DATABASE AxDB SET RECOVERY FULL; GO ====================================== dbcc sqlperf(logspace)   select * from sys.database_files ALTER DATABASE AXDB SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (2, 1000); GO -- Reset the database recovery model. ALTER DATABASE AXDB SET RECOVERY FULL;  ===================================================== Compare Power BI reports C:\Temp>fc "Financial Analysis - Fiscal - Copy.pbix" "Financial Analysis - Fiscal (1).pbix" Comparing files Financial Analysis - Fiscal - Copy.pbix and FINANCIAL ANALYSIS - FISCAL (1).PBIX FC: no differences encountered

Restart services through power shell

NET START "MR2012ProcessService" NET START "DynamicsAxBatch" NET START "Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe" NET START "W3SVC" PAUS ALTER DATABASE AXDBORIG SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE AXDBORIG MODIFY NAME = AXDB; ALTER DATABASE AXDB SET MULTI_USER

Open In Excel

https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/financial/dimensions-overview https://statics.teams.cdn.office.net/evergreen-assets/safelinks/1/atp-safelinks.html https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/user-interface/add-templates-open-lines-excel-menu For lookup add relations : https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/financial/add-dimensions-excel-templates https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/office-integration/office-integration-edit-excel

Customized lookup for form datasource field

class PurchtableFormEventHandler { [FormDataSourceEventHandler(formDataSourceStr(PurchTable, InventDim), FormDataSourceEventType::Initialized)] public static void InventDim_OnInitialized(FormDataSource sender, FormDataSourceEventArgs e) { var overrides = PurchReqTableFormExtensionOverrides::construct(); sender.object(fieldNum(InventDim, InventSiteId)).registerOverrideMethod(methodStr(FormDataObject, lookup), methodStr(PurchReqTableFormExtensionOverrides, InventSiteId_OnLookup), overrides); } } ================================================================================================== public class PurchReqTableFormExtensionOverrides { protected void new() { } public static PurchReqTableFormExtensionOverrides construct() { return new PurchReqTableFormExtensionOverrides(); } public void InventSiteId_OnLookup(FormStringControl _callingControl) { String255 sUserId = curUserId(); HcmWorkerRecId workerRecId = HcmWorker::findByPerson(DirPersonuser::find(sUserId).PersonParty).RecId; SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(EmployeesSites), _callingControl); Query query= new Query(); QueryBuildDataSource qbds = query.addDataSource(tableNum(EmployeesSites)); qbds.addRange(fieldNum(EmployeesSites, EmployeeID)).value(queryValue(workerRecId)); qbds.addRange(fieldNum(EmployeesSites, Company)).value(curExt()); sysTableLookup.addLookupfield(fieldNum(EmployeesSites, SiteId)); sysTableLookup.addLookupfield(fieldNum(EmployeesSites, SiteName)); sysTableLookup.parmQuery(query); sysTableLookup.performFormLookup(); } }

Display inventory dimensions dynamically in D365

The table must have the field InventDimId and it must have a relation with InventDim. Image 2. Create a new Form. My form has two data sources, InventDimDisplay and InventDim (required) and a grid. Image 3. Set the InventDim data source properties to: Image REPORT THIS AD 4. On form Design, create a new Grid and move the ItemId to your grid and then create a new Group and then set the properties below: Image 5. On class declaration add the following piece of code: 1 2 3 4 5 public class FormRun extends ObjectRun { // Declare the class InventDimCtrl_Frm_EditDimensions InventDimCtrl_Frm_EditDimensions inventDimFormSetup; } 6. Now, create a new method in form. 1 2 3 4 public InventDimCtrl_Frm_EditDimensions inventDimSetupObject() { return inventDimFormSetup; } 7. Override the form’s method Init. 1 2 3 4 5 6 public void init() { super(); // This method will be used to show default fields at form startup element.updateDesign(InventDimFormDesignUpdate::Init); } 8. Create a new method, this method is responsible to show the Inventory Controls. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 void updateDesign(InventDimFormDesignUpdate mode) { InventDimParm inventDimParmVisible; switch (mode) { // Form Init case InventDimFormDesignUpdate::Init : if (!inventDimFormSetup) inventDimFormSetup = InventDimCtrl_Frm_EditDimensions::newFromForm(element); inventDimFormSetup.parmSkipOnHandLookUp( true); // Use the methods on InventDimParm // to set which dimensions to show when form is initialized inventdimparmvisible.inventsiteidflag = true; inventdimparmvisible.InventLocationIdFlag = true; inventDimFormSetup.parmDimParmVisibleGrid(inventDimParmVisible); // Datasource Active case InventDimFormDesignUpdate::Active : inventDimFormSetup.formActiveSetup(InventDimGroupSetup::newItemId(InventDimDisplay.ItemId)); //InventDimDisplay is the datasource name. inventDimFormSetup.formSetControls( true); break; // Datasource Field change case InventDimFormDesignUpdate::FieldChange : inventDimFormSetup.formActiveSetup(InventDimGroupSetup::newItemId(InventDimDisplay.ItemId)); //InventDimDisplay is the datasource name. InventDim.clearNotSelectedDim(inventDimFormSetup.parmDimParmEnabled()); // InventDim is referring to datasource name inventDimFormSetup.formSetControls( true); break; default : throw error(strFmt ("@SYS54195", funcName())); } } 9. We have to create a method on data source to update our table InventDimId and use the method Active to refresh the controls. Override Data source’s method Active. 1 2 3 4 5 6 7 public int active() { int ret; ret = super(); element.updateDesign(InventDimFormDesignUpdate::Active); return ret; } 10. Now, override the method Modified for ItemId field in your data source. 1 2 3 4 5 6 7 public void modified() { super(); element.updateDesign(InventDimFormDesignUpdate::FieldChange); InventDim.clearNotSelectedDim(element.inventDimSetupObject().parmDimParmEnabled()); } 11. We have to create a MenuItemButton to call the Display Dimension form where the user can select which dimensions he want to display. Set the following properties: MenuItemType: Display MenuItemName: InventDimParmFixed 12. By the end of this tutorial, your form should look like this. Image 13. The results: Image

Use data entity and import through X++ code

EcoResProductV2Entity ecoResProductEntity; NumberSequenceReference numberSequenceReference = EcoResProductParameters::numRefProductNumber(); NumberSequenceTable numberSequenceTable = numberSequenceReference.numberSequenceTable(); NumberSeq numberSeq = NumberSeq::newGetNumFromId(numberSequenceTable.RecId); ecoResProductEntity.ProductNumber = numberSeq.num(); ecoResProductEntity.ProductSearchName = _prodIntegrationTable.SearchName; switch (_prodIntegrationTable.ProductType) { case 'Item': ecoResProductEntity.ProductType = EcoResProductType::Item; break; case 'Service': ecoResProductEntity.ProductType = EcoResProductType::Service; break; } switch (_prodIntegrationTable.ProductSubType) { case 'Product': ecoResProductEntity.ProductSubType = EcoResProductSubtype::Product; break; case 'Product Master': ecoResProductEntity.ProductSubType = EcoResProductSubtype::ProductMaster; break; } ecoResProductEntity.RetailProductCategoryName = _prodIntegrationTable.Category; if (ecoResProductEntity.ProductSubType == EcoResProductSubtype::ProductMaster) { ecoResProductEntity.ProductDimensionGroupName = _prodIntegrationTable.ProductDimensionGroup; switch (_prodIntegrationTable.ConfigTechnology) { case "@SYS301180": ecoResProductEntity.VariantConfigurationTechnology = EcoResVariantConfigurationTechnologyType::PredefinedVariants; break; case "@SYS301187": ecoResProductEntity.VariantConfigurationTechnology = EcoResVariantConfigurationTechnologyType::DimensionBased; break; case "@SYS301188": ecoResProductEntity.VariantConfigurationTechnology = EcoResVariantConfigurationTechnologyType::ConstraintBased; break; } ecoResProductEntity.ProductVariantNameNomenclatureName = _prodIntegrationTable.ProdVariantNameNomenclature; ecoResProductEntity.ProductVariantNumberNomenclatureName = _prodIntegrationTable.ProdVariantNumNomenclature; } EcoResProductV2EntityToCrossTableDataAdaptor adaptor = EcoResProductV2EntityToCrossTableDataAdaptor::newFromEntity(ecoResProductEntity); EcoResProduct product = EcoResProductCrossTableManager::makeProductRecord(adaptor); EcoResProductCrossTableManager::insert(adaptor, product); BisProductCreationNPIImportProductCreation::updateEcoResProduct(_prodIntegrationTable, product.DisplayProductNumber); return product;

Check whether Role is assigned to User or not.

static boolean isUserInRole(Description  _roleName)

    {

        boolean                 res;

        SecurityUserRole        securityUserRole;

        SecurityRole            securityRole;

        ;

        select RecId from securityUserRole

            where  securityUserRole.User    == curUserId()

            join RecId from securityRole

                where  securityRole.RecId == securityUserRole.SecurityRole

                    && (   securityRole.AotName == _roleName

                    )    ;

        if (securityUserRole.RecId)

        {

            res = true;

        }

    

        return res;

    }

====================================================================

   ListEnumerator              userRolesEnumerator;

 userManagement              = new SysUserManagement();

   userRoles                   = userManagement.getRolesForUser(curUserId());

   userRolesEnumerator       = userRoles.getEnumerator();

Global::IsSystemadministrator()

GLobal::FinancialController()