How to add filter on display method in ax 2012

I have implemented the filtering functionality for the ExportInvoiceNum field in the Invoice Journal screen. The field is exposed via a display method and now supports context menu filtering with the following options:

I added a display method to retrieve the ExportInvoiceNum value from the LEC_ExportInvoiceLine table.

In the StringEdit:ExportInvoiceNum control, I implemented a context() method to handle filtering actions via a custom context menu.

The control’s Auto-declaration property has been set to YES, allowing access to the control in code for dynamic filtering.

• Filter By Field: Opens a search dialog where users can input a value to filter ExportInvoiceNum.

• Filter By Selection: Applies a filter based on the currently selected field value.

• Remove Filter: Clears all filters on the dataset.

Tables using in this CustInvoiceJour & LEC_ExportInvoiceLine

Display method added in CustinvoiceJour Table

//BP Deviation Documented

[SysClientCacheDataMethodAttribute(true)]

display GOD_CashCollectionsequence ExportInvoiceNum()

{

    GOD_CashCollectionsequence _exportInvoiceLine;

    _exportInvoiceLine = LEC_ExportInvoiceLine::find(this.InvoiceId).ExportInvoiceNum;

    return _exportInvoiceLine;

}

------------------------------------------------------------------------------------------------------------------

below method is added in CustinvoiceJour form 

public void context()

{

    int                  selectedMenu;

    formrun              fr;

    Args                 ag;

    Name                 strtext;

    querybuilddataSource qb1;

    queryrun             qr;

    query                q;

    PopupMenu            menu = new PopupMenu(element.hWnd());

    int                  a = menu.insertItem('Filter By Field');

    int                  b = menu.insertItem('Filter By Selection');

    int                  c = menu.insertItem('Remove Filter');

    ;

    selectedMenu = menu.draw();

    switch (selectedMenu)

        {

        case -1: //Filter by field

                break;

        case a:

                ag = new args('SysformSearch');

                fr = new formrun(ag);

                fr.run();

                fr.wait();

                //Reading User entered value for filter process

                strtext = fr.design().controlName('FindEdit').valueStr();

                if(strtext)

                {

                //Creating a query for filter

                q   = CustInvoiceJour_ds.query();

                qb1 = q.dataSourceTable(tablenum(CustInvoiceJour));

                qb1 = qb1.addDataSource(TableNum(LEC_ExportInvoiceLine));

                qb1.addLink(FieldNum(CustInvoiceJour,InvoiceId),FieldNum(LEC_ExportInvoiceLine,InvoiceId));

                qb1.addRange(FieldNum(LEC_ExportInvoiceLine,ExportInvoiceNum)).value(strtext);

                CustInvoiceJour_ds.query(Q);

                CustInvoiceJour_ds.executeQuery();

                }

                break;

        case b:    // Filter By Selection

                q   = CustInvoiceJour_ds.query();

                qb1 = q.dataSourceTable(tablenum(CustInvoiceJour));

                qb1 = qb1.addDataSource(TableNum(LEC_ExportInvoiceLine));

                qb1.addLink(FieldNum(CustInvoiceJour,InvoiceId),FieldNum(LEC_ExportInvoiceLine,InvoiceId));

                qb1.addRange(FieldNum(LEC_ExportInvoiceLine,ExportInvoiceNum)).value(ExportInvoiceNum.valueStr());

                CustInvoiceJour_ds.query(Q);

                CustInvoiceJour_ds.executeQuery();

                break;

        case c :    // Remove Filter

                q   = new Query();

                qb1 = q.addDataSource(tablenum(CustInvoiceJour));

                qb1.clearLinks();

                qb1.clearRanges();

                CustInvoiceJour_ds.query(Q);

                CustInvoiceJour_ds.removeFilter();

                break;

        Default:

                break;

    }

}

Delivery Date Modification sending Email Alerts

 Based on Delivery Date Filed Modified by user will send email to concern department head

using Email template to send email 

FormName - PurchTable

Path -PurchTable /DataSource-PurchTable/DeliveryDate/ModifiedMethod

added the below code in PurchTable-Methods

//</Kavin Sending notification to Supplier>

