How to perform a Lookup for a particular FinancialDimension using X++?

{
    SysTableLookup                      sysTableLookup;
    Query                               query;
    QueryBuildDataSource                qbdsDimensionFinancialTag;
    QueryBuildRange                     qbrFinancialTagCategory;

    #define.MyCustomFinancialDimension('Divisions')

    query = new Query();
    qbdsDimensionFinancialTag = query.addDataSource(tableNum(DimensionFinancialTag));
    qbrFinancialTagCategory = qbdsDimensionFinancialTag.addRange(fieldNum(DimensionFinancialTag, FinancialTagCategory));
    qbrFinancialTagCategory.value(strFmt('%1', DimensionAttribute::findByName(#MyCustomFinancialDimension, false).financialTagCategory()));

    sysTableLookup = sysTableLookup::newParameters(tableNum(DimensionFinancialTag), this);
    sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, Value), true);
    sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag, Description));
    sysTableLookup.addSelectionField(fieldNum(DimensionFinancialTag, FinancialTagCategory));
    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();
}

Creating Payment Journal in AR using X++

class CICledgerJournalDetails
{
    LedgerJournalTransTaxExtensionIN    ledgerJournalTransTaxExtensionIN;
    LedgerJournalTable                  ledgerJournalTable;
    LedgerJournalTrans                  ledgerJournalTrans;
    SalesParameters                     salesParameters;
    ledgerParameters                    ledgerParameters;
    LedgerJournalName                   ledgerJournalName;
    NumberSeq                           numberSeq;
    NumberSequenceTable                 numSeqTable,numberSequenceTable;
    container                           ledgerDimension,offsetDimension;
    RecId                               offsetledgerrecid;
    DimensionDisplayValue               dimensionDisplayValue;
    CICLedgerJournalTableExt            ledgerJournalTableExt;
    CICLedgerJournalTransExt            ledgerJournalTransExt;
    int64                               ledgerDim, OffsetledgerDim,DefDimension,LedgerOffsetAccountValue;
    Name                                LedgerAccountValue,LedgerOffsetAcc;
    RefRecId                            refRecId;
    CICCreditCommittal                  creditCommittalDetails;
    CICGBPDC                            gBPDCDetails;
    CICPettyCashRequisition             cicPettycshReq;
    LedgerJournalACType                 LedgerJournalACTypepetty;
    CICPettyCashReplenishment           pettyCashRep;
    CICPettyCashClaim                   pettyCashCla;
    CICPettyCashAutoGLLog               postingLogTable;
    LedgerJournalCheckPost              ledgerJournalValidate, ledgerJournalPost;
    int                                 counter;
    NumberSeq                           numberSeqDetails;
    purchParameters                     purchParameters;
    CICInternalRequisitionTable         internalRequisitionLocal;
    CustPaymModeTable                   custPaymModeTable;
    CompanyInfo                         companyInfo;
    CustParameters                      custParameters;
    #define.checkInvoiceAmount("Please check the invoice amount.")
}

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

