Hesham El Gabarty: 2020

Wednesday 30 December 2020

SQL Server T-SQL check the query execution status

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time

FROM sys.dm_exec_requests

Tuesday 29 December 2020

Ax365 / 2012 create an edit value with a drop down for Financial dimension

//Set the dimesnion

public static RecId setDimension(RecId _recID,str _DimName,str _DimValue)

{

DimensionAttributeValue dimAttrValue;

DimensionAttribute dimAttr;

DimensionAttributeValueSetStorage davss;

RecId defaultDimension;

davss = DimensionAttributeValueSetStorage::find(_recID);

dimAttr = DimensionAttribute::findByName(_DimName);

dimAttrValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimAttr, _DimValue, false, true);

if(dimAttrValue)

{

davss.addItem(dimAttrValue);

defaultDimension = davss.save();

}

return defaultDimension;

}

//edit method

[SysClientCacheDataMethodAttribute(true)]

static edit DimensionValue DepartmentMethod(InventJournalTrans _InventJournalTrans,boolean _set,DimensionValue _DimensionValue)

{

DimensionAttribute dimensionAttribute;

DimensionAttributeValueSetItem valueSetItem;

DimensionAttributeValue dimAttrValue;

DimensionAttribute dimAttr;

DimensionFinancialTag _DimensionFinancialTag;

DimensionAttributeDirCategory dimAttributeDirCategory;

DimensionEntryControl _DimensionEntryControl;

dimensionAttribute = DimensionAttribute::findByName("Department");

if(_set)

{

_InventJournalTrans.DefaultDimension = InventJournalMovement_Extension::setDimension(_InventJournalTrans.DefaultDimension,"Department",_DimensionValue);

}

select * from valueSetItem

where valueSetItem.DimensionAttributeValueSet == _InventJournalTrans.DefaultDimension

join DimensionAttribute, EntityInstance, RecId, HashKey, IsSuspended, ActiveFrom, ActiveTo from dimAttrValue

where dimAttrValue.RecId == valueSetItem.DimensionAttributeValue &&

dimAttrValue.IsDeleted == false

&& dimAttrValue.DimensionAttribute==dimensionAttribute.RecId

join * from dimAttr

where dimAttr.RecId == dimAttrValue.DimensionAttribute

;

return valueSetItem.DisplayValue;

}

//Lookup

[FormControlEventHandler(formControlStr(InventJournalCount, DepartmentMethod), FormControlEventType::Lookup)]

public static void DepartmentMethod_OnLookup(FormControl sender, FormControlEventArgs e)

{

DimensionAttribute dimensionAttribute;

DimensionAttributeDirCategory dimAttributeDirCategory;

Query query = new Query();

SysTableLookup sysTableLookup;

dimensionAttribute = DimensionAttribute::findByName("Department");//Department//Purpose

if (dimensionAttribute.Type == DimensionAttributeType::CustomList)

{

select firstonly DirCategory from dimAttributeDirCategory where dimAttributeDirCategory.DimensionAttribute == dimensionAttribute.RecId;

sysTableLookup = SysTableLookup::newParameters(tableNum(DimensionFinancialTag), sender);

// Add name field to be shown in the lookup form.

sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, Value));

sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, Description));

//sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, RecId));

query = new Query();

query.addDataSource(tableNum(DimensionFinancialTag)).addRange(fieldNum(DimensionFinancialTag, FinancialTagCategory)).value(queryValue(dimAttributeDirCategory.DirCategory));

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

}

Monday 24 August 2020

Business Central 365 get max value AL/CAL

MyTable.RESET;

MyTable.SETCURRENTKEY(Field1,Field2,Field3);
// If Required
MyTable.SETRANGE(Field1,Field1);
MyTable.SETRANGE(Field2,Field2);
IF MyTable.FIND('+') then
message('This is the max. value of Field3: ',MyTable.Field3);

Wednesday 1 July 2020

AX 365 Event handlers standard form

Get control value

var docuRef_Notes = formRun.design().controlName(formControlStr(DocuView, docuRef_Notes)) as FormStringControl;

