D365FO: Add query ranges to form data sources in X++

Purpose:

In this post we’re going to learn about how can we add ranges to form data sources using event handlers.

Application:

Dynamics 365 for Finance and Operations

Business requirement:

The requirement goes like business wants to restrict access to Purchase order confirmations such that user should only be able to see confirmations for their purchase orders given that they have been given a specific security role.

Procurement and sourcing > Purchase orders > Purchase order confirmations > Purchase order confirmations

Solution:

This can be achieved by implementing event handler method for OnQueryExecuting event of one of the form’s data sources. It is important to note here that we’re using expression to build a composite filter in the query range and that’s why we’re apply the range to the RecId field.

Code

    /// <summary>
    /// Adds ranges to the form datasource.
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    [FormDataSourceEventHandler(formDataSourceStr(VendPurchOrderJournal, VendPurchOrderJour), FormDataSourceEventType::QueryExecuting)]
    public static void VendPurchOrderJour_OnQueryExecuting(FormDataSource sender, FormDataSourceEventArgs e)
    {
        ICH_HcmWorkerSite workerSite;
        QueryBuildDataSource qbds_PurchTable;
        QueryBuildDataSource qbds_PurchTableExtended;
        HcmWorker currentWorker;
        QueryBuildRange qbr_PurchTableExtended;
        QueryBuildRange qbr_PurchTable;
        Array purchSecRoleIds;
        boolean isUserInRole;

        purchSecRoleIds = ICH_PurchSecRoleTable::getSecurityRoles();

        if (purchSecRoleIds)
        {
            isUserInRole = SysUserInfo::ICH_IsUserInRole(purchSecRoleIds, curUserId());

            if (isUserInRole)
            {
                // Range for Orderer, Requester, Approver, Assignee fields
                qbds_PurchTable = sender.query().dataSourceName(sender.name()).addDataSource(tableNum(PurchTable));
                qbds_PurchTable.joinMode(JoinMode::InnerJoin);
                qbds_PurchTable.relations(true);

                qbds_PurchTableExtended = qbds_PurchTable.addDataSource(tableNum(ICH_PurchTableExtended));
                qbds_PurchTableExtended.joinMode(JoinMode::InnerJoin);
                qbds_PurchTableExtended.relations(true);

                qbr_PurchTableExtended = SysQuery::findOrCreateRange(qbds_PurchTableExtended, fieldNum(ICH_PurchTableExtended, RecId));
                qbr_PurchTableExtended.status(RangeStatus::Hidden);
                qbr_PurchTableExtended.value(strFmt('((PurchOrderer == %1) || (PurchRequester == %2) || (PurchApprover == "%3") || (PurchAssignee == "%4"))',
                    HcmWorkerLookup::currentWorker(),
                    HcmWorkerLookup::currentWorker(),
                    curUserId(),
                    curUserId()));
            }
        }        
    }