Langsung ke konten utama

QBDS Query Range

Range for string


QueryBuildDataSource  qbds;
queryBuildRange   qbr;
;
qbds = this.query().dataSourceTable(tablenum(SalesQuotationTable));
qbds.clearRanges();
qbdr = qbds.addRange(fieldnum(SalesLine, PlanModelId));
 //STRING
qbds.addRange(fieldnum(SalesLine, PlanModelId)).value(SysQuery::valueEmptyString()); // = value('=""');
qbds.addRange(fieldnum(SalesLine, PlanModelId)).value('!=""');
qbds.addRange(fieldnum(SalesLine, PlanModelId)).value('1');
qbds.addRange(fieldnum(SalesLine, PlanModelId)).value(queryValue('!=' + planModel.PlanModelId));

queryBuildRange = queryBuildDataSource.addRange(fieldnum(WMSLocation, SPLCustAccount));
queryBuildRange.value('=""');    -> SPLCUSTAccount=''  -> ((SPLCUSTAccount=''))
queryBuildRange.value(SysQuery::valueEmptyString()); -> SPLCUSTAccount=''  -> ((SPLCUSTAccount=''))
queryBuildRange.value(queryValue(''));   -> SPLCUSTAccount=''  -> ((SPLCUSTAccount=''))

queryBuildRange.value('!=""');     -> !SPLCUSTAccount='' -> ((!SPLCUSTAccount=''))

queryBuildRange.value(''); -> ATTENTION : THE Range is not added to the query !!!!!!!!!!
 
 //-> To initialize the range, the range is not took in the query (= value('') ?) 
 //value(""); is good also for integer and date.
 queryBuildRange.value(""); -> To initialize the range, the range is not took in the query.

//Range LIKE => value("*AA*")
if (SearchText)
{
     SearchText = "*" + SearchText + "*";
     if (ServiceSearchType == SPLWasteServiceSearchType::ServiceOrder)
     {
         qbds.addRange(fieldNum(SMAServiceOrderTable,serviceOrderId)).value(SearchText);
     }
     else if (ServiceSearchType == SPLWasteServiceSearchType::Customer)
     {
         qbds.addRange(fieldNum(SMAServiceOrderTable,CustAccount)).value(SearchText);
     }
 }
  -> range "*SearchText*" = LIKE



Range for integer


 
qbds.addRange(fieldNum(SalesLine, SalesQty)).value(queryValue('>0'));
qbds.addRange(fieldNum(SalesLine, SalesQty)).value('>0');

note for integer : (value('>0') == value(queryValue('>0')))



Range for enum


 
qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(queryValue(SalesQuotationStatus::Created));
qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(queryValue('!=' + enum2str(SalesQuotationStatus::Created)));

 //ENUM - the different way to write a value for an enum.
qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(enum2str(SalesQuotationStatus::Created));
qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(enum2value(SalesQuotationStatus::Created));  //to check
qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(SysQuery::value(SalesQuotationStatus::Created));
qbds.addRange(fieldnum(SalesQuotationTable, QuotationStatus)).value(queryValue(SalesQuotationStatus::Created));
 
 //example code to add list of codes
public void executeQuery()
{
 qbrWMSPickingRouteType.value(element.strRangePickingRoutesTypes());
 super();
} 
public str strRangePickingRoutesTypes()
{
 str                 selectionStr = '';
 if (showWMS)
 {
  selectionStr +=enum2str(WMSShipmentType::ConsolidatedPick);
 }
 if (!selectionStr || (showPick && showWMS))
 {
  return enum2str(WMSShipmentType::ConsolidatedPick) + ',' + enum2str(WMSShipmentType::OrderPick);
 }
 return selectionStr;
} 
 
statusRange = queryBuildDataSource.addRange(fieldNum(WMSTransport, ExpeditionStatus));
statusRange.value(strFmt('%1',  enum2Value(WMSExpeditionStatus::Activated)));