1. Form dataSource event handler: Here I will be using the vendTable form to get table buffer and perform additional business logic or validation.
Vendtable = Form name
vendTable = form data source name
[FormDataSourceEventHandler(formDataSourceStr(Vendtable, vendTable), FormDataSourceEventType::Written)]
public static void vendTable_OnWritten(FormDataSource sender, FormDataSourceEventArgs e)
{
    FormRun                 form           = sender.formRun();
    FormDataSource          vendTable_ds =       form.dataSource(formDataSourceStr(Vendtable,Vendtable)) as FormDataSource;
   Vendtable     Vendtable= vendTable_ds.cursor();
<Now to got table buffer, perform business logic/validation>
}
lets see one more example below;

2. Form DataSource while closing the form (same will apply for OnInitialized,
[FormEventHandler(formStr(EcoResAttributeValue), FormEventType::Closing)]
public static void EcoResAttributeValue_OnClosing(xFormRun sender, FormEventArgs e)
{
     FormDataSource ecoResProduct_ds   =          sender.dataSource(formDataSourceStr(EcoResAttributeValue, EcoResProductAttributeValue));
      EcoResProductAttributeValue      ecoResAttributeValue = ecoResProduct_ds.cursor();
<YOUR CODE>
}   

3.  Post-event hander of Form init or any other standard method method
[[PostHandlerFor(formStr(Ledger), formMethodStr(Ledger, init))]
    public static void Ledger_Post_init(XppPrePostArgs _args)
    {
        #ISOCountryRegionCodes
        FormRun form = _args.getThis();
        FormDesign design = form.design();
        FormControl revaluationAccount = design.controlName(formControlStr(Ledger, revaluationAccount));
        FormControl currencyRevaluation = design.controlName(formControlStr(Ledger, currencyRevaluation));
<your code>
}

4. Form control event hander:
[FormControlEventHandler(formControlStr(LogisticsPostalAddress, Roles), FormControlEventType::Modified)]
    public static void Roles_OnModified(FormControl sender, FormControlEventArgs e)
    {
        FormRun element = sender.formRun();
        FormControl purposeCtrl   = element.design().controlName(formControlStr(LogisticsPostalAddress, Roles));
       
        FormDataSource logisticsLocation_DS = element.dataSource(formDataSourceStr(LogisticsPostalAddress, LogisticsLocation));
       <business logic>
    }

Wednesday 27 May 2020

AX365 Retail Show a dialog

This is my first example to create a dialog box
==============================================
/////////////////////////////Hesham code added///////////////////////////////////////
// window.open('https://javascript.info/')

let yesButton: ClientEntities.Dialogs.IDialogResultButton = {

id: "YES_ID",
label: "Yes", // "Yes"

result: "Yes"
};

let noButton: ClientEntities.Dialogs.IDialogResultButton = {
id: "NO_ID",
label: "No", // "No"
result: "No"
};

let showMessageDialogClientRequestOptions: ClientEntities.Dialogs.IMessageDialogOptions = {
title: "My Title",
subTitle: "my Subtitle",
message: "The message ?",
button1: yesButton,
button2: noButton

};

let showMessageDialogClientRequest: ShowMessageDialogClientRequest<ShowMessageDialogClientResponse> =
new ShowMessageDialogClientRequest(showMessageDialogClientRequestOptions);
this.context.runtime.executeAsync<ShowMessageDialogClientResponse>(showMessageDialogClientRequest);
//////////////////////////////////////////////////

Monday 11 May 2020

Gel the ledger dimension from x++ or SQL

select dimensionAttributeValueCombination.RecId,
DimensionAttribute.Name,
DimensionAttributeLevelValue.DisplayValue
from dimensionAttributeValueCombination

inner join dimensionAttributeValueGroupCombination
on dimensionAttributeValueGroupCombination.DimensionAttributeValueCombination =
dimensionAttributeValueCombination.RecId
inner join dimensionAttributeValueGroup
on dimensionAttributeValueGroup.RecId = dimensionAttributeValueGroupCombination.DimensionAttributeValueGroup
inner join dimensionAttributeLevelValue
on dimensionAttributeLevelValue.DimensionAttributeValueGroup = dimensionAttributeValueGroup.RecId
inner join dimensionAttributeValue
on dimensionAttributeValue.RecId = dimensionAttributeLevelValue.DimensionAttributeValue
inner join dimensionAttribute
on dimensionAttribute.RecId = dimensionAttributeValue.DimensionAttribute
-- and DimensionAttribute.Name = "Project"

-- where dimensionAttributeValueCombination.RecId = 5637174256

======================== X++====================

DimensionDefault                        d = 5637169331;
    DimensionAttributeValueSet dimAttrValueSet;
    DimensionAttributeValueSetItem dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute          dimAttr;
    Common      dimensionValueEntity;
    DimensionValue dimensionValue;
    DimensionAliasName dimensionName;

    dimAttrValueSet = DimensionAttributeValueSet::find(D);
    while select dimAttrValueSetItem
        where dimAttrValueSetItem.DimensionAttributeValueSet == dimAttrValueSet.RecId
    {
        dimAttrValue = DimensionAttributeValue::find(dimAttrValueSetItem.DimensionAttributeValue);

        dimAttr = DimensionAttribute::find(DimAttrValue.DimensionAttribute);
        dimensionvalueentity = DimensionDefaultingControllerBase::findBackingEntityInstance(
        curext(),DimAttr,dimAttrvalue.EntityInstance);
        dimensionvalue = dimattrvalue.getValue();
        info(strFmt("%1 %2",Dimensionvalue, DimAttr.Name));
    }

Wednesday 4 March 2020

AX 2012 x++ set the default financial dimension

DimensionAttributeValue         dimAttrValue;
DimensionAttribute              dimAttr;
DimensionAttributeValueSetStorage davss;
RecId                defaultDimension;

davss = DimensionAttributeValueSetStorage::find(this.DefaultDimension);

dimAttr = DimensionAttribute::findByName(‘MyDimensionName’);
dimAttrValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimAttr, “DimensionValue”, false, true);
if(dimAttrValue)
{
davss.addItem(dimAttrValue);
this.DefaultDimension = davss.save();
}

In case we need to set the Ledger Dimension

LedgerDimensionFacade::serviceCreateLedgerDimension(LedgerDefaultAccountHelper::getDefaultAccountFromMainAccountRecId(MainAccount::findByMainAccountId(_mainAccount).RecId),_FinDim);

Tuesday 18 February 2020

AX 2012 number sequence by code

NumberSequenceTable numt;
NumberSequenceReference numR;
NumberSeq num;
EMCClinicParameters _EMCClinicParameters;
EEMC_ProgRegId _EEMC_ProgRegId;
select firstOnly _EMCClinicParameters;
if (_EMCClinicParameters.CustProgramSequnce !="")
{

num = NumberSeq::newGetNumFromCode(_EMCClinicParameters.CustProgramSequnce,NumberSeqScopeFactory::createDefaultScope(), false, false);

this.ProgRegId = num.num();

}

/////////////////////////////////////
///////////////////////// Create the number sequence as type of comapny

Tuesday 4 February 2020

AX 2012 X++ create Transfer journal

CommaTextIO csvFile;
container readCon;
Dialog dialog;
DialogField dfFileName;

int insertCount,updateCount;
str 200 DistributionId;
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
InventJournalTrans _InventJournalTrans;
InventDim _InventDim;
WMSJournalTrans _WMSJournalTrans;
InventLocation _InventLocation,_InventLocationTo;
InventLocationId _InventLocationId,_InventLocationIdTo;
InventSerialId _InventSerialId;
NumberSeq numberSeq;
NumberSequenceReference numberSequenceReference;

int ProgramDay;
#define.JournalID(1)
#define.TransDate(2)
#define.ItemID(3)
#define.Qty(4)
#define.Serial(5)
#define.LocationFrom(6)
#define.LocationTo(7)
super();

#File
dialog = new Dialog("Pick the file");
dfFileName = dialog.addField(extendedTypeStr("FilenameOpen"));
dialog.filenameLookupFilter(["All files", #AllFiles]);

if (dialog.run())
{

csvFile = new CommaTextIo(dfFileName.value(), 'r');
csvFile.inFieldDelimiter(',');
readCon = csvFile.read();

while(csvFile.status() == IO_Status::OK)
{
readCon = csvFile.read();

if (readCon)
{
ttsBegin;
_InventJournalTrans.clear();
// numberSeq = new NumberSeq();
// numberSequenceReference = InventParameters::numRefInventJournalVoucherId();
// numberseq = NumberSeq::newGetNum(numberSequenceReference);

_InventJournalTrans.initFromInventJournalTable(InventJournalTable::find(_InventJournalTrans.JournalId));
// _InventJournalTrans.Voucher = numberseq.num();
_InventJournalTrans.JournalType = InventJournalType::Transfer;
_InventJournalTrans.JournalId= conPeek(readCon,#JournalID); //cells.item(row, 1).value().bStr();

_InventJournalTrans.transdate= str2Date(conPeek(readCon,#TransDate),123); //cells.item(row, 2).value().date();
_InventJournalTrans.itemId= conPeek(readCon,#ItemID); //cells.item(row,3).value().bStr();
_InventJournalTrans.qty= str2num(conPeek(readCon,#Qty)); //cells.item(row,4).value().double()*-1;

_InventLocation = InventLocation::find(conPeek(readCon,#LocationFrom));//InventLocation::find(cells.item(row,6).value().bStr());
_InventLocationTo = InventLocation::find(conPeek(readCon,#LocationTo));//InventLocation::find(cells.item(row,7).value().bStr());

_InventDim.clear();
_InventDim.InventSiteId=_InventLocation.InventSiteId;
_InventDim.InventSerialId = conPeek(readCon,#Serial);//cells.item(row,5).value().bStr();
_InventDim.InventLocationId = _InventLocation.InventLocationId;
_InventJournalTrans.inventDimId=InventDim::findOrCreate(_InventDim).inventDimId;

_InventDim.clear();
_InventDim.InventSiteId=_InventLocationTo.InventSiteId;
_InventDim.InventSerialId = conPeek(readCon,#Serial);//cells.item(row,5).value().bStr();
_InventDim.InventLocationId = _InventLocationTo.InventLocationId;
_InventJournalTrans.ToInventDimId= InventDim::findOrCreate(_InventDim).inventDimId;

_InventJournalTrans.insert();
ttsCommit;

updateCount++;
progress.setCaption("Uploading transfer journal");
progress.setAnimation(#AviUpdate);
progress.setText(strfmt("%1 line uploaded", updateCount));
// progress.setCount(updateCount, 1);

}

}
}

info(strFmt("%1 records inserted ",updateCount));

Tuesday 14 January 2020

AX X++ get the selected grid records

InventQualityOrderTable _InventQualityOrderTable;
_InventQualityOrderTable = InventQualityOrderTable_DS.getFirst(true);
while (_InventQualityOrderTable)
{

info(strFmt('The order %1 is validated', _InventQualityOrderTable.QualityOrderId));

_InventQualityOrderTable = InventQualityOrderTable_DS.getNext();
}

AX 2012 X++ validate quality order by code

InventQualityOrderTable _InventQualityOrderTable;
InventQualityOrderValidate obj ;//= new InventQualityOrderValidate();

obj = InventQualityOrderValidate::construct();
obj.parmAcceptError(NoYes::Yes);
obj.parmQualityOrderId('QO19S000009234');
obj.parmWorker(5637146076);
obj.run();

Monday 6 January 2020

AX X++ cancel and UnPick PickList

void cancelPickingRoute()
{
    WMSPickingRoute         WMSPickingRoute = WMSPickingRoute::findBySalesId("SO024406");
    WMSPickingLineCancel    WMSPickingLineCancel;
    WMSOrderTrans           WMSOrderTrans;
    WMSOrderTransUnPick     WMSOrderTransUnPick;
    ;

    switch (WMSPickingRoute.expeditionStatus)
    {
        case WMSExpeditionStatus::Complete :
            while select forupdate WMSOrderTrans
                index hint OrderIdx
            where WMSOrderTrans.fullPallet == 0
            &&    WMSOrderTrans.routeId    == WMSPickingRoute.pickingRouteID
            {
                ttsbegin;
                WMSOrderTransUnPick::newWMSOrderTrans(WMSOrderTrans).unPick(WMSOrderTrans.qty);
                ttscommit;
            }

            while select WMSOrderTrans
                index hint OrderIdx
            where WMSOrderTrans.fullPallet == 0
            &&    WMSOrderTrans.routeId    == WMSPickingRoute.pickingRouteID
            {