Formatting code block blog post

 Below is the style for formatting a code block:

 
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: &quot;Andale Mono&quot;, &quot;Lucida Console&quot;, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">       <code style="color: black; overflow-wrap: normal; word-wrap: normal;">

	--code goes here

 </code><br /></pre>

insert_recordset with type casting

Its a well known fact that the insert_recordset is used for performance reasons when we want to use set operations instead of doing record based loop and roundtrips to the server. 

The only problem being that there is no type casting allowed. Consider a scenario like below where there is a data mismatch between the fields being inserted to and the fields being selected from: 

In the statement below using the int2str function is not allowed so how do we type cast ? 

       

            insert_recordset PriorityTmp( PriortyCode, Description, OrderPlaningId, WrkCtrId, PriorityNum )
            select "P" + int2str( PriorityNum) , "P" + int2str( PriorityNum ) , OrderPlaningId , WrkCtrId, PriorityNum
            from LocalOrderPlanning
            order PriorityNum asc
            where LocalOrderPlanning.RecId         != orderPlanning.RecId &&
                LocalOrderPlanning.WrkCtrId         == orderPlanning.WrkCtrId &&
                LocalOrderPlanning.PlanningStatus   == OrderPlanning.PlanningStatus &&                
		LocalOrderPlanning.MergeLine ==  NoYes::NO;

Unfortunately there is no direct solution for the above using the statements, basically we would want to use a typecast function at the SQL level and this can only be done using views or direct sql statement. For this case we will try to create a view with the necessary type casting : 

The view would have a computed column with the below method :

       

    static server str PriorityCode()
    {
        DictView dictView2;
        str sPriorityNumField, sReturnField;

        // Construct a DictView object for the present view.
        dictView2 = new DictView(tableNum(AFZOrderPlanningView));

        // Get a string that has the target field name
        // propertly qualified with an alias (such as "A." or "B.").
        sPriorityNumField = dictView2.computedColumnString("AFZOrderPlanning",  fieldStr(AFZOrderPlanning, PriorityNum ), FieldNameGenerationMode::FieldList, true);

        sReturnField = strFmt(" 'P' + cast( %1 as varchar)", sPriorityNumField);

        return sReturnField;
    }

The above column is then added to the fields of the view and is used in the function above. The advantage of using the computed columns is also that it can be filtered upon unlike the display methods that are used in AX. 

Please Note: When adding a computed column to the view, there are two methods to choose from, on the computed column property sheet. These are Method and ViewMethod. Please ensure that ViewMethod property is used to specify the name of the method. 

Deploying User control in EP

Once a certain functionality has been developed using User Controls for EP, the next step is to deploy this functionality on the EP portal. Following are the steps that are used to deploy an SSRS report, using a parameters forms (User control) on EP.

Basically the process involves two steps :- 

1. Creating the UserControl to accept the report parameters
2. Creating a new page to host the UserControl, which is used to accept the parameters. 
3. Creating a navigation to viewing this page.

Step 1: Creating of the user control to accept report parameters

• Start by creating a new project in Visual Studio for the EP Web Application and then add EP User Control to the project. 
• The control that would be used for viewing a report would be the AxReportViewer control and to be able to use this we will have to ensure that the AxBaseUserControl is added and reference to the ApplicationProxies is added to the project.  

•  Create the necessary interface for accepting the parameters on the screen. This could be a combination of asp and Ax controls if required. 
• The Report that needs to be printed should be a SSRS report and should be based on the SRSReportDataProviderBase class
• The report should have an output menu ( LeaveBalance in this example ) 
• Add the AxReportViewerControl from the toolbox and assign the output menu as a property as shown below 

• Now lets add the code for the print button click. The first thing we need to do is to open our SSRS report and view all the parameters that are present. Its important that we assign a value to all the parameters that the report uses
• Please note that we can ignore the DynamicParameter as these are due to query dialogs on the report and cannot be used on the EP.

• Now that we know we have three parameters as highlighted above being used in the report. Please create the button click procedure to pass all the parameters to the report and when the AddParameters method is called the report is triggered. 

public partial class SPYLeaveBalanceReport : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        Dictionary parms = new Dictionary();

        parms.Add("LeaveDetails_AsOnDate", DateTime.Parse(txtAsOnDate.Text).ToString("G"));

        if ( chkShowDetails.Checked == true)
        {
            parms.Add("LeaveDetails_ShowDetails", "Yes");
        }            
        else
        {
            parms.Add("LeaveDetails_ShowDetails", "No");
        }            
        parms.Add("LeaveDetails_absenceCode", "");

        this.axLeaveBalanceReport.AddParameters(parms);        
    }
}