Range for date


 
qbds.addRange(fieldnum(SalesLine, ShippingDateConfirmed)).value(queryValue(dateNull()));
qbds.addRange(fieldnum(SalesLine, ShippingDateConfirmed)).value(queryValue('!0'));
qbds.addRange(fieldnum(SalesLine, ShippingDateConfirmed)).value(queryValue('>0'));
reconciledDate.value('>' + queryValue(dateNull()));

reconciledDate.value(''); ? = reconciledDate.value(queryValue(dateNull()));


qbds.addRange(fieldnum(SalesLine, ShippingDateConfirmed)).value(queryRange(_startDateStart, _startDateEnd));
qbds.addRange(fieldnum(SalesLine, ShippingDateConfirmed)).value(queryValue(_fromDate) + '..' + queryValue(_toDate));

--> (periodStart .. periodEnd)
criteriaStartDate.value(queryRange(period.PeriodStart,period.PeriodEnd));

--> (dateNull or (aujourd'hui et max)).
queryBuildRange.value(queryValue(dateNull()) + ',' + queryRange(today(), dateMax()));

FromDate            fromDate;
ToDate              toDate;
qbr.value(queryValue(fromDate)+ '..'+ queryValue(toDate));


//Range : EndDate = Null OR EndDate >= DateJour : (we need to define 2 times the QueryBuildRange)
queryBuildRangeDate = queryBuildDataSource1.addRange(fieldNum(SMAAgreementTable,EndDate));
queryBuildRangeDate.value(queryValue(dateNull()));
queryBuildRangeDate = queryBuildDataSource1.addRange(fieldNum(SMAAgreementTable,EndDate));
queryBuildRangeDate.value(queryRange(systemDateGet(),dateMax()));



Range for All dates


 
//This example show with date how to use the null date. 
//If null date and we want to see all the lines we need to write qbrDate.value('');
public class FormRun extends ObjectRun
{
    date                            dateSearch;
    QueryBuildRange                 qbrDate;
}
//Init of the form
public void init()
{
    dateSearch      = systemDateGet() + 1;
    super();
    DateHeader.dateValue(dateSearch);
}
//Init of the DS
public void init()
{
    QueryBuildDataSource    qbds;
    super();
    qbds = this.query().dataSourceTable(tablenum(SPLDailyBID));
    qbrDate = qbds.addRange(fieldNum(SPLDailyBID,BIDDate));
    qbrDate.value(QueryValue(dateSearch));
}
//Modified of the field Date
public boolean modified()
{
    boolean ret;
    ret = super();

    if (this.dateValue())
    {
        qbrDate.value(QueryValue(this.dateValue()));
    }
    else
    {
        qbrDate.value('');
    }
    SPLDailyBID_ds.executeQuery();
    return ret;
}



Range for date Time


 
class SMAServiceFunctionLine
protected void setQueryDatePeriod()
{
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    utcdatetime             dateFrom;
    utcdatetime             dateTo;

    qbds = queryRun.query().dataSourceTable(tableNum(SMAServiceOrderTable));
    dateFrom = DateTimeUtil::newDateTime(periodFromDate, 0);
    dateTo = DateTimeUtil::newDateTime(periodToDate, 0);
    // Add 1 day to get inclusive range
    dateTo = DateTimeUtil::addDays(dateTo, 1);

    if (periodFromDate && periodToDate)
    {
        qbr = qbds.addRange(fieldNum(SMAServiceOrderTable, ServiceDateTime));
        qbr.value(strFmt('((%1 >= %2) && (%3 < %4))',
            fieldStr(SMAServiceOrderTable, ServiceDateTime), DateTimeUtil::toStr(dateFrom),
            fieldStr(SMAServiceOrderTable, ServiceDateTime), DateTimeUtil::toStr(dateTo)));
    }
    else if (periodFromDate)
    {
        qbr = qbds.addRange(fieldNum(SMAServiceOrderTable, ServiceDateTime));
        qbr.value(strFmt('(%1 >= %2)',
            fieldStr(SMAServiceOrderTable, ServiceDateTime), DateTimeUtil::toStr(dateFrom)));
    }
}



Range with strfmt


 
//example 1
 queryBuildRange = queryBuildDataSource.addRange(fieldnum(WMSPickingRoute, ExpeditionStatus));
 queryBuildRange.value(enum2Value(WMSExpeditionStatus::Activated));
 queryBuildRange.status(RangeStatus::Locked);

 queryBuildRange = queryBuildDataSource.addRange(fieldnum(WMSPickingRoute, ExpeditionStatus));
 queryRange = strfmt('((' + fieldstr(WMSPickingRoute, ExpeditionStatus) + ' == WMSExpeditionStatus::Started) && ('+ fieldstr(WMSPickingRoute, Operator) + ' == "%1"))', _emplId);
 queryBuildRange.value(queryRange);
 queryBuildRange.status(RangeStatus::Locked);

 ((expeditionStatus = 3) OR
 -> (((ExpeditionStatus == WMSExpeditionStatus) && (Operator == "0110"))))

//example 2
 queryBuildRange = queryBuildDataSource.addRange(fieldnum(WMSPickingRoute, ExpeditionStatus));
 queryBuildRange.value(enum2Value(WMSExpeditionStatus::Activated));
 queryBuildRange.status(RangeStatus::Locked);

 queryBuildRange = queryBuildDataSource.addRange(fieldnum(WMSPickingRoute, ExpeditionStatus));
 queryRange = strfmt('((' + fieldstr(WMSPickingRoute, ExpeditionStatus) + ' == WMSExpeditionStatus::Started) && ('+ fieldstr(WMSPickingRoute, Operator) + ' == "%1"))', _emplId);
 queryBuildRange.value(queryRange);
 queryBuildRange.status(RangeStatus::Locked);

 ((expeditionStatus = 3) OR
 -> (((ExpeditionStatus == WMSExpeditionStatus) && (Operator == "0110"))))

//example 3
 queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) && (%4 == "%5")))',
 fieldStr(InventTable, ItemType),
 any2int(ItemType::Service),
 any2int(ItemType::Item),
 fieldStr(InventTable, ProjCategoryId),
 queryValue("Spares")));

 if (inventTableModule.LineDisc)
 {
  qbr = qBuild.addRange(fieldnum(LPLSalesDealLine,ItemCode));
    strQuery = strFmt('((%1 == %2) && (%3 == "%4"))',
    fieldStr(LPLSalesDealLine, ItemCode),
    any2int(LPLTableGroupBrandBgAll::All),
    fieldStr(LPLSalesDealLine, ItemRelation),
    "");                                                       <---use "" and not SysQuery::valueEmptyString()
  qbr.value(strQuery);
 }
 
