Save/Copy the Filters/Queries/Inquiries to other users in AX

Blogs & code stuff are based on my research & development work. I try my best to publish the safe material. Applying of code in production environment is at your own risk. You can experiment/test my code and then apply for your necessities with care.

Hi,
This is my first blog related to ERP system - Microsoft Dynamics AX

While being new to AX I have started learning about it bit by bit.
I greatly appreciate that AX is truly extra-ordinary platform with full of flexibility and scaaaalability.

This article is about copying the Filters/Queries/Inquiries to other users in AX

In the early stage of implementation we planned to share the filters/queries/Inquiries to other users. I was thinking how if we make & setup all the required filters initially and then start sharing them to the needed users and similarly users also wanted to share their queries to each other on daily basis.

But alas, our implementation consultants clearly informed us, it is not possible to share the user queries/filters. The AX has not provided this feature by any means.

Upon searching at AX forums, I found that many people wanted this feature but unfortunately no one has yet got any solution. Few posts suggested to use direct SQL statements in AX database to copy the query-data to the users usage-values in tables, this is workable solution and it can be done by only experienced database professionals but not viable to the end users for their regular needs.

So friends here is the ultimate solution in AX

I have written a safe method in AX for copying the Filters/Queries/Inquiries to other users

It will allow the end-users to save/copy their filters/queries to other users simply and users does not require any technical awareness…

Look at the following screens how the users will save their queries to other users:

Fig1: Using the “Save as” option to save the query

 

Fig2: Save Inquiry dialog now has “Copy To User” field

Fig3: Simply select/enter the desired user to copy the currently saving filter/inquiry

Fig4: Info dialog showing the query is successfully saved to the desired user

Now you can simply ask the beneficiary users to start enjoying the filter query you have copied to them

If the selected user is already having the same-name query then the confirmation to overwrite the query will be shown.

Fig5: Confirmation to overwrite if the filter/query is already exists in the beneficiary user

However the above situation will never arise because the target query name will be appended with audit-details while saving it to beneficiary user

For safety & tracking and to avoid overwriting the beneficiary users queries,

The target query-name will be appended with the audit details while being saved to the beneficiary users. Audit details such as concatenating the current-userid and current-datetime in the query-name, it will help the other users to know who saved the queries to them and when were they saved. Also it will avoid overwriting the users' queries if they already have same named queries.

Following figure shows all the queries saved to the beneficiary users furthermore showing who and when were these queries saved to them

Fig6: The screen shows the queries saved to him by other user with their userid and datetime in the query-name

Well friends look at the source code finally then…
Note: The solution is written and tested in Microsoft Dynamics AX 2009 with SP1.
The source code for saving filter/Inquiries to other users in X++
..