public static void sendEmailVendor(PurchTable     purchTable)

{  str positionName,finalResult;

    int found = 0;

    int found1 = 0;

    FromDate                        fromDate;

    SecurityRole                    securityRole;

    SecurityUserRole                securityUserRole;

    DirPersonUser                   personUser;

    DataAreaId                      employeeEntity;

    Name                            entityName;

    SysMailer                       mailer;

    UserId                          _sendingUserId;

    str                             mailAppendTo,Person,mailSubject,body,MgrdBody,

                                    sendMailTo,sendMailCc,Employee,BccMailto,

                                    BccMailID,mailServer,s,HRManager,SendFrom;

    int                             mailServerPort;

    int64                           StartWorkerId;

    str                             NoofDays;

    str                             IsAnnual,subject;

    str                             OveralJudgement;

    Query                           query;

    QueryRun                        qr;

    QueryBuildDataSource            qbd1;

    List                            list,listForBcc;

    ListEnumerator                  listEnumerator,listEnumerator1;

    Name FromName = HcmWorker::find(HcmWorker::userId2Worker(curUserId())).name();

    SysMailer mail;

    SysOutgoingEmailTable outgoingEmailTable;

    SysEmailItemId nextEmailItemId;

    System.Net.Mail.SmtpClient          mailClient;

    System.Net.NetworkCredential        credentials;

    System.Net.Mail.MailMessage         mailMessage;

    Email email;

    SysEmailParameters parameters = SysEmailParameters::find();

    str pwd;

    Map map;

    SysEmailTable        sysEmailTable        = SysEmailTable::find('POChanged');

    SysEmailMessageTable sysEmailMessageTable = SysEmailMessageTable::find(sysEmailTable.EmailId, sysEmailTable.DefaultLanguage);

    mailer         = new SysMailer();

    mailServer     = SysEmaiLParameters::find(false).SMTPRelayServerName;

    mailServerPort = SysEmaiLParameters::find(false).SMTPPortNumber;

    SendFrom       = SysEmaiLParameters::find(false).SMTPUserName;

    fromDate       = systemDateGet();

    if(purchTable)

    {

        Person = smmParametersTable::find().GH_EmailCC;

        s = date2Str(today(),213,DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4);

        subject = "Delivery date changed Notification";

        if(SendFrom && Person)

        {

            mailSubject = subject ;

            body =  strFmt(sysEmailMessageTable.Mail,strFmt("%1: %2",purchTable.PurchId,purchTable.PurchName),

            strFmt("%1- %2",purchTable.OrderAccount,VendTable::find(purchTable.OrderAccount).name()),PurchTable.orig().DeliveryDate,PurchTable.DeliveryDate,HcmWorker::find(HcmWorker::userId2Worker(curUserId())).name());

            mailClient = new System.Net.Mail.SmtpClient();

            mailClient.set_DeliveryMethod(System.Net.Mail.SmtpDeliveryMethod::Network);

            mailClient.set_Host(parameters.SMTPRelayServerName);

            mailClient.set_Port(SysEmaiLParameters::find(false).SMTPPortNumber);

            mailClient.set_DeliveryMethod(System.Net.Mail.SmtpDeliveryMethod::Network);

            //mailClient.set_EnableSsl(true);

            pwd=SysEmaiLParameters::password();

            credentials = new System.Net.NetworkCredential(parameters.SMTPUserName, pwd);

            mailClient.set_UseDefaultCredentials(false);

            mailClient.set_Credentials(credentials);

            mailClient.set_Timeout(30000000);

            mailMessage = new System.Net.Mail.MailMessage(SendFrom, Person);

            mailMessage.set_Subject(mailSubject);

            mailMessage.set_Body(body);

            mailMessage.set_IsBodyHtml(true);

            try

            {

                mailClient.Send(mailMessage);

            }

            catch (Exception::CLRError)

            {

                info("Event:" + AifUtil::getClrErrorMessage());

            }

            _sendingUserId = "Administ";

            //Inserting History in Outgoing Email Table here ***

            nextEmailItemId = EventInbox::nextEventId();

            outgoingEmailTable.EmailItemId = nextEmailItemId;

            outgoingEmailTable.IsSystemEmail = NoYes::No;

            outgoingEmailTable.Sender = SendFrom;

            outgoingEmailTable.SenderName = "Administ";

            outgoingEmailTable.Recipient  = Person;

            outgoingEmailTable.Subject    = SysEmailMessage::stringExpand(subject, map);

            outgoingEmailTable.Priority   = eMailPriority::Normal ;

            outgoingEmailTable.WithRetries = false;

            outgoingEmailTable.RetryNum = 0;

            outgoingEmailTable.UserId = _sendingUserId;//curUserId();

            outgoingEmailTable.Status = SysEmailStatus::Sent;

            outgoingEmailTable.Message = body;

            outgoingEmailTable.Origin  = sysEmailTable.Description;

            outgoingEmailTable.LatestStatusChangeDateTime = DateTimeUtil::getSystemDateTime();

            outgoingEmailTable.insert();

        }

      info(strFmt("PO has been changed and  email sent to - %1",Employee));

    }

}

