HTML Stripping function

 Below is the function used to strip the HTML tags from a field in a SQL table. 

ALTER  FUNCTION [dbo].[udf_StripHTML]
	(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT

    SET @Start = CHARINDEX('[if',@HTMLText)
    SET @End = CHARINDEX('if]',@HTMLText, @Start)
    SET @Length = (@End - @Start) + 1
    WHILE ( @Start > 0 	AND @End > 0 AND @Length > 0 )
	BEGIN
		SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
		SET @Start = CHARINDEX('[if',@HTMLText)
		SET @End = CHARINDEX('if]',@HTMLText,@Start)
		SET @Length = (@End - @Start) + 1
	END


    SET @Start = CHARINDEX('<',@HTMLText)
    SET @End = CHARINDEX('>',@HTMLText, @Start)
    SET @Length = (@End - @Start) + 1
    WHILE ( @Start > 0 	AND @End > 0 AND @Length > 0 )
	BEGIN
		SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
		SET @Start = CHARINDEX('<',@HTMLText)
		SET @End = CHARINDEX('>',@HTMLText,@Start)
		SET @Length = (@End - @Start) + 1
	END


    set @HTMLText = REPLACE(@HTMLText, '&nbsp', ' ');

    while ( PATINDEX('%[' + CHAR(9)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255) + '-' +CHAR(13) +']%', @HTMLText COLLATE Latin1_General_100_BIN2 ) > 0)
    begin 
        set @HTMLText = REPLACE(@HTMLText, SUBSTRING(@HTMLText, PATINDEX('%[' + CHAR(9)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255) + '-' + CHAR(13) +']%'  , @HTMLText COLLATE Latin1_General_100_BIN2 ), 1 ), ' ')
    end 

    RETURN LTRIM(RTRIM(@HTMLText))
END
GO

 

Strategy and Factory Pattern Combo

 Strategy and Factory Patterns 

These two design patterns go quite hand in hand. While strategy is a behavioral pattern, the factory is a creational pattern. 

Strategy Patterns: Allows us to create interchangeable code that can be changed at runtime. Basically we create an interface and multiple concrete classes implementing that interface each concrete class would have a algorithm to be applied . Any of these concrete classes can be used as long as they implement the interface.  

Factory Pattern: Allows us to create patterns without specifying the exact type. In this pattern we return an interface and the actual object. The core behavior of the factory pattern is that it allows creation of an object based on an interface and return the same. This works hand in hand with Strategy as factory can take care of creating the object based on the strategy (logic) that has to be applied.     

Runtime Controls

 Runtime controls can provide a lot of flexibility and sometimes this is required. I spent a lot of time to understand the details of how this works. Below are the basics 

• Runtime controls should be created 