//example 4
Query hideReturnReservationLine(Query _query)
{
 QueryBuildRange      qbrHideReservationLine;
 QueryBuildDataSource qbdsSalesLine = _query.dataSourceTable(tablenum(SalesLine));
 int                  statusAwaiting = ReturnStatusLine::Awaiting;
 ;

 qbrHideReservationLine = qbdsSalesLine.addRange(fieldnum(SalesLine, ReturnStatus));
 qbrHideReservationLine.value(strfmt('((%1.%4 != %5) || (%1.%2 < %3)&&(%1.%4 == %5))',
 qbdsSalesLine.name(),//%1
 fieldstr(SalesLine, ExpectedRetQty),//%2
 0, //%3
 fieldstr(SalesLine, ReturnStatus), //%4
 statusAwaiting)); //%5
 qbrHideReservationLine.status(RangeStatus::Hidden);

 return _query;
}



Range with strfmt and field T.X = T.Y


 
 &&  (vendTrans.TransType     == LedgerTransType::Payment ||
  (vendTrans.Correct == NoYes::Yes && vendTrans.TransType == LedgerTransType::Vend  &&
  (vendtrans.BPLAdvPayment == '' || (vendTrans.LastSettleVoucher == vendtrans.BPLAdvPayment && vendtrans.BPLAdvPayment != ''))))

    qbdsVendTrans = qbdsLedgerTrans.addDataSource(tablenum(VendTrans),"VendTrans"); //Here "Vendtrans" because if not : qbdsVendTrans.name() -> vendTrans_1 !
    qbrTransType  = qbdsVendTrans.addRange(fieldnum(VendTrans, TransType));
    qbrTransType.value(strfmt('( (%1.%2 == %6) || ( (%1.%3 = %7) && (%1.%2 == %8) && ( (%1.%4 == %9) || ( (%1.%5 == %1.%4) && (%1.%4 != %9) ))))',
    qbdsVendTrans.name(),                       //%1
    fieldstr(VendTrans, TransType),             //%2
    fieldstr(VendTrans, Correct),               //%3
    fieldstr(VendTrans, BPLAdvPayment),         //%4
    fieldstr(VendTrans, LastSettleVoucher),     //%5
    any2int(LedgerTransType::Payment),          //%6
    any2int(NoYes::Yes),                        //%7
    any2int(LedgerTransType::Vend),             //%8
    SysQuery::valueEmptyString()));             //%9   // (%1.%4 == %9) -> VendTrans.BPLAdvPayment == ""  //I check SysQuery::valueEmptyString() this is good.