//</Kavin End>

Dear All,

PO Number: %1 delivery date is changed . please have  a check and take the action

Supplier : %2

Old delivery date: %3

Modified delivery date: %4

Modified by: %5

For your reference & information.

Best regards,

Dynamics AX .
This is an automatically generated message. Please do not try to reply to this E-Mail.

CustTable form Button Click to Update customer status

 

CustTable Form Button Click method to Update customer status under the current customer account mapped to child accounts 

void clicked()

{

    CustTable                       custTableLoc;

    CustTable                       custTableCur = CustTable::find(custTable.AccountNum);

    GOD_CustStatusChangeTracking    statusTracking;

    int                             counter;

    super();

        info(strFmt("%1 - %2", custTableCur.GOD_StatusChangeReason, custTableCur.GOD_CustStatus));

    ttsBegin;

    while select forUpdate custTableLoc

        where custTableLoc.GOD_ParentCustomer == custTableCur.AccountNum

           && custTableLoc.InvoiceAccount == custTableCur.AccountNum

            && custTableLoc.AccountNum != custTableCur.AccountNum

    {

        if(custTableLoc.GOD_CustStatus != GOD_CustStatus::None)

        {

            custTableLoc.Blocked = CustVendorBlocked::All;

        }

        else

        {

            custTableLoc.Blocked = CustVendorBlocked::No;

        }

        custTableLoc.GOD_StatusChangeReason = custTableCur.GOD_StatusChangeReason;

        custTableLoc.GOD_CustStatus = custTableCur.GOD_CustStatus;

        custTableLoc.update();

        statusTracking.clear();

        statusTracking.OldCustStatus = custTableLoc.GOD_CustStatus;

        statusTracking.AccountNum = custTableLoc.AccountNum;

        statusTracking.ReasonNotes  = custTableCur.GOD_StatusChangeReason;

        statusTracking.NewCustStatus = custTableLoc.orig().GOD_CustStatus;

        statusTracking.insert();

        counter++;

    }

    ttsCommit;

    info(strFmt("%1 child account's status has been changed", counter));

}

Form Menu Button click method to update field by filtering records only in form

 adding menu button to update the filed in table for marked based on filtered records only  

void clicked()

{

   GOD_CashCollection  god_CashCollectionUpdate;

   super();

  ttsBegin;

    for(god_CashCollectionUpdate = getFirstSelection(GOD_CashCollection_ds);god_CashCollectionUpdate;god_CashCollectionUpdate = GOD_CashCollection_ds.getNext())

    {

       //info( god_CashCollectionUpdate.InvoiceId);

        if(god_CashCollectionUpdate.Delivered)

        {

         god_CashCollectionUpdate.selectForUpdate(true);

         god_CashCollectionUpdate.MissingInvoice = NoYes::No;

         god_CashCollectionUpdate.AccountConfirmed = NoYes::Yes;

         god_CashCollectionUpdate.update();

        }

    }

    ttsCommit;

    GOD_CashCollection_ds.research(true);

}

Vendor Statement Controlling settlement entries in display

 Adding new parameter checkbox to display the entries in Vendor statement 

VendAccountStatementIntContract()