In the below code snipped, a group control is being used to add all the controls to. As the same group is being used multiple times the code clears off all the controls at the start and then adds new controls. 

 
	    str                 controlName;
            FormStringControl   formBuildLookupControl, formBuildStringControl;
            FormIntControl      formBuildIntControl;
            FormDateControl     formBuildDateControl;
            FormRealControl     formBuildRealControl;
            FormGroupControl    FormBuildGroupControl;

            //unload all the existing controls within the given group
            while ( GroupVariables.controlCount() )
            {
                element.design().removeControl( GroupVariables.controlNum(1).id() );
            }

            _controlsMap = QtnPackageLineControls::loadControls( _pkgCode, _NewItem.data() );
            _controlsEnumerator = _controlsMap.getEnumerator();

            //add all the controls to the group
            while ( _controlsEnumerator.moveNext() )
            {
                QtnPackageLineParams    _controlParams = _controlsEnumerator.currentValue();
                controlName = _controlsEnumerator.currentKey();
            
                FormBuildGroupControl = GroupVariables.addControl( FormControlType::Group, strFmt("%1Group", controlName));
                FormBuildGroupControl.caption( _controlParams.parmControlLabel() );
                FormBuildGroupControl.visible(true);

                switch (_controlParams.parmControlType() )
                {
                    case QtnPackageConfigTypesEnum::Date:
                        FormBuildDateControl = FormBuildGroupControl.addControl( FormControlType::Date, controlName);
                        FormBuildDateControl.registerOverrideMethod( methodStr( FormDateControl, modified ), methodStr(QtnPackageConfigLookupValue, "DynamicsDateControl_modified"), runTimeMethods);
                        FormBuildDateControl.visible(true);

                        break;

                    case QtnPackageConfigTypesEnum::Integer:
                        formBuildIntControl = FormBuildGroupControl.addControl( FormControlType::Integer, controlName);
                        formBuildIntControl.registerOverrideMethod( methodStr( FormDateControl, modified ), methodStr(QtnPackageConfigLookupValue,"DynamicsIntControl_modified"), runTimeMethods);
                        formBuildIntControl.visible(true);
                        break;

                    case QtnPackageConfigTypesEnum::Lookup:
                        FormBuildStringControl = FormBuildGroupControl.addControl( FormControlType::String, controlName);
                        FormBuildStringControl.registerOverrideMethod(methodStr( FormStringControl, modified ),  methodStr(QtnPackageConfigLookupValue,"DynamicsStringControl_modified"), runTimeMethods);
                        FormBuildStringControl.registerOverrideMethod(methodStr( FormStringControl, lookup ),  methodStr(QtnPackageConfigLookupValue,"DynamicsStringControl_lookup"), runTimeMethods);
                        FormBuildStringControl.lookupOnly(true);
                        FormBuildStringControl.visible(true);
                        break;

                    case QtnPackageConfigTypesEnum::Real:
                        FormBuildRealControl = FormBuildGroupControl.addControl( FormControlType::Real, controlName);
                        FormBuildRealControl.registerOverrideMethod( methodStr( FormDateControl, modified ),methodStr(QtnPackageConfigLookupValue,"DynamicsRealControl_modified"), runTimeMethods);
                        FormBuildRealControl.visible(true);
                        break;

                    case QtnPackageConfigTypesEnum::String:
                        FormBuildStringControl = FormBuildGroupControl.addControl( FormControlType::String, controlName);
                        FormBuildStringControl.registerOverrideMethod(methodStr( FormStringControl, modified ),  methodStr(QtnPackageConfigLookupValue,"DynamicsStringControl_modified"), runTimeMethods);
                        FormBuildStringControl.visible(true);
                        break;
                }
            }

 

• The registerOverrideMethod should be used to provide a callback method 

When the controls are created the override method is also provided. There are multiple parts to the override methods as shown below: 

   formButtonControl.registerOverrideMethod(
    methodStr(FormButtonControl,clicked), //method to override
    methodStr(testClass,testMethod),      //method to invoke
    new testClass());                     //object of class containing method

 The registerOverideMethod is called using an instance of a control, so it already knows which source object is being overloaded. The rest of the parameters are used as follows: 

1. The first parameter identifies the method of this object to overload. The name of the method can be identified using the methodStr function; make sure to use the right control type class.  
2. The second parameter the target method to be called instead 
3. The third parameters identified the runtime object to find the target methods in

• The next step is to create the callback method that would be called at runtime on the control being created and loaded 

    public boolean DynamicsIntControl_modified(FormControl _control)
    {
        boolean ret = true;
        
        info("control is modified");
        
        return ret;
    }

Below are some of the points to note about the runtime callback function:

1.  This method should always have a parameter of type FormControl type.
2.  The name of the method can be anything. The signature of the method can be determined by checking an actual method of a similar contol on the form.

Like in the above example we can create a modified method on a Integer type of control. Because the modified method of an integer control has a return value of boolean; we use the same return types in the callback method.

The number of defined parameters is not equal to the number of cell definitions in the parameter panel.