RangeStatus


 
 QueryBuildRange         qbr;
 ;
 qbr = queryBuildDataSource.addRange(fieldnum(Address, Address));
 qbr.value(queryExpression);
 qbr.status(RangeStatus::Hidden);// - RangeStatus::Hidden - RangeStatus::Locked



QueryRangeConcat


 
 //1)
 queryBuildRange.value(queryRangeConcat(SysQuery::value(InventQuarantineStatus::Created), SysQuery::value(InventQuarantineStatus::ReportedFinished)));
 //2)
 str queryRange = '';

 queryRange = queryRangeConcat(queryRange,queryValue(TaxDirection::IncomingTax));
 queryRange = queryRangeConcat(queryRange,queryValue(TaxDirection::TaxExemptPurchase));
 queryRange = queryRangeConcat(queryRange,queryValue(TaxDirection::OutgoingTax));
 queryRange = queryRangeConcat(queryRange,queryValue(TaxDirection::TaxExemptSales));
 queryBDS.addRange(fieldnum(TaxTrans, TaxDirection)).value(queryRange);

 range = queryRangeConcat('',StatusIssue::ReservPhysical);
 range = queryRangeConcat(range,StatusIssue::ReservOrdered);
 range = queryRangeConcat(range,StatusIssue::OnOrder);
 range = queryRangeConcat(range,StatusIssue::QuotationIssue);
 qbds_InventTrans2.addRange(fieldnum(InventTrans,StatusIssue)).value(range);

 //3)
 queryBuildDataSource.addRange(fieldnum(WMSOrderTrans,OrderType)).value(Global::queryRangeConcat(queryValue(WMSOrderType::OrderPick),WMSOrderType::Output));
 -> ((orderType = 3 OR orderType = 0))

 //4     rangeStatus = queryRangeConcat(queryNotValue(WMSShipmentStatus::Shipped),queryNotValue(WMSShipmentStatus::Canceled));
 -> ((NOT (status = 9)) AND NOT (status = 3)))



Range A = "X" or A = "Y"


 
 qbr = queryLoc.dataSourceTable(tablenum(WMSLocation)).findRange(fieldnum(WMSLocation, WMSLocationId));

 -> qbr         = wMSLocationId = N'A00' or wMSLocationId = N'A01' OR wMSLocationId = N'A02'
 -> rangeWMSLoc = "A00,A01,A02"
 qbr.value(rangeWMSLoc) -> ((wMSLocationId = N'A00' or wMSLocationId = N'A01' OR wMSLocationId = N'A02'))

 qbr = queryLoc.dataSourceTable(tablenum(WMSLocation)).findRange(fieldnum(WMSLocation, WMSLocationId));
 qbr.value("A00");
 qbr = queryLoc.dataSourceTable(tablenum(WMSLocation)).findRange(fieldnum(WMSLocation, WMSLocationId));
 qbr.value("A01");
 qbr = queryLoc.dataSourceTable(tablenum(WMSLocation)).findRange(fieldnum(WMSLocation, WMSLocationId));
 qbr.value("A02");
 -> ((wMSLocationId = N'A00' or wMSLocationId = N'A01' OR wMSLocationId = N'A02'))



