Wednesday, January 30, 2019

View computed column to get OR for two flags

Nothing new, just to reiterate the basics.

Say, we need to add a computed column as a result of OR function over two other columns of Boolean type: wblPPOFirmDisableForSalesLine and wblPPOFirmDisableForTransferLine.



Create a new field wblPPOFirmDisable as Enum Computed field


and point it to the following method.



private static server str wblPPOFirmDisable(int _tableNum)
{
    DictView    ctView      = new DictView(tableNum(wblPeggedRefTransOnHoldUnionView));
    str         sTransType1 = SysComputedColumn::comparisonField(identifierStr(wblPeggedRefTransOnHoldUnionView), identifierStr(wblSalesTableOnHoldView),  fieldStr(wblSalesTableOnHoldView, wblPPOFirmDisableForSalesLine));
    str         sTransType2 = SysComputedColumn::comparisonField(identifierStr(wblPeggedRefTransOnHoldUnionView), identifierStr(wblSalesTableOnHoldView),  fieldStr(wblSalesTableOnHoldView, wblPPOFirmDisableForTransferLine));
    str     sRet        = '';

    sRet =
            SysComputedColumn::or2
            (
                SysComputedColumn::equalExpression(sTransType1, SysComputedColumn::returnLiteral(NoYes::Yes)),
                SysComputedColumn::equalExpression(sTransType2, SysComputedColumn::returnLiteral(NoYes::Yes))
            );
    sRet =
        SysComputedColumn::if(sRet, SysComputedColumn::returnLiteral(NoYes::Yes), SysComputedColumn::returnLiteral(NoYes::No));

    return sRet;
}

The whole point is to create a string with an SQL statement as a result in static method. There are a lot of standard functions in SysComputedColumn class aiming to help with syntax.

As you can see in this example it is not enough to get a logical comparison result at the first sRet assigning. The latter must be converted explicitly to an SQL string as it goes at the second step.

Finally we get the following string as a part of the whole SQL statement.

CASE WHEN (T1.WBLPPOFIRMDISABLEFORSALESLINE = 1) OR (T1.WBLPPOFIRMDISABLEFORTRANSFERLINE = 1) THEN 1 ELSE 0 END

Wednesday, January 23, 2019

bugs in SysLookupMultiSelectGrid class and form

You can find a lot of example about how to support multiple selection lookups. AX 2012/D365 provide us with SysLookupMultiSelectGrid class and form to implement such scenarios.

There are two bugs however still existing in the standard code.

SysLookupMultiSelectGrid class, method lookup() must be as follows in order to refresh Query in case QueryRun is given.


// the standard method does not update the query in case if queryrun is given
public static void wblLookup(Query _query, FormStringControl _ctrlIds, FormStringControl _ctrlStrs, container _selectField, queryRun _queryRun = null)
{
    SysLookupMultiSelectGrid    lookupMS = new SysLookupMultiSelectGrid();

    lookupMS.parmCallingControlId(_ctrlIds);
    lookupMS.parmCallingControlStr(_ctrlStrs);
    lookupMS.parmQuery(_query);
    lookupMS.parmQueryRun(_queryRun);
    if(_queryRun)
    // Begin: Alexey Voytsekhovskiy
    {
        lookupMS.parmQuery(_queryRun.query());
    }
    // End: Alexey Voytsekhovskiy
    lookupMS.parmSelectField(_selectField);
    lookupMS.run();
}

SysLookupMultiSelectGrid form, method executeQuery() on common data source must be as follows in order not to consider referenced data sources, which may come with a given query.