Every SSRS report has a parameters layout. The parameter layouts is where the parameters are presented to the user at the run time to drive the report behavior. The parameter layout has a layout with no of rows and columns that is used to present the parameters. If the no of parameters are more than the no of cells that the cross section of Rows and Columns can accommodate then we will have the following error: 

"The number of defined parameters is not equal to the number of cell definitions in the parameter panel."

Solution: 

In Dynamics we don't have a visual layout to control the parameters, hence we have to open the SSRS Report in XML format in order to edit the parameter layout. To open a report in XML format we need to right click the same and choose open with.. 

We should find the following section in the xml format and delete the same, so that the no of rows and columns are not defined and restricted. 

<ReportParametersLayout>
  <GridLayoutDefinition>
    <NumberOfColumns>3</NumberOfColumns> 
    <NumberOfRows>...</NumberOfRows>
  </GridLayoutDefinition>
</ReportParametersLayout> 

 

Add new design to Print Management

 Some of the document prints in the Dynamics Finance and Operations application have been configured as a part of the Print management framework. 

The print management framework makes it possible that the design of a document print is configurable. However, this also restricts the configuration to be picked from the set of reports that are configured to be a part of the framework. 

Hence, if a new design is being prepared for a document that needs to be configured in the document print management framework, then the following steps should be followed to register the new layout in the system. 

Find the PrintMgmtDocType class and create a subscriber to the getDefaultReportFormatDelegate method. 

       

class AFZPrintMgmtDocType_EventHandler
{
    [SubscribesTo(classstr(PrintMgmtDocType), delegatestr(PrintMgmtDocType, getDefaultReportFormatDelegate))]
    public static void getDefaultReportFormatDelegate(PrintMgmtDocumentType _docType, EventHandlerResult _result)
    {
        switch (_docType)
        {
            case PrintMgmtDocumentType::SalesFreeTextInvoice:
                _result.result(ssrsReportStr(AFZFreeTextInvoice, Report));
                break;

            case PrintMgmtDocumentType::SalesOrderInvoice:
                _result.result(ssrsReportStr(AFZSalesInvoice, Report));
                break;
        }
    }

}

There is a bug in the base system when a new DocType is registered in PrintManagement, the latest report registered becomes the default report. 

The default report is printed when we use the Original Preview option in the journal prints. 

If we wish to have a different report as the default report we can change the same from a backend table PrintMgmtReportFormat. 

This table has a row for each DocumentType that has been registered in the system and for each documentType there would be only one row that marked as System. If we want to change the default report then we have to switch on the system column for that report layout. 

Get FieldID TableID in SQL

 There are two important views that can be used in SQL scripts to refer to X++ TableID and FieldID values. 

SysTableIDView: In this table the ID column refers to TableID which can be fetched using the name of the table in the Name column.

SysTableFieldIDView: In this table the ID columns refers to the TableID for which the fields are being fetched and the FieldID column refers to the ID of the field in the given table. 

To fetch all the fields in a give table we can use the following SQL 

       
select TID.ID TableID, TID.Name TableName, FID.Name FieldName , FID.FIELDID
from SYSTABLEIDVIEW TID
inner join SYSTABLEFIELDIDVIEW FID
	on FID.Id = TID.Id
where TID.Name = 'InventDim'
 

SQL View for Dimension

 The following SQL view can be used to extract dimension information using SQL 

1. DefaultDimensionView: for default dimension each dimension value is listed as a separate row. Hence, we  might need multiple joins for one each dimension to get values in a columnar format. 

2. DimensionAttributeValueCombination: For Ledger dimension. Each dimension would exist as a column in this view. 

Choose design patterns

 As you learn more and more design patterns it becomes increasingly difficult to understand which one to use in a given scenario. 

Below are some of the observations:

CoR Vs Decorator:

CoR (Chain of Responsibility): To be used when there is a sequence of activities to be performed in a given sequence. These activities and their sequence can change based on a given business scenario. Each activity is a done by a separate class in the chain and we can add new classes as and when they are discovered. 