FindRange


 
 range rangeWMSLoc;
 ;
 qbr = queryLoc.dataSourceTable(tablenum(WMSLocation)).findRange(fieldnum(WMSLocation, WMSLocationId));
 if (qbr)
    rangeWMSLoc = qbr.value();



Find or create range


 
    qbrWMSPickingRouteType = SysQuery::findOrCreateRange(qbdsWMSPickingRoute,fieldnum(WMSPickingRoute,ShipmentType));



List of the range


 
 int                     cnt, i;
 QueryBuildRange         qbr;
 Query                   queryFreeText;
 QueryBuildDataSource    qdbsFreeText;

 queryFreeText = new Query();
 qdbsFreeText  = queryFreeText.addDataSource(tableNum(VendInvoiceJour));

 cnt = qr.query().dataSourceTable(tablenum(VendInvoiceJour)).rangeCount();

 for (i=1 ; i<=cnt; i++)
 {
  qbr = qr.query().dataSourceTable(tablenum(VendInvoiceJour)).range(i);
  qdbsFreeText.addRange(qbr.field()).value(qbr.value());
 }

Add range on the same field x times



//AddRange on the same field x times.
public void linkActive()
{
    SalesTable          localSalesTable;
    InventTransferTable localInventTransferTable;
    SalesLine           localSalesLine;

    if (salesTable_ds)
    {
        this.query().dataSourceTable(tablenum(WMSPickingRouteLink)).clearDynalinks();
        this.query().dataSourceTable(tablenum(WMSPickingRouteLink)).clearRanges();

        for (localSalesTable = salesTable_ds.getFirst(true) ? salesTable_ds.getFirst(true) : salesTable_ds.cursor(); localSalesTable; localSalesTable = salesTable_ds.getNext())
        {
            this.query().dataSourceTable(tablenum(WMSPickingRouteLink)).addRange(fieldnum(WMSPickingRouteLink, OrigInventTransRefId)).value(queryValue(localSalesTable.SalesId));
        }
    }
    super();
}

Range with fieldName2Id