public void executeQuery()
{
    QueryRun qr;
    Query lookupMultiSelectQueryCopy = new Query(lookupMS.parmQuery());
    FormRun formRun;
    FormDataSource formDataSource;
    int dsCount, i = 1;
    Common formDataSourceCursor, queryRunCursor;

    // Always use the query defined on the SysLookupMultiSelectGrid class. Note that a copy is used
    // so that any modifications made to the Query by the Form at runtime aren't fed back through
    // the next time the lookup is presented to the user. (The Query is used to define which fields
    // are present on the Form during lookup construction. Therefore, if any fields are added at runtime
    // by the Forms engine, duplicated or non-original-Query defined fields may be presented on the
    // 2nd or later presentations of the lookup if a copy isn't used.)
    this.query(lookupMultiSelectQueryCopy);

    // check if user has set any queryrun. If yes, that means the cursors are set by user explicitly,
    // usually the case where query contains tmp tables and needs to be populated.
    qr = lookupMS.parmQueryRun();
    if(qr)
    {
        formRun = this.formRun();
        dsCount = formRun.dataSourceCount();

        // get data source from query run, get the cursor and set it on the form data source cursor.
        for(i = 1; i<=dsCount; i++)
        {
            formDataSource = formRun.dataSource(i);
            if(formDataSource
                            // Begin: Alexey Voytsekhovskiy
                            // we don't need a reference data source here!
                            && !formDataSource.isReferenceDataSource()
                            // End: Alexey Voytsekhovskiy
                            )
            {
                // get form data source cursor and set the queryrun cursor as data on it.
                formDataSourceCursor = formDataSource.cursor();
                queryRunCursor = qr.get(formDataSourceCursor.TableId);
                if(queryRunCursor)
                {
                    if(queryRunCursor.isTempDb() || queryRunCursor.isTmp())
                    {
                        formDataSourceCursor.setTmpData(queryRunCursor);
                    }
                    else
                    {
                        formDataSourceCursor.data(queryRunCursor);
                    }

                }
            }
        }
    }

    super();
}

Saturday, January 12, 2019

Date time range

Just for my records

 (dayRange(-5, 0))

 Taken from here

Thank you Martin!

Friday, October 19, 2018

Get table name from its id


static void wblTableName(Args _args)
{
    tableId tableId = 175; //1962; //2954; //101377;

    info(strFmt("%1 : %2", tableId2name(tableId), tableId2pname(tableId)));
}

Wednesday, October 17, 2018

How to get all related table ids from code

We can loop all relations on a table by code.


static void wzhGetRelatedTableNames(Args _args)
{
    wblInExtCodeValueTable          wblInExtCodeValueTable;
    int                             mapId;
    TableName                       relatedTableName;
    TableId                         relatedTableId;
    Set                             tablesIdsSet    = new Set(Types::Integer);
    Set                             tablesNamesSet  = new Set(Types::String);
    TableId                         tableId         = tableName2id(tableStr(wblInExtCodeValueTable));
    Dictionary                      dictionary      = new Dictionary();
    SysDictTable                    dictTable       = dictionary.tableObject(tableId);
    DictRelation                    dictRelation    = new DictRelation(wblInExtCodeValueTable.TableId);
    int                             mapCnt          = dictTable.relationCnt();
    container                       ret ;            
    str                             relationName;
    //create a maps of literals for all tables from the table relations
    // so that we could get tables names based on their ids
    // and if any new relation will be added to multiple external codes table
    // it is present automatically in this view
    for (mapId=1; mapId <= mapCnt; mapId++)
    {
        // elaborate if any table present many times
        relationName        = dictTable.relation(mapId);
        dictRelation.loadNameRelation(relationName);
        if(dictRelation)
        {
            relatedTableId      = dictRelation.externTable();
            relatedTableName    = tableId2pname(relatedTableId);
            tablesIdsSet.add(relatedTableId);
            tablesNamesSet.add(relatedTableName);
            info(strFmt("Table %1 - %2", relatedTableId, relatedTableName));
        }
    }
        
    ret = [tablesIdsSet.pack(), tablesNamesSet.pack()];
}



It can be useful in cases when we need, say, to open a form with a related record.


Here you can find a more elaborated example.

Wednesday, September 12, 2018

Lookup and Modified methods for FormReferenceGroup fields in D365

Let's say we need to keep in Purchase line a manufacturer code for a particular product. So each Product/Manufacturer code combination is unique.



Three tables are referenced via RecId fields.



So once Manufacturer code field is placed in the form, we end up with a FormReferenceGroup.



We can easily override its lookup method by subscribing to the relevant event on it.

 [FormControlEventHandler(formControlStr(PurchTable, avrEcoResManufacturerProduct_avrEcoResManufacturerProductRecId), FormControlEventType::Lookup)]
    public static void avrEcoResManufacturerProduct_avrEcoResManufacturerProductRecId_OnLookup(FormControl sender, FormControlEventArgs e)
    {
        PurchLine                           purchLine = sender.formRun().dataSource(formDataSourceStr(PurchTable, PurchLine)).cursor() as PurchLine;
        FormControlCancelableSuperEventArgs cancelableArgs = e as FormControlCancelableSuperEventArgs;
        
        avrEcoResManufacturerProduct::lookupByItem(sender, purchLine.itemId);
        
        cancelableArgs.CancelSuperCall();
    }