Decorator: Initially I was quite confused between CoR and Decorator as both of them provided classes that can be added to a base object and these can be changed at runtime. I later realized that the difference between the two is that while Decorator is designed to change the behavior of the base object, CoR is designed to perform different activities with each class. Which means that if the given functionality has to be enhanced based on conditions then decorators should be used, and if different functionalities have to be triggered in a given sequence CoR should be used. 

Summary: Decorators are additive in nature and every decorator handles every message. This is the basic behavior of the pattern. Hence when a client wishes to enhance one or more additional behaviors decorator is used. Decorator would exhibit at least a minimum of two behaviors.

Chain of Responsibility will exhibit one behavior or less. The potential for multiple links to handle one message is possible but not the intention. Hence ideally the cardinality is zero to one so when a CoR pattern is being used the message should be processed by one chain.

Strategy Vs Visitor

Strategy: Involves a class objects being induced into a method as a parameter and this contains an algorithm to do a certain process in a certain way. By parameterizing the algorithm the functionality can be enhanced later to accommodate new algorithms. 

Visitor: Visitor involves the current object being passed as an argument to the visitor class method and later invoking a specific method in the visitor class which can then access the current objects and perform any desired operation. 

So while strategy is used to change the behaviour, visitor is used to add new behavior 

Sort X++ Map into a List

 Maps are auto sorted based on the key. Sometimes it might be required to get these values sorted and retrieved in a particular order. 

Below is a code snippet where a map is sorted and stored in a List where it can be retrieved in a sequential manner. The sorting of the map elements is done based on the value of each item, but the list is populated with the keys. 

       

	//Sorting of the values
        //add the sorted map values into a list
        mapEnumeratorParent = resourceWeights.getEnumerator();
        resourceList = new List( Types::String );
        
        while ( mapEnumeratorParent.moveNext() )        //iteration 1
        {           
            lowestResource = mapEnumeratorParent.currentKey();
            lowestWeight = mapEnumeratorParent.currentValue();

            mapEnumerator = resourceWeights.getEnumerator();
            while ( mapEnumerator.moveNext() )      //iteration 2
            {
                if ( mapEnumerator.currentValue() < lowestWeight )
                {
                    lowestResource = mapEnumerator.currentKey();
                    lowestWeight = mapEnumerator.currentValue();
                }
            }

            resourceList.addEnd( lowestResource);

            resourceWeights.remove( lowestResource ) ;
            mapEnumeratorParent = resourceWeights.getEnumerator();
        }

 

Extra parameters on Report

 I have created a class with 2 parameters however when the Dialog box is rendered there are 4 parameters displayed as shown below

So basically when the parameters are rendered these are done from the SSRS report. When we open the SSRS report parameters we can notice that these additional parameters are added to the report and these are the same parameters which are repeating. 

To solve the issue, we need to delete the unwanted parameters from the parameters list and redeploy the report. 

Find Hyper-V Host

 When lost between multiple Hyper Hosts and Images you can always find the host of a hyper-V image using the registry. 

The following powershell command can be used to read the registry 

Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Virtual Machine\Guest\Parameters"  | Select-Object HostName

SQL remove invisible characters

You will need a combination of 3 functions 

1. PATINDEX: PATINDEX is used to find a position in a string where a specified character or pattern exists. In our chase we have specified all characters from ascii value 0 to 31, 127 and 255. 

2. SUBSTRING: Once the PATINDEX function return the location where one of the invisible characters is found, substring function extracts that 1 length string to isolate the invisible character. 

3. REPLACE: This function then replaces all occurrences of the invisible character found and replaces it with a zero length string.

select REPLACE([Text], SUBSTRING([Text], PATINDEX('%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'  , [Text] COLLATE Latin1_General_100_BIN2 ), 1 ), ' ') [Text] 
from DataExtract

Please note that casting the column to be replaced with binary collation is required else some of the characters are not parsed.  

As the above statement only replaces all occurences of the the first invisible character found. It might be more meaningful to extract this data into a table and update the text column replacement until there are no more rows found with invisible characters 