// Range using fieldName2id;
   if (HeaderDateMonthYear.dateValue())
    {
        iMonth = MthOfYr(HeaderDateMonthYear.dateValue());
        fieldName = #SMAFormulaFieldMonth + strRFix(int2str(iMonth),2,"0");
        qbdsSPLWasteSMAFormula.addRange(fieldName2id(tableNum(SPLWasteSMAFormula),fieldName)).value(queryValue(NoYes::Yes));
    }
 
 //======getFieldValue ============//
 //in the table I have :
 //Month01
 //Month02
 //Month03 ...
 //#SMAFormulaFieldMonth = "Month"
    for (i = 1; i <= 12; i++)
    {
        MonthValue = this.getFieldValue(#SMAFormulaFieldMonth + strLFix(int2str(i),2,"0"));
        if (MonthValue)
        {
            transDate = mkDate(01,i,iYear);
            return transDate;
        }
    } 
 
// another example class MultiSelectionHelper, called in SMAServiceFunctionLine

multiSelectionHelper.createQueryRanges(qbdsAgreementLine, fieldstr(SMAAgreementLine, AgreementLineNum)); 

public void createQueryRanges(QueryBuildDataSource _queryBuildDataSourece, FieldName _fieldName, FieldName _recordFieldName = _fieldName)
{
    fieldId         datasourceFieldId;
    fieldId         recordFieldId;
    Common          record;

    // currentRecord and currentRecordIdx should be restored before return
    Common          copyOfCurrentRecord     = currentRecord;
    int             copyOfCurrentRecordIdx  = currentRecordIdx;
    ;

    if (!_queryBuildDataSourece)
    {
        throw error("@SYS96323");
    }

    record = this.getFirst();

    if (record)
    {
        datasourceFieldId   = fieldname2id(_queryBuildDataSourece.table(), _fieldName);
        recordFieldId       = fieldname2id(record.TableId, _recordFieldName);

        if (!datasourceFieldId)
        {
            throw error(strFmt("@SYS94058", _fieldName, _queryBuildDataSourece.name()));
        }

        if (!recordFieldId)
        {
            throw error(strFmt("@SYS75684", _recordFieldName, tableid2name(record.TableId)));
        }
    }


    while (record)
    {
        _queryBuildDataSourece.addRange(datasourceFieldId).value(SysQuery::value(record.(recordFieldId)));
        record = this.getNext();
    }

    currentRecord       = copyOfCurrentRecord;
    currentRecordIdx    = copyOfCurrentRecordIdx;
}

Note



 SysQuery::value() = queryValue()
 EX : SysQuery::value(InventQuarantineStatus::Created)  = queryValue(InventQuarantineStatus::Created)

 qbr.value('!=""');   !!!*** qbr.value("!=''"); doesn't work ***!!!
 //QueryValue is used if I write a variable inside the value :
 qbr.value('!=""'); qbr.value(queryValue('!=' + planModel.PlanModelId));

sumber  












Label:































Komentar











Posting Komentar




















Postingan populer dari blog ini



















Use X++ wildcard (LIKE and NOT LIKE) in X++ select statement




























For x++ select statements:  select firstOnly batchHistory      where batchHistory.Caption  LIKE  "*Test*"  For x++ queries:  queryBuildRange.value(*Test*); Note the LIKE instead of a '==' and the wildcards inside of the quotations. All other combinations of wildcard usage will work here. This is the same functionality as what the users would put in the grid filtering(eg. '*TEST*' in the caption field filter on the batch tasks form).  However, if you want to find all Captions that do not have the word Test in them (NOT LIKE, !LIKE), you will have to modify the above example slightly.  For x++ select statements:  select firstOnly batchHistory      where  !( batchHistory.Caption LIKE "*TEST*" ) ;  For x++ queries:  queryBuildRange.value(!*Test*);




















Posting Komentar









Baca selengkapnya




























Cleanup Jobs AX 2012




























 Cleanup jobs in Dynamics AX 2012 can help delete old data that is not needed anymore, and this reduction in database size can improve performance.   In Dynamics AX 2012, cleanup jobs are available across various modules to run manually or in a batch mode. It is important to note that these jobs should be run after enough analysis determines that the business doesn’t need this data in the future. Please be sure to discuss with business management before deleting old data used in transactions that is not required any longer.   Dynamics AX 2012 adds data to various tables for staging/intermediate purpose and this staging/intermediate data can be deleted once the transaction has been posted such as Sales update history, Purchase update history data.     Here is the list of cleanup jobs available in Dynamics AX 2012 along with brief descriptions. All of these cleanup jobs are available in module > Periodic > Cleanup section      Module   Job   Description    General Ledger   Clean up...




















Posting Komentar









Baca selengkapnya






























AX X++ str2Date




























From time to time, a user will need to pull a date from a string. More often than not it is from an XML or from some other location. In order to actually use this date passed, it needs to be of data type 'date' so it can be written to fields, used in queries etc.  To do this, a user can use the 'str2Date' function. The str2Date function takes two parameters:   datestring = The date in string format. It can be separated by either a dash ('-') or slash ('/')  sequence = A three number combo using 1, 2, and 3 where 1 = day, 2 = month, and 3 = year. (e.g. 231 would be MM-YYY-DD, and 321 would be YYYY-MM-YY)   If the sequence doesn't match the string or has an error, a '0' will result. Note that its very important to distinguish between day and month as an invalid month (>12) will result in a 0 and if both are <13, the date can translate totally wrong. I've detailed out the results of various scenarios below. Should be pretty self explan...




















Posting Komentar









Baca selengkapnya