//-------------------------------------------------------------------------------------------------------
//
//  Form:           SysQueryForm
//
//  Method:         saveQueryToUser
//
//  Parameters:     Name   _queryName     -- Name of the filter/inquiry
//                  UserID _saveToUserID  -- Beneficiary UserId to whom to copy the filter/inquiry
//
//  Retruns:        boolean [true for success, false for fail]
//
//  Description:    This method saves a copy of the filter/query to the other selected user.
//
//  Related Method: SysQueryForm.querySave is extened to accept the userid
//                  to save the copy of filter/query
//
//  Called From:    SysQueryForm.querySave
//
//-------------------------------------------------------------------------------------------------------
// saveQueryToUser - skf-28JAN2010
// Author          - skf@axaptian
//-------------------------------------------------------------------------------------------------------
private boolean saveQueryToUser(Name _queryName, UserID _saveToUserID)
{
    int i, j, beneficiaryUserQueryPackCount, currentUserQueryPackCount, sourceElementCount;
    boolean ok = true, break_outerloop = false;
    UserInfo userInfo;
    container sourceElement, beneficiaryUserQueryPack, currentUserQueryPack;
    str queryNameMAX;   // full name with adding the audit values in the query-name
    str userIDandDT;    // for audit (current-userid and current-system-datetime)
    Dictionary dict;
    DictType dictType;
    ;

 _queryName=strRtrim(strLtrim(_queryName));
 
    if(strlen(_queryName)==0)
        return false;

    _saveToUserID=strRtrim(strLtrim(_saveToUserID));

    // simply ignore & return if the beneficiary-userid is current-userid or beneficiary-userid is empty
    if(_saveToUserID==curUserID() || strLen(_saveToUserID)==0)
        return false;

    // simply ignore & return if the userid is not-valid
    select RecId from userInfo where userInfo.Id==_saveToUserID;
    if(!userInfo.RecId) 
    {
        // info(strfmt('Cannot copy the query to user [%1], invalid user-id entered', _saveToUserID));
        return false;
    }

    currentUserQueryPack=this.pack();

    if(conLen(currentUserQueryPack)<2)
        return false;  // for any reasons if the currentUserQueryPack is empty

    beneficiaryUserQueryPack = classfactory.lastValueGet(element.lastValueDataAreaId(),
                                                         _saveToUserID,
                                                         element.lastValueType(),
                                                         element.lastValueElementName(),
                                                         element.lastValueDesignName());
 
    //-----------------------------------------------------------------------------------------------
    // for safety and tracking, adding the audit details to the beneficiary query-name
    // such as concatenating the current-userid and current-datetime in the query-name
    // it will help the other users to know who saved the query to them and when was it saved
    // also it will avoid overwriting the users' queries if they already have same named queries
    // additionally, care should also be taken that Name-Field will not allow more than 60 characters
    // if the beneficiary-name length is more than Type-Length characters, 
    // then the query-name will be truncated to accomodate the audit values
    //-----------------------------------------------------------------------------------------------
    userIDandDT=strfmt("(%1-%2)",
                        curUserId(),
                        DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::utcNow(),
                                                          DateTimeUtil::getCompanyTimeZone()));

    queryNameMAX = strfmt("%1 %2", _queryName, userIDandDT);
    dict = new Dictionary();
    dictType = dict.typeObject(dict.typeName2Id(extendedtypestr(Name)));
    if(strlen(queryNameMAX)>=dictType.stringLen())
    {
        queryNameMAX = strfmt("%1 %2",
                              subStr(_queryName, 1, strlen(_queryName)-(strlen(queryNameMAX)-dictType.stringLen())),
                              userIDandDT);
    }
    //-----------------------------------------------------------------------------------------------

    //-----------------------------------------------------------------------------------------------
    // check for the query-data in the beneficiaryed-user
    //-----------------------------------------------------------------------------------------------
    beneficiaryUserQueryPackCount = conLen(beneficiaryUserQueryPack);
    if(beneficiaryUserQueryPackCount<2) // if the query data is not set in the beneficiary user
    { 
        // set the beneficiaryUserQueryPack with initial queryset values
        beneficiaryUserQueryPack = [conPeek(currentUserQueryPack, 1), conPeek(currentUserQueryPack, 2)];
        beneficiaryUserQueryPackCount = conLen(beneficiaryUserQueryPack);
    }

    //-----------------------------------------------------------------------------------------------
    // check for the query-already-exists in the beneficiaryed-user
    //-----------------------------------------------------------------------------------------------
    for (i=1; i<=beneficiaryUserQueryPackCount; i++)
    {
        if(typeof(conPeek(beneficiaryUserQueryPack, i))==Types::Container)
        {
            sourceElement = conPeek(beneficiaryUserQueryPack, i);
            sourceElementCount = conLen(sourceElement);
            for (j=1; j<=sourceElementCount; j++)
            {
                if(typeof(conPeek(sourceElement, j))==Types::String)
                {
                    if(conPeek(sourceElement, j)==queryNameMAX)
                    {
                        // confirm to overwrite the query for the selected user
                        if(Box::yesNo(strfmt("Saving Query To User [%1]\n\n", _saveToUserID) + 
                                             strfmt("@SYS55302" + '\n\n', _queryName) + 
                                             strfmt('%1%2', "@SYS3074", '?'), DialogButton::Yes) == DialogButton::No)
                        {
                            ok = false;
                        }
                        else
                        {
                            beneficiaryUserQueryPack = conDel(beneficiaryUserQueryPack, i, 1);
                        }

                        break_outerloop = true;
                        break;
                    }
                }
            }
            if(break_outerloop==true)
                break;
        }
    }
    //-----------------------------------------------------------------------------------------------


    //-----------------------------------------------------------------------------------------------
    // if everything is ok, then save the query to beneficiary-user
    //-----------------------------------------------------------------------------------------------
    if(ok==true)
    {
        break_outerloop = false;
        currentUserQueryPackCount = conLen(currentUserQueryPack);
        for (i=1; i<=currentUserQueryPackCount; i++)
        {
            if(typeof(conPeek(currentUserQueryPack, i))==Types::Container)
            {
                sourceElement = conPeek(currentUserQueryPack, i);
                sourceElementCount = conLen(sourceElement);
                for (j=1; j<=sourceElementCount; j++)
                {
                    if(typeof(conPeek(sourceElement, j))==Types::String)
                    {
                        if(conPeek(sourceElement, j)==_queryName)
                        {
                            // beneficiaryUserQueryPack += [conPeek(sourceElement, 1), conPeek(sourceElement, 2)];
                            beneficiaryUserQueryPack = conins(beneficiaryUserQueryPack,
                                                  conLen(beneficiaryUserQueryPack)+1,
                                                  [queryNameMAX, conPeek(sourceElement, 2)]);

                            classfactory.lastValuePut(beneficiaryUserQueryPack,
                                                      this.lastValueDataAreaId(),
                                                      _saveToUserID,
                                                      this.lastValueType(),
                                                      this.lastValueElementName(),
                                                      this.lastValueDesignName());
                            // info(strfmt('The Filter/Query [%1] is saved to user [%2]', queryNameMAX, _saveToUserID));
                            return true;
                        }
                    }
                }
            }
        }
    }
    //-----------------------------------------------------------------------------------------------

    return false;

}