update DataExtract
set Text = REPLACE([Text], SUBSTRING([Text], PATINDEX('%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'  , [Text] COLLATE Latin1_General_100_BIN2 ), 1 ), ' ')
where  PATINDEX('%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'  , [Text] COLLATE Latin1_General_100_BIN2 ) > 0

Sometimes a value starting with a double quote '"' might cause an issue when one tries to paste the data in Excel, as excel tries to interpret a start and end of text column using double quote as a delimiter. So if you are copying and pasting values in excel make sure that the column does not start with a double quote else best is to replace such occurrences:

Update DataExtract
set  [Text] = SUBSTRING( [Text], 2, 10000 )
where left( [Text], 1 ) = '"'

Fetch the Enum labels in SQL Statements

 When dealing with writing SQL Statements the Enum values and their corresponding names force us to write switch case statements which is time consuming. A shortcut for this method is as follows. 

There is already a table in the D365 Database called SRSANALYSISENUMS this table is used to store the D365 labels for use in the Datawarehouse cubes. By default all the tables that are part of any perspectives are populated in this table. However, for some of our enum we can explicitly populate this table and use the same in SQL. 

Create the following job to populate any given enum into the SRSANALYSISENUMS table. 

    public static void main(Args _args)
    {
        SRSAnalysisEnums analysisEnums;
        DictEnum currentEnum;
        str currentEnumName;
        int valueCount;
        str enumItemName;
        int enumItemValue;
        RecordInsertList records;
        Dialog dialog;
        DialogField  dfEnumName;

        dialog = new Dialog("Please enter the enum name");
        dfEnumName = dialog.addField(extendedTypeStr("Name"));
        dfEnumName.label("Enum name");
        if (dialog.run())
        {
            records = new RecordInsertList(tablenum(SRSAnalysisEnums));
         
            currentEnumName = dfEnumName.value();
            currentEnum = new DictEnum(enumName2Id(currentEnumName));
            if(currentEnum == null)
            {
                throw error(strfmt("Enum with name %1 does not exists.",currentEnumName));
            }
            valueCount = currentEnum.values();
            ttsbegin;
            delete_from analysisEnums where analysisEnums.EnumName == currentEnum.name();
            for(int j = 0; j < valueCount; j++)
            {
                enumItemName = currentEnum.index2Symbol(j);
                enumItemValue = currentEnum.index2Value(j);
             
                select firstfast forupdate EnumName, EnumItemName, EnumItemValue
                from analysisEnums where analysisEnums.EnumName == currentEnum.name() && analysisEnums.EnumItemValue == enumItemValue;

                if (analysisEnums)
                {
                    if (analysisEnums.EnumItemName != enumItemName)
                    {
                        analysisEnums.EnumItemName  = enumItemName;
                        analysisEnums.update();
                    }
                }
                else
                {
                    analysisEnums.EnumName      = currentEnum.name();
                    analysisEnums.EnumItemName  = enumItemName;
                    analysisEnums.EnumItemValue = enumItemValue;
                 
                    records.add(analysisEnums);
                }
            }
            records.insertDatabase();
            ttscommit;

            Info("Completed");
        }
    }

 

Once this job is created and compiled we can run this class for each Enum that we want to be populated. To run the class from the url use the following syntax: 

https://ds-dev-7658e683048e57b40devaos.cloudax.dynamics.com/?cmp=ds01&mi=SysClassRunner&Cls=SRSAnalysisEnumsGenerator

In the above URL the servername and cmp parameter should be replaced to match your environment.

Split CSV values as columns

To split a CSV value in a given column into different individual columns use the following trick. 

Firstly, let us look at the different functions that are involved in this process. 

1. Table Value Constructor: The Value clause can be used to create a table out of values. This clause is used in the insert statement however its interesting to note that it can also be used in joins to create a derived table with fixed values. 

E.g.: In the examples below a derived table Source has been created using literal values. 