Step 2: Creating a page to host the User control created in step 1

• Open Enterprise portal and navigate to the page where the new link has to be created and then click on -> Site Actions –> More Options –> Page –> Create –> 

• Provide name for the page –> Select the layout –> Select Library as Enterprise Portal –> OK.

Please use the right page type (Header, Footer, 3 columns) else the page might not show the page navigation of the parent page. A new page will now be created relative to the page we started from. The address of the newly created page will be visible in the address bar. Click edit to open the page in design mode and add a webpart called SPYLeaveBalancesESS created above.

Step 3: Create a navigation to the above sharepoint page

Now that the sharepoint page with the UserControl has been created and saved. We have to create a navigation to the same from the menu where we desire. 

• So we start from the front end screen where we wish to place the new link. Given the screen below one would start from the Web Menus in the AOT

• We browse the Web menu intuitively to figure out the above menu. Once we get the menu in the property sheet we will be able to find the WebMenu that is used in the property sheet under the property QuickLaunch : SPYHCPMListPageQuickLaunch (shown below)

Once the Web Menu name is identified we can expand the same in the AOT and we will be able to notice that the Web Menu is a group of Web Menu Items of url type. 

Each Menu item is hence a url to a specific page on the sharepoint portal. We will have to now create a new URL web menu item and add the page that we created in step 1 as the URL. The URL should begin relative from the portal where it is being assigned. Hence the url for web page "Leave balance.aspx" which was added under employee portal would be 

EmployeeServices/Enterprise%20Portal/Leave%20balance.aspx 

Once the menu has been created and the URL has been specified the page can be imported into AOT from the sharepoint portal. This is useful when the deployment has to be migrated from the DEV server to the Production server. Once the page has been imported we will get an Info Log as shown below.

The last piece of the deployment is to add this newly created Web Menu URL to the Web Menu by dragging and drop it on the menu. 

 

Format date as per user setting

 In SSRS when dates need to be formatted as per the user preferences and time zone use the below expressions: 

       
Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, System.DateTime.UtcNow, "d", Parameters!AX_RenderingCulture.Value)
 

In the above expression System.DateTime.UtcNow should be replaced with the actual data.

Exists join in select statement

Exists joins are required when we want to check for an existence of a records in an table without having duplicate records returned. I had this strange problem today where i was trying to fetch the records using an exists join and the results where not generating.

       

select generateOnly forceLiterals sum(Qty) from projEmplTrans
     where projEmplTrans.TransDate >= tsStartDate &&
	projEmplTrans.TransDate <= tsEndDate &&
	projEmplTrans.ProjId == projId
    exists join journalId from payrollJournalTable
	where payrollJournalTable.JournalId == conPeek( payrollJournalCon, i )
    join worker from payrollJournalLine
	where payrollJournalLine.Worker == projEmplTrans.Worker
	&& payrollJournalLine.PayrollJournal == payrollJournalTable.RecId;

 

The SQL Statement that is generated is

       

SELECT SUM(T1.QTY) FROM PROJEMPLTRANS T1 
WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'0001')) AND (((T1.TRANSDATE>={ts '2021-02-01 00:00:00.000'}) 
AND (T1.TRANSDATE<={ts '2021-02-28 00:00:00.000'})) AND (T1.PROJID=N'BAA'))) 

AND EXISTS 

(SELECT 'x' FROM SPYPAYROLLJOURNALLINE T2 CROSS JOIN SPYPAYROLLJOURNALTABLE T3 
WHERE (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'0001')) AND (T2.WORKER=0)) 
AND (((T3.PARTITION=5637144576) AND (T3.DATAAREAID=N'0001')) AND ((T2.PAYROLLJOURNAL=T3.RECID) AND (T3.JOURNALID=N'0001-00000181'))))

 

The problem here is that the T2.Worker == 0 which ideally should have been T2.Worker == T1.Worker. 

After a lot of trials realized that the Worker field in projEmplTrans is a deprecated field and hence the system was equating it to zero (0).

XDS Security

The extended security model is used to create a customer rule for limiting the access to records. In simple words, XDS is placing a Where clause on any SQL Select, Update, Delete, or Insert statement based on parameters from another related table. 

Scenario: Users are clubbed in a group and this group is then associated with a Sales Order. A user should only be allowed to view the sales orders which belong to a group where the users is associated.   

The different components of the XDS security are : 

1. Query: Query created for a table which is then extended to all the related tables where this table is a foreign key. 