//---[EOM: saveQueryToUser ]-----------------------------------------------------------------------------

..

//-------------------------------------------------------------------------------------------------------
//
//  Form:           SysQueryForm
//
//  Method:         querySave
//
//  Parameters:     LabelType _name
//                  boolean _updateDropDownBox
//
//  Retruns:        void
//
//  Description:    This method is extended to allow the user to save a copy of the
//                  filter/query to the other selected user.
//
//  Related Method: SysQueryForm.saveQueryToUser, it saves the copy of filter/query to other users
//
//  Calling :       SysQueryForm.saveQueryToUser
//
//-------------------------------------------------------------------------------------------------------
// Extened Author - skf@axaptian - 28JAN2010
//-------------------------------------------------------------------------------------------------------
public void querySave(LabelType _name = '', boolean _updateDropDownBox = true)
{
    Dialog          saveDialog;
    DialogField     dialogField;
    boolean         validate;
    Name            name;

    // --------------------------------------------------------------
    // variables applies to saveQueryToUser skf@28JAN2010
    // --------------------------------------------------------------
        DialogField     dialogField_userid;
        UserID          dialogField_userid_value;
    // --------------------------------------------------------------

    if (_name)
    {
        validate = false;
        name     = _name;
    }
    else
    {
        validate   = true;
        saveDialog = new Dialog();
        saveDialog.caption("@SYS26193");
        dialogField = saveDialog.addField(typeid(Name));

        // --------------------------------------------------------------
        // applies to saveQueryToUser skf@28JAN2010
        // --------------------------------------------------------------
            dialogField_userid = saveDialog.addField(typeid(UserID), "Copy To User: ");
        // --------------------------------------------------------------

        if (!saveDialog.run())
        {
            return;
        }
        name = dialogField.value();

        // --------------------------------------------------------------
        // applies to saveQueryToUser skf@28JAN2010
        // --------------------------------------------------------------
            dialogField_userid_value=dialogField_userid.value();
        // --------------------------------------------------------------
    }

    if (name)
    {
        sysQueryForm.parmPrintRanges(printRangeBox.value());
        sysQueryForm.parmPrintGrandTotal(printGrandTotalBox.value());
        sysQueryForm.parmPrintOnlyTotals(printOnlyTotalsBox.value());
        sysQueryForm.parmPrintRemoveRepeatedHeaders(printRepeatedHeadersBox.value());
        sysQueryForm.parmPrintRemoveRepeatedFooters(printRepeatedTotalsBox.value());

        sysQueryForm.queryBuild(range, sorting, companyRanges);
        sysQueryForm.querySave(name, validate);

        if (_updateDropDownBox)
        {
            sysQueryForm.buildSavedQueriesBox(savedQueriesBox);
            savedQueriesBox.selectText(name);
            this.queryLoad(name);
        }

        // --------------------------------------------------------------
        // applies to saveQueryToUser skf@28JAN2010
        // --------------------------------------------------------------
        if (dialogField_userid_value && dialogField_userid_value!=curUserID())
            this.saveQueryToUser(name, dialogField_userid_value);
        // --------------------------------------------------------------

    }
}

//---[EOM: querySave ]-----------------------------------------------------------------------------

..