select Source.NewReasonType, Source.NewName
from (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion')) 
     AS Source (NewName, NewReasonType)

2. Apply operator: Just like the join operator we have another operator apply which has been introduced in SQL and this operator indicates to the SQL Engine that the clause has to be evaluated for each row

3. JSON_VALUE: This function is used to extract a scalar value from a Json string. The function accepts 2 paramters 1. Json expression and 2. subscript of the node to refer.

E.g.

       

DECLARE @data NVARCHAR(MAX);
SET @data = N'{  
     "info":{    
       "ID":1,  
     "Name":"Akshat",
       "address":{    
         "City":"Gurgaon",  
         "Country":"India"  
       },  
       "Favorite_Subject":["English", "Science"]  
    },  
    "type":"student"  
 }';  
SELECT JSON_VALUE(@data, '$.info.Name') AS 'Name', 
       JSON_VALUE(@data, '$.info.Favorite_Subject[1]') AS 'Favorite Subject';

 

E.g: If Display value contains the dimension values separated by a pipe (|) the below statement will break it into individual dimension columns

 select
JSON_VALUE(JS,'$[0]') Dim1, JSON_VALUE(JS,'$[1]') Dim2, JSON_VALUE(JS,'$[2]') Dim3, JSON_VALUE(JS,'$[3]') Dim4
from DIMENSIONSETENTITY T17
Cross Apply (values ('["' + replace( T17.DISPLAYVALUE ,'|','","')+'"]') ) B(JS)
 

SSRS Report CreatedTransactionId

CreatedTransactionId, is a read-only field maintained by AX.  

For a table that has its CreatedTransactionId property set to Yes, when an insert occurs, AX automatically sets the CreatedTransactionId value (to the current value of appl.curTransactionId()).  

The CreatedTransactionId is tied to the outermost transaction only – that is, if there are nested transactions, they do not get their own CreatedTransactionId, but instead share the same one as the master (outermost) transaction.   Only after the ttsLevel has dropped back to 0 will a new CreatedTransactionId get generated.

SQL Enable Maintenance Mode

Certain tasks like enabling of a new accounts Structure requires the SQL database to be in maintenance mode. This is done using the LCS portal for UAT and PROD environments. However, if the same has to be done on the DEV machine then the database has to be manually put into maintenance mode. 

To enable maintenance mode in the SQL server database of the DEV Machine using the following script. 

update SQLSYSTEMVARIABLES SET VALUE = 1 where PARM = 'CONFIGURATIONMODE'

Skip the upgrade checklist

 SysCheckList_Update::finalizeMinorUpgrade();

Find SID for a user

 Use the following command to find the SID for a given user 

wmic useraccount where name="USER" get sid

SSRS Report Company does not exist error

 Recently has this issue where when a report was executed an error was logged. 

The issue identified was with the contract class. The report i created was a RDP report however the contract class that i created was extending SrsReportRdlDataContract.

When a contract class is being created for a RDP Report then the contract class need not extend any base report and can simply implement the required interfaces like SysOperationValidatable. 

Item or Product or Product Master

Item and Product are terms that have a similar meaning and as both terms are used in the D365 system, it is confusing and important to understand the glossary in its true terms. 

The term that D365 associates for an Inventory entity is Product. 

When a product is being designed at an abstract level it is called Product Master. This serves as a template or model for creation of the actual products. We have three important aspects to consider when thinking about a product master.

1. Product Dimension: Will the product have additional variants on its inception as a product.
2. Storage Dimension: What level of storage details would be maintained for the product when it is created. This could be a combination of Location, Warehouse, Pallet etc 
3. Tracking Dimension: Will the product be tracked for Batch and Serial information during its movement across the different transactions.  

With all the above attributes decided and attached to the product master; an Item is created which is a physical concept and will have transactions created against itself. 

Product also has a classification called Product Type which could either be Item or Service which are self-explanatory.  

Products are released to legal entities where they are relevant and released Items are created in the entities where they are authorized.