public RefRecId GBPDCCreateJournal(CICGBPDC gBPDC)
{
    ttsBegin;
    if (counter == 0 )
    {
        select firstOnly JournalName from SalesParameters;
        ledgerJournalTable.JournalName = SalesParameters.JournalName;
        ledgerJournalTable.initFromLedgerJournalName(ledgerJournalTable.JournalName);
        ledgerJournalTable.insert();

        select firstOnly LedgerJournalTable, RecId from ledgerJournalTableExt
            where ledgerJournalTableExt.LedgerJournalTable == ledgerJournalTable.RecId;
        if(!ledgerJournalTableExt.RecId)
        {
            ledgerJournalTableExt.LedgerJournalTable = ledgerJournalTable.RecId;
            ledgerJournalTableExt.insert();
            refRecId = ledgerJournalTableExt.recid;
        }
    }
    counter++;
    select firstonly NumberSequenceTable, JournalName from ledgerJournalName
        where ledgerJournalName.JournalName == ledgerJournalTable.JournalName
    join NumberSequence from numberSequenceTable
        where numberSequenceTable.RecId == ledgerJournalName.NumberSequenceTable;

    numberSeq = NumberSeq::newGetVoucherFromCode(numberSequenceTable.NumberSequence);
    ledgerJournalTrans.JournalNum               = ledgerJournalTable.JournalNum;
    ledgerJournalTrans.voucher                  = numberseq.voucher();
    ledgerJournalTrans.initValue();
    LedgerJournaltrans.initFromLedgerJournalTable_LT(ledgerJournalTable);
    DefDimension = this.DefaultDimension(gBPDC.Divisions,gBPDC.ProfitCenter,"");
    ledgerJournalTrans.Txt                      = gBPDC.Text;
    ledgerJournalTrans.TransDate                = gBPDC.CommittalDate;
    LedgerJournalTrans.MarkedInvoice            = gBPDC.Invoice;
    ledgerJournalTrans.AmountCurCredit          = gBPDC.Amount;
    ledgerJournalTrans.CurrencyCode             = CompanyInfo::standardCurrency();
    ledgerJournalTrans.Company                  = curext();
    LedgerJournalTrans.AcknowledgementDate      = systemDateGet();
    ledgerJournalTrans.PaymMode                 = gBPDC.PaymMode;
    ledgerJournalTrans.PaymReference            = gBPDC.PaymReference;
    LedgerJournalTrans.CICDrawerName            = gBPDC.DrawerName;
    LedgerJournalTrans.CICDrawersBank           = gBPDC.DrawersBank;
    ledgerJournalTrans.CICCommissionPayoff      = gBPDC.CommissionPayoff;
    ledgerJournalTrans.Approved                 = NoYes::Yes;
    ledgerJournalTrans.Approver                 = HcmWorker::userId2Worker(curUserId());
    ledgerJournalTrans.Due                      = ledgerJournalTrans.TransDate;
    changecompany(ledgerJournalTrans.Company)
    {
        select firstOnly PostingProfile from custParameters
            index Key
            where custParameters.Key == 0;
        ledgerJournalTrans.PostingProfile           = custParameters.PostingProfile;//CustParameters::findByCompany(ledgerJournalTrans.Company).PostingProfile;
    }
    ledgerJournalTrans.AccountType              = LedgerJournalACType::Cust;
    LedgerAccountValue                          = gBPDC.Customer;
    ledgerJournalTrans.TransactionType          = LedgerTransType::Payment;
    if(LedgerAccountValue)
    {
        if(ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)
        {
            ledgerDim                           = DimensionStorage::getDynamicAccount(LedgerAccountValue, ledgerJournalTrans.AccountType);
            LedgerJournalTrans.LedgerDimension  = DimensionAttributeValueCombination::find(ledgerDim).Recid;
            LedgerJournalTrans.DefaultDimension = DefDimension;
        }
    }

    select firstonly OffsetAccountType, JournalName from ledgerJournalName
        where ledgerJournalName.JournalName == ledgerJournalTable.JournalName;

    ledgerJournalTrans.OffsetAccountType = ledgerJournalName.OffsetAccountType;



    select firstonly PaymMode, InterCompanyLedgerDimension from custPaymModeTable
        where custPaymModeTable.PaymMode == gBPDC.PaymMode;

    LedgerOffsetAccountValue                    = custPaymModeTable.InterCompanyLedgerDimension;
    LedgerJournalTrans.OffsetDefaultDimension   = DefDimension;

    if(LedgerOffsetAccountValue)
    {
        if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Ledger)
        {
            OffsetledgerDim                          = DimensionDefaultingService::serviceCreateLedgerDimension(LedgerOffsetAccountValue, DefDimension);
            LedgerJournalTrans.OffsetLedgerDimension = OffsetledgerDim;
        }
        else
        {
            LedgerJournalTrans.OffsetLedgerDimension = LedgerOffsetAccountValue;
        }
    }

    ledgerJournalTrans.LineNum  = LedgerJournalTrans::lastLineNum(ledgerJournalTrans.JournalNum) + 1;
    ledgerJournalTrans.ExchRate = Currency::exchRate(ledgerJournalTrans.CurrencyCode, ledgerJournalTrans.TransDate);
    ledgerJournalTrans.insert();

    ledgerJournalTransTaxExtensionIN.LedgerJournalTrans = LedgerJournalTrans.RecId;
    ledgerJournalTransTaxExtensionIN.TaxInformation_IN  = TaxInformation_IN::findDefaultbyLocation(CompanyInfo::findByCompany_IN().PrimaryAddressLocation).RecId;
    ledgerJournalTransTaxExtensionIN.insert();

    select firstOnly ledgerJournalTrans, GBPDCId, AccountTypeId, RecId from ledgerJournalTransExt
        where ledgerJournalTransExt.ledgerJournalTrans == ledgerJournalTrans.RecId;
    if(!ledgerJournalTransExt.RecId)
    {
        ledgerJournalTransExt.GBPDCId            = gBPDC.GBPDCId;
        ledgerJournalTransExt.AccountTypeId      = gBPDC.AccountTypeId;
        ledgerJournalTransExt.ledgerJournalTrans = ledgerJournalTrans.RecId;
        ledgerJournalTransExt.insert();
    }
    select firstOnly forupdate recid, JournalNo from gBPDCDetails
        where gBPDCDetails.recid == gBPDC.RecId;
    if (gBPDCDetails.RecId)
    {
        gBPDCDetails.JournalNo = ledgerJournalTable.JournalNum;
        gBPDCDetails.update();
    }
    this.GBPDCcustomerSettlement(gBPDC);
    ttsCommit;

    return refRecId;
}

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