variable declaration 

boolean incSettlement;

boolean incForeignCuyRevaluation;

[

    DataMemberAttribute('incForeignCuyRevaluation'),

    SysOperationLabelAttribute(literalstr("FC Revaluation")),

    SysOperationHelpTextAttribute(literalstr("Include foreignCurrency Revaluationn")),

    SysOperationGroupMemberAttribute('Criteria'),

    SysOperationDisplayOrderAttribute('14')

]

public boolean parmIncForeignCuyRevaluation(boolean _incForeignCuyRevaluation = incForeignCuyRevaluation)

{

    incForeignCuyRevaluation = _incForeignCuyRevaluation;

    return incForeignCuyRevaluation;

}

[

    DataMemberAttribute('IncSettlement'),

    SysOperationLabelAttribute(literalstr("Inc settlement")),

    SysOperationHelpTextAttribute(literalstr("Include settlement transaction")),

    SysOperationGroupMemberAttribute('Criteria'),

    SysOperationDisplayOrderAttribute('13')

]

public boolean parmIncSettlement(boolean _incSettlement = incSettlement)

{

    incSettlement = _incSettlement;

    return incSettlement;

}

Dp class /  VendAccountStatementIntDP / initVendTransQuery

/// <summary>

/// Creates and executes the query.

/// </summary>

/// <returns>

/// A <c>Query</c> object.

/// </returns>

/// <remarks>

/// Adds data source, ranges, and sorting based on the selected parameters.

/// </remarks>

public Query initVendTransQuery()

{

    Query query = this.parmQuery();

    QueryBuildDataSource vendTableDataSource                    = SysQuery::findOrCreateDataSource(query, tableNum(VendTable));

    QueryBuildDataSource vendTransDataSource                    = vendTableDataSource.addDataSource(tableNum(VendTrans));

    QueryBuildDataSource transactionReversalTransDataSource     = vendTransDataSource.addDataSource(tableNum(TransactionReversalTrans));

    QueryBuildDataSource bankLCVendTransDataSource;

    QueryBuildDataSource ds;

    vendTransDataSource.relations(true);

    vendTransDataSource.joinMode(JoinMode::OuterJoin);

    transactionReversalTransDataSource.addLink(fieldNum(VendTrans, RecId), fieldNum(TransactionReversalTrans, RefRecId));

    transactionReversalTransDataSource.addLink(fieldNum(VendTrans, TableId), fieldNum(TransactionReversalTrans, RefTableId));

    transactionReversalTransDataSource.fetchMode(QueryFetchMode::One2One);

    transactionReversalTransDataSource.joinMode(JoinMode::OuterJoin);

    vendTransDataSource.addRange(fieldNum(VendTrans,AccountNum));

    if (fromDate || toDate)

    {

        vendTransDataSource.addRange(fieldNum(VendTrans,TransDate)).value(queryRange(fromDate, toDate));

    }

    if (currencyCode)

    {

        vendTransDataSource.addRange(fieldNum(VendTrans,CurrencyCode)).value(currencyCode);

    }

    //-- added by kavin for control settlement entries and foreign currency revaluation to display as per checkbox control in report 

    if(!includeSettlement && includeForeignCuyRevaluation)

    {

        vendTransDataSource.addRange(fieldNum(VendTrans,TransType)).value(SysQuery::valueNot(LedgerTransType::Settlement));

    }

    if(!includeForeignCuyRevaluation && includeSettlement)

    {

        vendTransDataSource.addRange(fieldNum(VendTrans,TransType)).value(SysQuery::valueNot(LedgerTransType::ExchAdjustment));

    }

    if(!includeForeignCuyRevaluation && !includeSettlement)

    {

        vendTransDataSource.addRange(fieldNum(VendTrans,TransType)).value(queryNotValueCon_W([LedgerTransType::Settlement,LedgerTransType::ExchAdjustment]));

    }

    // --- ended checkbox control in report 

    

    vendTransDataSource.addSortField(fieldNum(VendTrans,TransDate));

    vendTransDataSource.addSortField(fieldNum(VendTrans,Voucher));

    if (!includeReversed)

    {

        ds = vendTransDataSource.addDataSource(tableNum(TransactionReversalTrans));

        ds.joinMode(JoinMode::NoExistsJoin);

        ds.addLink(fieldNum(VendTrans,TableId), fieldNum(TransactionReversalTrans,RefTableId));

        ds.addLink(fieldNum(VendTrans,RecId), fieldNum(TransactionReversalTrans,RefRecId));

        ds.addRange(fieldNum(TransactionReversalTrans,Reversed)).value(enum2str(NoYes::Yes));

    }

    if (BankLCImportFeatureChecker::checkBankLCImportEnabled())

    {

        if (bankLCBankDocumentType != BankLCBankDocumentType::All)

        {

            bankLCVendTransDataSource = vendTransDataSource.addDataSource(tableNum(BankLCVendTrans));

            bankLCVendTransDataSource.joinMode(JoinMode::ExistsJoin);

            bankLCVendTransDataSource.addLink(fieldNum(VendTrans, BankLCImportLine), fieldNum(BankLCVendTrans, BankLCImportLine));

            bankLCVendTransDataSource.addRange(fieldNum(BankLCVendTrans, BankDocumentType)).value(

            SysQuery::value(BankLCCustVendTransHelper::convertDocumentType(bankLCBankDocumentType)));

        }

    }

    return query;

}

