Easy Extended AX Table-Browser

2010-02-14T11:58:00.000-08:00

Easy Extended AX Table-Browser

During implementations and regular developments in AX, I observed that many consultants/developers open the tables from AOT in the Table-Browser and by hand attempt to add/delete the records by hitting Ctrl+N/Alt+F9 keys and often copies the records from the table-browser and paste them to excel for checking/saving/printing/... the table data. The distress of manually copying/pasting of records and recalling the control-keys can be avoided by simply extending the Table-Browser by adding the Standard data Toolbar to the form

Step-1: Go to the AOT and select \Forms\SysTableBrowser\Designs\Design
and then change the design property WindowType value from “Popup” to “Standard”

Fig1: Changing WindowType property value to “Standard”

Step-2: Save and compile the SysTableBrowser Form after changing the WindowType property value

Fig2: The original Table-Browser (WindowType property is “Popup”)

Fig3: Easy Extened Table-Browser with Standard-Toolbar (WindowType property is “Standard”)

That’s it, enjoy the easy extended table-browser with the standard toolbar with all table/record action features

Enjoy…AXAPTIAN
http://axaptian.blogspot.com/

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

2010-01-31T01:08:00.000-08:00

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 ]-----------------------------------------------------------------------------

AXAPTIAN - Microsoft Dynamics AX Blog

Easy Extended AX Table-Browser

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