For the above scenarios because we need to filter the Sales Order, so we will find a table where the Sales Order has a relationship. Eg: ( CommissionSalesGroup ) however the sales commission group does not have users associated to it, so we create a new table ( AFZCommissionSalesGroupUsers ). Now the query that we will create will be based on the table which has a direct link the sales order (CommissionSalesGroup ) and we will restrict this based on a join with the (AFZCommissionSalesGroupUsers) where the user= curUserID()

Next step is to add a security policy object.

2. Security policy: Once the query is designed, the next step is to plan which all table would this query restrict access to. A policy is used to link the query (created in step 1) with a list of tables which need to be restricted for security (constraint tables). 

It also has a policy context which is used to determine when the policy is applied, this is generally set to a security role (i.e. anyone which the configured role would be applied with the record level security). 

The security policy also has a primary table mentioned which is the root of the query that is created. The primary table should have an explicit relationship with all the tables where the constraint is being applied. 

Every security policy would also have a property called Constrained Table which can be set to Yes or No to decide if the policy would be applied on the primary table itself or not. 

When the policy is applied on the primary table it be already applied before the xds method is called in MyTables. If the xds method needs access to the records in the primary table, this might be a concern as the policy would already be applied and not all the rows in the primary table would be available for the xds method. In cases like this a view based on the primary table can be created and that should be used instead of the actual table. 

Alternatively within the XDS method if the xds needs to be by-passed the below constructs can be used

in AX2012 use 

       
XDSServices.setXDSState(0)
 

in Dynamics F&O you can use the below code block 

       
unchecked(Uncheck::XDS)
{
    select ValidTimeState(_asOfDate) maxof(ValidTo) from hcmEmployment
        where hcmEmployment.Worker == _workerRecId;
}
 

Ensure that you set the Constrained Table property to Yes and add the primary table is set to the primary table of the Query that is attached. 

 

3. MyTables : These are special tables created to be used in the security query, where custom logic can be written to populate data. These table names are prefixed with "My" keyword and the logic is written in xds function.

       

    public RefreshFrequency xds()
    {
        MyAFZCategory   myCategory;
        SPYHCMWorker       hcmWorker; //view is used as the primary table is contrained
        DirPersonUser   dirPersonUser;

        AFZEmployeeCategory afzEmployeeCategory;
        AFZWorkerCategoryUserAccess workerCategoryUserAccess;        
       
        insert_recordset myCategory(HcmWorkerRecId)
        select RecID from hcmWorker
        join afzEmployeeCategory
            where afzEmployeeCategory.Code == hcmWorker.AFZCategoryCode
        join workerCategoryUserAccess
            where workerCategoryUserAccess.UserId == curUserId()
            && afzEmployeeCategory.CodeGroup == workerCategoryUserAccess.CodeGroup ;             

        /*
        select generateOnly forceLiterals RecID from hcmWorker
        join afzEmployeeCategory
            where afzEmployeeCategory.Code == hcmWorker.AFZCategoryCode
        join workerCategoryUserAccess
            where workerCategoryUserAccess.UserId == curUserId()
            && afzEmployeeCategory.CodeGroup == workerCategoryUserAccess.CodeGroup ;
        info( hcmWorker.getSQLStatement() );
        */


        //Calculate current worker value
        select firstonly PersonParty, ValidFrom, ValidTo from dirPersonUser
            where dirPersonUser.User == curUserId();

        select firstonly RecId from hcmWorker
            where hcmWorker.Person == dirPersonUser.PersonParty;

        myCategory.initValue();
        myCategory.HcmWorkerRecId = hcmWorker.RecId;
        myCategory.insert();

        // This is static data, so only refresh when session is restarted
        return RefreshFrequency::PerSession;
    }

}

When a table is being added to the constraint table. There are two options:

• Constraint Table : Allows us to select tables from which the data would be filtered. These tables should have a relationship defined with the primary table of the xds query. 
• Constraint Expressions: Allows us to select tables and define a realtionship of our own. 

In the example below a InventTable is being added to the constraint tables with an explicit relationship (InventTable.ItemID == InventItemPurchSetup.ItemID)

The XDS security policies are saved in the database and hence its important to have the project "Synchronize Database on build" turned on when changes are being done to the security policy. The table where the security filters are saved is ModelSecPolRuntimeEx. This table also stores a  

       
select QUERYOBJECTAOTNAME, CONSTRAINEDTABLE, MODELEDQUERYDEBUGINFO , MODELEDQUERYPACKDATA
from ModelSecPolRuntimeEx 
where [Name] like 'AFZ_NonStoppedItemPolicy'
 

Some times the containers in the database might not get updated and you might get an error as below when the security is applied. 

This only indicates that the Query is not updated in the database. To update the required data in the database against a XDS query execute the below code in a job class.