Add in Excel

To add extended fields to the Price Disc Adm Trans entity in the Dynamics 365 Finance and Operations Excel add-in, you need to extend the underlying data entity and then modify the Excel design to include the new fields. Here's a step-by-step guide: 1. Extend the Data Entity: Identify the Data Entity: The relevant data entity for price/discount journal lines is PriceDiscAdmTrans. This entity is used when you select "Edit in Excel" from the Price/discount journal lines form. Create a Data Entity Extension: Create a new extension for the PriceDiscAdmTrans entity. This allows you to add new fields without modifying the standard entity. Add the Fields: Add your desired extended fields to the extended entity. You can add them to the PriceDiscAdmTrans table or create a new related table and add the fields there, then link them to the entity. Add the Fields to the Entity: Within the entity extension, add the new fields to the entity's data sources, making sure they are included in the Selected Fields list. Compile and Synchronize: Compile and synchronize your extension to make the changes available in the system. Deploy and Build: Deploy the extension and build the application to ensure the changes are reflected. 2. Modify the Excel Add-in Design: Open the Excel Add-in: Open the Price/discount journal lines form in D365, select "Lines", and then click "Edit in Excel". Access Design Mode: In the Excel add-in, click "Design" to enter design mode. Select the Data Source: Find the PriceDiscAdmTrans data source in the list of available data sources. Add Fields: Click the pencil icon to edit the selected fields. In the "Available fields" list, find your newly added extended fields. Select the fields and click "Add" to move them to the "Selected fields" list. Change Field Order: If needed, change the order of the fields in the "Selected fields" list using the "Up" and "Down" buttons. Update and Refresh: Click "Update" to save the changes to the design. Then, click "Refresh" to load the new data and see your extended fields in the Excel sheet. 3. Additional Notes: Consider Data Types: Ensure the data types of your extended fields are compatible with Excel and D365. Labels and Translations: Remember to create appropriate labels for your new fields and consider translations if needed. Permissions: Make sure the users accessing the Excel add-in have the necessary permissions to view and edit the extended fields. Testing: Thoroughly test the changes in the Excel add-in to ensure the new fields are displayed correctly and their data is being handled as expected

Override Lookup of form control.

[ExtensionOf(formStr(VendEditInvoice))] final class XXX_VendEditInvoice_Extension { public void overridePaymSpecLookup(FormStringControl _formControl) { SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(VendPaymModeSpec), _formControl); Query query = new Query(); QueryBuildDataSource queryBuildDataSource; VendInvoiceInfoTable vendInvoiceInfoTable = _formControl.formRun().dataSource().cursor() as VendInvoiceInfoTable; queryBuildDataSource = query.addDataSource(tableNum(VendPaymModeSpec)); queryBuildDataSource.addRange(fieldNum(VendPaymModeSpec, PaymMode)).value(queryValue(vendInvoiceInfoTable.PaymMode)); sysTableLookup.addLookupfield(fieldNum(VendPaymModeSpec, Specification)); sysTableLookup.addLookupfield(fieldNum(VendPaymModeSpec, PaymMode)); sysTableLookup.addLookupfield(fieldNum(VendPaymModeSpec, Name)); sysTableLookup.parmQuery(query); sysTableLookup.performFormLookup(); } public void init() { next init(); PaymentInvoice_PaymSpec.registerOverrideMethod(methodStr(FormDataObject, lookup), formMethodStr(VendEditInvoice, overridePaymSpecLookup)); } }

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