Inventory Value Report Adding New Field filter

 Adding new field in InventValueReportTmpLine Table and InventValueReportTmpLine sysQuery 

then add below lines in InventValueReportPopulateItem Class 

protected Map mapReportLineToInventTable()

{

    Map   map;

    map = new map(Types::Integer, Types::Integer);

    map.insert(fieldNum(InventValueReportTmpLine, ResourceId), fieldNum(InventTable, ItemId));

    map.insert(fieldNum(InventValueReportTmpLine, ShipmentMode), fieldNum(InventTable, ShipmentMode));//-- newly added for filtering

    return map;

}

-----------------------------------

protected void buildTransactionsWithDynamicQuery()

q = new Query();

    qbdsInventTable = q.addDataSource(tableNum(InventTable));

    qbdsInventTable.orderMode(OrderMode::GroupBy);

    qbdsInventTable.addGroupByField(fieldNum(InventTable, ItemType));

    qbdsInventTable.addGroupByField(fieldNum(InventTable, ShipmentMode));//-- newly added for filtering

    if (inventValueReport.ViewResourceId || inventValueReport.ViewResourceIdTotal)

    {

        qbdsInventTable.addGroupByField(fieldNum(InventTable, ItemId));

    }

-----------------

protected void buildTransactionsWithDynamicQuery()

