SQL script to retrieve the list of active users with license details D365 FnO

Hi guys,

I am writing this blog to share the information regarding active users license details in D365 FnO. 

Below is the SQL script that will fetch list of active users, roles and their license details.

Select userId.ID as "User id",
       userId.NAME as "User name", 
       securityRole.NAME AS "Role name",
       securityRole.description AS "Role description",      
       ISNULL(internalOrganization.NAME,'All') as "Legal entity", 
       (CASE securityRole.USERLICENSETYPE
        WHEN 4 Then 'Operations'
        WHEN 6 Then 'Team Members'
        WHEN 7 Then 'Activity'
        Else 'Unknown' end) as "License type"
       from userInfo userid
       inner JOIN SECURITYUSERROLE UserRole
       on UserRole.USER_ = userid.ID
       inner JOIN AXDB.dbo.SECURITYROLE securityRole
       on securityRole.RecId = UserRole.SecurityRole
       LEFT JOIN OMUserRoleOrganization roleOrganization
       on roleOrganization.SecurityRole = UserRole.SecurityRole
       AND roleOrganization.USER_ = userid.Id
       LEFT JOIN DIRPARTYTABLE internalOrganization
       on internalOrganization.RECID = roleOrganization.OMINTERNALORGANIZATION
       left join SYSUSERLICENSELIST ssul
       on  ssul.username = userid.ID
       left join SYSUSERLICENSECOUNT sslc
       on  sslc.recid = ssul.SYSUSERLICENSECOUNT
where userid.ENABLE = '1'

order by userid.ID

How to extend Form datasource method X++

Hi guys,

This blog will be useful in those scenarios where in standard MS Form datasource methods (init, write, validateWrite, active, selectionChanged etc) will be required to further extend the standard functionality.

Requirement:

I came across a requirement on which I had to disable the standard MS field on the basis of a true value on custom boolean field. If custom flag field contains true value then target field "Allow reservation" on the return sales order line should be disabled as a result of focus set on that selected return sales order line.

Solution:

Right click on the Form datasource active method and then click create an extension to create an extension class.

 [ExtensionOf(formDataSourceStr(ReturnTable, SalesLine))]  
 final class DemoReturnTableSalesLineFrm_Extension  
 {  
   int active()  
   {  
     int ret = next active();  
     SalesLine salesLine = this.cursor();  
     SalesTable salesTable = SalesTable::find(salesLine.SalesId);  
     FormRun formRun = this.formRun();  
     if(salesTable.DemoPriceAdjustment)  
     {  
       formRun.control(formRun.controlId(formControlStr(ReturnTable, return_ReturnAllowReservation))).enabled(false);  
     }  
     return ret;  
   }  
 }  

How to extend lookup method and apply query range on the standard MS form X++

Hi folks

In this blog, I am going to explain how lookup control on the standard form can be overridden. 

Requirement:

I got the requirement from the customer where in user wants to select Operating unit of type "Department" from the drop down list on the custom fast tab which contains the custom fields on the grid.

Since, Operating units are shared across mutiple legal entities therefore lookup control will display Departments across all companies.

Solution: 

Create a class that will handle the logic of overriding the lookup control and applying the range of Operating unit of type "Department" on the lookup. This class is being called on the Form event handler on the lookup control

Form control lookup override extension class:

 public class DemoLogisticsPostalAddressFormExtensionOverrides  
 {  
   protected void new()  
   {  
   }  
   public static DemoLogisticsPostalAddressFormExtensionOverrides construct()  
   {  
     return new DemoLogisticsPostalAddressFormExtensionOverrides();  
   }  
   public Common lookupReference(FormReferenceControl _formReferenceControl)  
   {  
     return OMOperatingUnitHelper::operatingUnitLookup(_formReferenceControl,     OMOperatingUnitType::OMDepartment);  
   }  
   public Common resolveReference(FormReferenceControl _formReferenceControl)  
   {  
     // Do not call super since we're providing our own disambiguation logic.  
     return OMOperatingUnitHelper::operatingUnitResolve(_formReferenceControl, OMOperatingUnitType::OMDepartment);  
   }  
 }  

Form datasource event handler class:

Create a form datasource event handler class for the control on which Departments will be displayed.

Create datasource post event handler initialize method

 public class DemoLogisticsPostalAddressFrmEventHandler  
 {  
   [FormDataSourceEventHandler(formDataSourceStr(LogisticsPostalAddress, DemoDeliverySettings), FormDataSourceEventType::Initialized)]  
   public static void DemoLogisticsPostalAddress_OnInitialized(FormDataSource _sender, FormDataSourceEventArgs _e)  
   {  
     var overrides = DemoLogisticsPostalAddressFormExtensionOverrides::construct();   
     
     _sender.object(fieldNum(DemoShiftDeliverySettings, Department)).registerOverrideMethod(methodStr(FormDataObject, lookupReference),  
       methodStr(DemoLogisticsPostalAddressFormExtensionOverrides, lookupReference), overrides);
       
     _sender.object(fieldNum(DemoShiftDeliverySettings, Department)).registerOverrideMethod(methodStr(FormDataObject, resolveReference),  
       methodStr(DemoLogisticsPostalAddressFormExtensionOverrides, resolveReference), overrides);  
   }  
 }