public client static Common lookupByItem(FormReferenceControl _formReferenceControl, ItemId _itemId)
    {
        SysReferenceTableLookup sysReferenceTableLookup;
        Query                   query;
        QueryBuildDataSource    avrEcoResMan;

        sysReferenceTableLookup = SysReferenceTableLookup::newParameters(tableNum(avrEcoResManufacturerProduct), _formReferenceControl);
        sysReferenceTableLookup.addLookupfield(fieldNum(avrEcoResManufacturerProduct, EcoResManufacturerRecId));
        sysReferenceTableLookup.addLookupfield(fieldNum(avrEcoResManufacturerProduct, EcoResManufacturerPartNbr));

        query = new Query();
        avrEcoResMan = query.addDataSource(tableNum(avrEcoResManufacturerProduct));
        avrEcoResMan.addRange(fieldNum(avrEcoResManufacturerProduct, EcoResProductRecId)).value(SysQuery::value(InventTable::find(_itemId).Product));

        sysReferenceTableLookup.parmQuery(query);

        return sysReferenceTableLookup.performFormLookup() as avrEcoResManufacturerProduct;
    }




But what if the user wants to create new values in appropriate tables if them do not exist yet?



We can catch the modified event in order to create new values before failing the validation.
However, given that its content may be changed, it is impossible to get access to its fields at design time.