{

    Query                               q;

    QueryRun                            qr;

    QueryBuildDataSource                qbds;

    QueryBuildDataSource                qbdsInventTable;

    QueryBuildDataSource                qbdsInventView;

    InventItemGroupItem                 inventItemGroupItem;

    InventTable                         inventTable;

    EcoResProductDimensionGroupProduct  productDimensionGroupProduct;

    EcoResStorageDimensionGroupItem     storageDimensionGroupItem;

    EcoResTrackingDimensionGroupItem    trackingDimensionGroupItem;

    InventValueReportView               inventView;

    InventDim                           inventDim, fetchedInventDim;

    InventValueReportTmpLine            tmpReportLine;

    InventDimParm                       inventDimParmProduct;

    InventDimParm                       inventDimParmStorage;

    InventDimParm                       inventDimParmTracking;

    InventDimParm                       financialInventDimParm;

    InventDimParm                       populateInventDimParm;

    InventDimParm                       inventDimParmFilter;

    boolean                             updateFinancialInventDimParm;

    boolean                             joinInventDim;

    boolean                             joinProductDimensionGroupProduct;

    boolean                             joinStorageDimensionGroupProduct;

    boolean                             joinTrackingDimensionGroupProduct;

    boolean                             joinInventItemGroupItem;

    boolean                             isInventDimParmEqual = true;

    // Group by dimensions (view || total)

    populateInventDimParm = InventDimParm::orParms(inventValueReport.viewInventDimParm(), inventValueReport.totalInventDimParm());

    joinInventDim = this.isRangeSetOnReportLine(this.MapReportLineToInventDim(), inventDimParmFilter) ||

                    populateInventDimParm.anyProductDimensionFlagSet() ||

                    populateInventDimParm.anyStorageDimensionFlagSet() ||

                    populateInventDimParm.anyTrackingDimensionFlagSet();

    joinInventItemGroupItem = inventValueReport.ViewResourceGroup || inventValueReport.ViewResourceGroupTotal;

    joinProductDimensionGroupProduct = populateInventDimParm.anyProductDimensionFlagSet();

    joinStorageDimensionGroupProduct = populateInventDimParm.anyStorageDimensionFlagSet();

    joinTrackingDimensionGroupProduct = populateInventDimParm.anyTrackingDimensionFlagSet();

    q = new Query();

    qbdsInventTable = q.addDataSource(tableNum(InventTable));

    qbdsInventTable.orderMode(OrderMode::GroupBy);

    qbdsInventTable.addGroupByField(fieldNum(InventTable, ItemType));

    qbdsInventTable.addGroupByField(fieldNum(InventTable, ShipmentMode));//-- newly added for filtering

    if (inventValueReport.ViewResourceId || inventValueReport.ViewResourceIdTotal)

    {

        qbdsInventTable.addGroupByField(fieldNum(InventTable, ItemId));

    }

    this.copyRangesFromContractQuery(this.MapReportLineToInventTable(), qbdsInventTable);

    qbds = qbdsInventTable;

    if (joinInventItemGroupItem)

    {

        qbds = this.joinInventItemGroupItem(qbds, qbdsInventTable);

    }

    if (joinProductDimensionGroupProduct)

    {

        qbds = this.joinProductDimensionGroupProduct(qbds, qbdsInventTable);

    }

    if (joinStorageDimensionGroupProduct)

    {

        qbds = this.joinStorageDimensionGroupProduct(qbds, qbdsInventTable);

    }

    if (joinTrackingDimensionGroupProduct)

    {

        qbds = this.joinTrackingDimensionGroupProduct(qbds, qbdsInventTable);

    }

    qbdsInventView = qbds.addDataSource(tableNum(InventValueReportView));

    qbdsInventView.addLink(fieldNum(InventTable, ItemId), fieldNum(InventValueReportView, ItemId), qbdsInventTable.name());

    switch (inventValueReport.SelectDateRange)

    {

        case InventValueReportSelectDateRange::TransDate:

            qbdsInventView.addRange(fieldNum(InventValueReportView, TransDate)).value(queryRange(this.getActualFromDate(), dateMax()));

            break;

        case InventValueReportSelectDateRange::TransBeginTime:

            qbdsInventView.addRange(fieldNum(InventValueReportView, TransBeginTime)).value(queryRange(this.getActualFromDate(), DateTimeUtil::maxValue()));

            break;

    }

    qbdsInventView.orderMode(OrderMode::GroupBy);

    if (this.parmResourceId())

    {

        qbdsInventView.addRange(fieldNum(InventValueReportView, ItemId)).value(queryValue(this.parmResourceId()));

    }

    if (joinInventDim)

    {

        qbds = this.joinInventDim(qbdsInventView, populateInventDimParm);

    }

    // select sum(field)

    qbdsInventView.addSelectionField(fieldNum(InventValueReportView, Qty), SelectionField::Sum);

    qbdsInventView.addSelectionField(fieldNum(InventValueReportView, Amount), SelectionField::Sum);

    // InventView

    qbdsInventView.addGroupByField(fieldNum(InventValueReportView, TransDate));

    if (inventValueReport.DetailLevel == TotalTrans::Trans)

    {

        qbdsInventView.addGroupByField(fieldNum(InventValueReportView, Voucher));

        qbdsInventView.addGroupByField(fieldNum(InventValueReportView, Reference));

    }

    // Group by dates regardless of DetailLevel value, so that the records with TransDate > ToDate could be deleted later, in the run() method

    if (inventValueReportContracts.parmViewTransBeginTime() ||

        inventValueReport.SelectDateRange == InventValueReportSelectDateRange::TransBeginTime)

    {

        qbdsInventView.addGroupByField(fieldNum(InventValueReportView, TransBeginTime));

    }

    qbdsInventView.addGroupByField(fieldNum(InventValueReportView, ReferenceCategory));

    qbdsInventView.addGroupByField(fieldNum(InventValueReportView, InventTransPostingType));

    qbdsInventView.addGroupByField(fieldNum(InventValueReportView, IsPosted));

    if (inventValueReportContracts.parmViewCOGSFinancial() || inventValueReportContracts.parmViewCOGSTotal())

    {

        qbdsInventView.addGroupByField(fieldNum(InventValueReportView, PostingTypeOffset));

    }

    this.copyRangesFromContractQuery(this.mapReportLineToReportView(), qbdsInventView);

    qr = new QueryRun(q);

    while (qr.next())

    {

        if (qr.changed(tableNum(InventTable)))

        {

            inventTable  = qr.get(tableNum(InventTable));

            this.insertReportLines();

        }

        if (joinInventItemGroupItem && qr.changed(tableNum(InventItemGroupItem)))

        {

            inventItemGroupItem  = qr.get(tableNum(InventItemGroupItem));

            this.insertReportLines();

        }

        if (joinProductDimensionGroupProduct && qr.changed(tableNum(EcoResProductDimensionGroupProduct)))

        {

            productDimensionGroupProduct = qr.get(tableNum(EcoResProductDimensionGroupProduct));

            inventDimParmProduct.data(EcoResProductDimGroupSetupCache::financialInventoryEnabled(productDimensionGroupProduct.ProductDimensionGroup));

            updateFinancialInventDimParm = true;

        }

        if (joinStorageDimensionGroupProduct && qr.changed(tableNum(EcoResStorageDimensionGroupItem)))

        {

            storageDimensionGroupItem = qr.get(tableNum(EcoResStorageDimensionGroupItem));

            inventDimParmStorage.data(EcoResStorageDimGroupSetupCache::financialInventoryEnabled(storageDimensionGroupItem.StorageDimensionGroup));

            updateFinancialInventDimParm = true;

        }

        if (joinTrackingDimensionGroupProduct && qr.changed(tableNum(EcoResTrackingDimensionGroupItem)))

        {

            trackingDimensionGroupItem = qr.get(tableNum(EcoResTrackingDimensionGroupItem));

            inventDimParmTracking.data(EcoResTrackingDimGroupSetupCache::financialInventoryEnabled(trackingDimensionGroupItem.TrackingDimensionGroup));

            updateFinancialInventDimParm = true;

        }

        if (qr.changed(tableNum(InventValueReportView)))

        {

            inventView  = qr.get(tableNum(InventValueReportView));

        }

        if (updateFinancialInventDimParm)

        {

            financialInventDimParm.data(inventDimParmProduct);

            financialInventDimParm = InventDimParm::orParms(financialInventDimParm, inventDimParmStorage);

            financialInventDimParm = InventDimParm::orParms(financialInventDimParm, inventDimParmTracking);

            isInventDimParmEqual = InventDimParm::isInventDimParmEqual(inventDimParmFilter, InventDimParm::andParms(inventDimParmFilter, financialInventDimParm));

        }

        if (isInventDimParmEqual && joinInventDim && (!fetchedInventDim || qr.changed(tableNum(InventDim))))

        {

            fetchedInventDim = qr.get(tableNum(InventDim));

            // Fields might not have been selected on the specified buffers, or might have been updated since selection

            fetchedInventDim.checkInvalidFieldAccess(false);

            // Clear non-financial dimensions (qr.next() is restoring the values)

            inventDim.data(fetchedInventDim);

            inventDim.clearNotSelectedDim(financialInventDimParm);

        }

        updateFinancialInventDimParm = false;

        if (isInventDimParmEqual && (inventView.Qty || inventView.Amount))

        {

            // Update transaction lines

            if (inventValueReport.DetailLevel == TotalTrans::Trans)

            {

                if (this.selectedDate(inventView) <= this.getActualToDate())

                {

                    tmpReportLine = this.findReportLine(InventValueReportLineType::Transaction, inventTable, inventItemGroupItem, inventView, inventDim);

                    this.updateReportLineBalance(tmpReportLine, 1, inventTable, inventView);

                    this.updateReportLinePL(tmpReportLine, inventTable, inventView);

                    this.updateReportLine(tmpReportLine);

                }

                // Update beginning balance

                if (inventValueReport.IncludeBeginningBalance)

                {

                    tmpReportLine = this.findReportLine(InventValueReportLineType::BeginningBalance, inventTable, inventItemGroupItem, inventView, inventDim);

                    this.updateReportLineBalance(tmpReportLine, -1, inventTable, inventView);

                    this.updateReportLine(tmpReportLine);

                }

            }

            else

            {

                // Update ending balance

                tmpReportLine = this.findReportLine(InventValueReportLineType::EndingBalance, inventTable, inventItemGroupItem, inventView, inventDim);

                if (this.selectedDate(inventView) > this.getActualToDate())

                {

                    this.updateReportLineBalance(tmpReportLine, -1, inventTable, inventView);

                }

                else

                {

                    this.updateReportLinePL(tmpReportLine, inventTable, inventView);

                }

                this.updateReportLine(tmpReportLine);

            }

        }

    }

}