public void GBPDCCustomerSettlement(CICGBPDC _gBPDC)
{
    custvendopentransmanager            manager;
    CustTransOpen                       CustTransOpen;
    CustTrans                           CustTransExt;
    ExchangeRateHelper                  exchangeRateHelper;
    AmountCur                           totalSettlement;

    select firstOnly AccountNum, RefRecId, AmountCur from CustTransopen
        where CustTransOpen.AccountNum  == _gBPDC.Customer
    join Invoice, RecId, AccountNum from CustTransExt
        where CustTransExt.Invoice      == _gBPDC.Invoice
        && CustTransExt.RecId           == CustTransopen.RefRecId
        && CustTransExt.AccountNum      == CustTransOpen.AccountNum;

    if(CustTransopen && CustTransOpen.AmountCur >= _gBPDC.Amount)
    {
        manager = custvendopentransmanager::construct(ledgerJournalTrans);
        manager.updateTransMarked(CustTransOpen,true);

        select firstonly RecId from companyInfo
            where companyInfo.DataArea == ledgerJournalTrans.Company;
        exchangeRateHelper  =  ExchangeRateHelper::newCurrency(Ledger::primaryLedger(companyInfo.RecId),ledgerJournalTrans.CurrencyCode);

        totalSettlement    = SpecTransManager::getTotalSettleAmountForSpecReference(
                                ledgerJournalTrans.Company,
                                ledgerJournalTrans.TableId,
                                ledgerJournalTrans.RecId,
                                ledgerJournalTrans.CurrencyCode,
                                ledgerJournalTrans.Company,
                                ledgerJournalTrans.TransDate,
                                exchangeRateHelper.prepareExchangeRateForStorage(ledgerJournalTrans.crossrate()));

        //To update in ledgerJournal trans
        ttsBegin;
        ledgerJournalTrans.selectForUpdate(true);
        ledgerJournalTrans.AmountCurCredit   = _gBPDC.Amount <= abs(totalSettlement) ? _gBPDC.Amount : 0;
        ledgerJournalTrans.SettleVoucher = SettlementType::SelectedTransact;
        ledgerJournalTrans.MarkedInvoice = _gBPDC.Invoice;
        ledgerJournalTrans.doupdate();
        ttsCommit;
    }
    else
    {
        checkFailed(#checkInvoiceAmount);
    }
}

Dynamic Query in X++

// The first step is to define the query variables that we will be using. This is building the framework.
    Query                                q;
    QueryRun                         qr;
    QueryBuildDataSource    qbds;
    QueryBuildRange             qbr;
    PurchTable                        purchTable;

// Instantiates or creates the query framework.
    q       = new Query();

// Links the table to the data source definition.
    qbds    = q.addDataSource(tableNum(PurchTable));

//Set the Range and Range value
    qbr     = qbds.addRange(fieldNum(PurchTable, PurchId));
    qbr.value('GB-PO-000040');

//Instantiate the query
    qr      = new QueryRun(q);
 
//Query will display a query form for the user. The user can then add their own number sequences or sorting preferences. This is ideal to create much more dynamic queries for form information or reporting.

    if(qr.prompt())
    {

//The while loops over the Table in search of the information needed.
        while(qr.next())
        {
            purchTable = qr.get(tableNum(PurchTable));
            info(strfmt("%1 - %2",PurchTable.PurchId,PurchTable.PurchName));
        }
    }

SysQuery functions

Instead of direct assignment of the values to the query build range value, we can use SysQuery class methods to avoid the errors of datatype conversion. So here in this post I gave some useful methods.

QueryBuildRange qbr;

---To set the value we usually follow as below mentioned code

qbr.value(“Our Value”);

qbr.value(queryValue(“Our Value”));

--We can set the value using SysQuery methods like mentioned below

qbr.value(SysQuery::value(“Our Value”));

qbr.value(SysQuery::valueLike(“Our Value”));

qbr.value(SysQuery::valueNot(“Our Value”));

---For logical NOT

qbr.value(SysQuery::valueNot(“Our Value”));

---To retrive all records

in usual way we can give like empty (” “) but using sysquery class

qbr.value(SysQuery::valueUnlimited());

---To retrive Null records

qbr.value(SysQuery::valueEmptyString());

---To retrive Not Null records

qbr.value(SysQuery::valueNotEmptyString());

---To give range

usually we add qbr.value(fromDate,toDate)  but using sysQuery

qbr.value(SysQuery::range(fromDate,toDate);

The method will add the dots (‘..’)  like this

---To find total number of records available in the resulting query

info(strFmt(“%1”,SysQuery::countTotal(queryRun)));

---To find total number of Datasource available in the resulting query

info(strFmt(“%1”,SysQuery::countLoops(queryRun)));

--- For Datasource and Range creation

SysQuery::findOrCreateRange(parameter);

SysQuery::findOrCreateDataSource(parameter);

SysQuery::findOrCreateDataSourceByName(parameter);