We can do it during run-time by means of getting sought field form controls by their names and overloading then their Modified() methods. (see the similar trick for AX 2012 https://alexvoy.blogspot.com/2014/01/how-to-set-properties-for-reference.html)



The code you need to add.

[ExtensionOf(formStr(PurchTable))]
final class avrPurchTableForm_PurchTableManuf_Extension
{
    private const  str          avrFieldNameDisplayProductNumber    = 'EcoResManufacturerPartNbr';
    private const  str          avrFieldNameEcoResManufacturerName  = 'EcoResManufacturerName';
    private FormStringControl   avrFSCDisplayProductNumber;
    private FormStringControl   avrFSCEcoResManufacturerName;
    
    // the only way to change the standard modified method for a control inside of a dynamically populated reference group
    // is to get it by its name looping all form controls of this group during run-time. then to overload it
    [FormEventHandler(formStr(PurchTable), FormEventType::Initialized)]
    public void PurchTable_OnInitialized(xFormRun sender, FormEventArgs e)
    {
        FormDesign                      formDesign = sender.design();
        FormReferenceGroupControl       formReferenceGroupControl;
        formReferenceGroupControl = formDesign.controlName(formControlStr(PurchTable, avrEcoResManufacturerProduct_avrEcoResManufacturerProductRecId)) as formReferenceGroupControl;
        this.registerManufacturerNameOverload(formReferenceGroupControl);
    }

    private void registerManufacturerNameOverload(FormReferenceGroupControl _formReferenceGroupControl )
    {
        int                                 i;
        Object                              childControl;
        FormStringControl                   formStringControl;

        for (i = 1; i <= _formReferenceGroupControl.controlCount(); i++) // FilterCategory is of FormReferenceGroupControl type
        {
            childControl = _formReferenceGroupControl.controlNum( i );
            formStringControl = childControl as formStringControl;
            if(formStringControl.DataFieldName() == avrFieldNameEcoResManufacturerName)
            {
                avrFSCEcoResManufacturerName = formStringControl;
                formStringControl.registerOverrideMethod(methodStr(formStringControl, modified), formMethodStr(PurchTable, avrEcoResManufacturerName_modified_overload),
                    this);
            }
            if(formStringControl.DataFieldName() == avrFieldNameDisplayProductNumber)
            {
                avrFSCDisplayProductNumber = formStringControl;
                formStringControl.registerOverrideMethod(methodStr(formStringControl, modified), formMethodStr(PurchTable, avrDisplayProductNumber_modified_overload),
                this);
            }
        }
    }

    /// <summary>
    /// We have to allow the user to insert any value, even though such a value is not found in the referenced table;
    /// then we will ask whether this new value must be created in the table and set this new value for the current record
    /// </summary>
    /// <param name = "_sender">EcoResManufacturerName</param>
    public void avrEcoResManufacturerName_modified_overload(FormStringControl _sender)
    {
        avrEcoResManufacturer avrEcoResManufacturer = avrEcoResManufacturer::findOrCreateByName(_sender.text());
        _sender.modified();
    }

    /// <summary>
    /// We have to allow the user to insert any value, even though such a value is not found in the referenced table;
    /// then we will ask whether this new value must be created in the table and set this new value for the current record
    /// </summary>
    /// <param name = "_sender">EcoResManufacturerName</param>
    public void avrDisplayProductNumber_modified_overload(FormStringControl _sender)
    {
        Common          comm = _sender.dataSourceObject().cursor();
        PurchLine       purchLine   =  _sender.parentControl().dataSourceObject().cursor() as PurchLine;

        avrEcoResManufacturerProduct avrEcoResManufacturerProduct = avrEcoResManufacturerProduct::findOrCreateEcoResManufacturerProduct(
                                                                                                        purchLine.itemId, 
                                                                                                        avrFSCEcoResManufacturerName.text(),
                                                                                                        _sender.text());
        _sender.modified();
    }

}

Table methods

public static avrEcoResManufacturer findOrCreateByName(avrEcoResManufacturerName       _manufacturerName)
    {
        avrEcoResManufacturer           avrEcoResManufacturer = avrEcoResManufacturer::findByName(_manufacturerName);
        if(!avrEcoResManufacturer)
        {
            if(Box::confirm("Do you want to creare new manufacturer?", strFmt(avrEcoResManufacturer::txtNotExist(), _manufacturerName)))
            {
                try
                {
                    avrEcoResManufacturer.EcoResManufacturerName = _manufacturerName;
                    if(avrEcoResManufacturer.validateWrite())
                    {
                        avrEcoResManufacturer.insert();
                    }
                }
                catch
                {
                    Error("Failed to create new manufacturer");
                }
            }
        }
        return avrEcoResManufacturer;
    }


public static avrEcoResManufacturerProduct findOrCreateEcoResManufacturerProduct(ItemId                         _itemId,
                                                                                    avrEcoResManufacturerName       _manufacturerName,
                                                                                    avrEcoResManufacturerPartNbr    _manufacturerPartNbr)
    {
        avrEcoResManufacturerProduct    avrEcoResManufacturerProduct;
        avrEcoResManufacturer           avrEcoResManufacturer;
        InventTable                     inventTable = InventTable::find(_itemId);
        // item and part number are given and exist
        if(inventTable.Product && _manufacturerPartNbr)
        {
            // such a manufacturer exists, so just try to find it for given combination
            avrEcoResManufacturer           = avrEcoResManufacturer::findOrCreateByName(_manufacturerName);
            avrEcoResManufacturerProduct    = avrEcoResManufacturerProduct::find(inventTable.Product, avrEcoResManufacturer.RecId);
            if(!avrEcoResManufacturerProduct)
            {
                if(Box::confirm("Do you want to create new part number", strFmt(avrEcoResManufacturerProduct::txtNotExist(), _manufacturerPartNbr)))
                {
                    try
                    {
                        avrEcoResManufacturerProduct.EcoResProductRecId         = inventTable.Product;
                        avrEcoResManufacturerProduct.EcoResManufacturerRecId    = avrEcoResManufacturer.RecId;
                        avrEcoResManufacturerProduct.EcoResManufacturerPartNbr  = _manufacturerPartNbr;
                        avrEcoResManufacturerProduct.EcoResManufacturerDefault  = NoYes::Yes;
                        if(avrEcoResManufacturerProduct.validateWrite())
                        {
                            avrEcoResManufacturerProduct.insert();
                        }
                    }
                    catch
                    {
                        Error("Failed to create new part number");
                    }
                }
            }
        }
        return avrEcoResManufacturerProduct;
    }


Friday, August 17, 2018

TECH SALARIES IN TORONTO, MONTREAL, VANCOUVER REVEALED (2018)

 have published a short article on IT salaries in Canadian cities based on Randstad report; it is worth reading.

For example, about Montreal