Updating Sales Group Employee responable and dimension in customer

 static void GH_CustDimension_SalesGroup_Updating(Args _args)

{

    SysExcelApplication                 application;

    SysExcelWorkbooks                   workbooks;

    SysExcelWorkbook                    workbook;

    SysExcelWorksheets                  worksheets;

    SysExcelWorksheet                   worksheet;

    SysExcelCells                       cells;

    COMVariantType                      type;

    Filename                            filename;

    str                                 filePath, fileNameOnly;

    Dialog                              dialog;

    DialogField                         dialogFileName;

    int                                 row;

    container                           record, roles;

    CustTable                         _custTable;

    DimensionAttribute                _dimAttribute;

    DimensionAttributeValueSet        _dimAttributeSet;

    DimensionAttributeValueSetItem    _dimAttributeSetItem;

    DimensionAttributeValue           _dimValue;

    DimensionAttributeValueSetStorage _dimStorage;

    Common                            _dimensionValueEntry;

    CustAccount                       _custAccount;

    ;

    dialog = new Dialog();

    dialog.caption("Pick excel file");

    dialogFileName = dialog.addField(extendedTypeStr(FilenameOpen), "Enter excel file", "Browse excel file");

    if(!dialog.run())

        return;

    filename                = dialogFileName.value();

    [filePath,fileNameOnly] = fileNameSplit(filename);

    application             = SysExcelApplication::construct();

    workbooks               = application.workbooks();

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error(strFmt("Filename %1 cannot be blank.",filename));

    }

    workbook   = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet  = worksheets.itemFromNum(1);

    cells      = worksheet.cells();

    row = 1;

    do

    {

        try

        {

            row++;

            _custAccount   = cells.item(row, 1).value().bStr();

            if(strLRTrim(cells.item(row, 1).value().bStr())!= "")

                {

                     select forUpdate _custTable where _custTable.AccountNum == _custAccount;

                    {

                         ttsBegin;

                         _custTable.SalesGroup           = 'TES';

                         _custTable.MainContactWorker    = 5637156776;

                         _dimStorage                     = DimensionAttributeValueSetStorage::find(_custTable.DefaultDimension);

                         _dimAttribute                   = DimensionAttribute::findByName("GH01_Salesman");

                         _dimValue                       = DimensionAttributeValue::findByDimensionAttributeAndValue(_dimAttribute, 'TES', true, true);

                         _dimStorage.addItem(_dimValue);

                         _custTable.DefaultDimension     = _dimStorage.save();

                         _custTable.doUpdate();

                        ttsCommit;

                    }

                }

            else

            {

                warning(strFmt("Row %1 not updated.", row));

            }

        }

        catch

        {

            error(strfmt("Row %1 not updated.", row));

        }

        type = cells.item(row+1, 1).value().variantType();

    }

    while (type != COMVariantType::VT_EMPTY);

    workbook.saved(true);

    application.visible(false);

    application.quit();

    info("Excel uploaded successfully");

}