Thursday, May 14, 2015

AOT and X++ queries and ranges in AX 2012

Let's say we have a query in AOT \Queries\ProjTable














And now we want to add more datasources into this query's parent datasource (ProjTable) and some ranges etc.

Let's assume we have a class (SRS report data provider class or a dialog class) which is using above query and during processing in this class we want to add more datasources and so on.

I created a SRS report data provider class for this example;

Class declaration
[
    SRSReportQueryAttribute (querystr(MarginAnalysisReportV2Sw)),
    SRSReportParameterAttribute(classstr(MarginAnalysisReportContractV2Sw))
]
public class MarginAnalysisReportDPV2Sw extends SRSReportDataProviderBase
{
    Query                       query;
    TempTable               tempTable;
    RecordInsertList       recordInsertListTmpTable;
    ProjTable                  projTable;
    TransDate                 AsOfDate;

}

ProcessReport
[SysEntryPointAttribute]
public void processReport()
{
    // Query variable declaration
    QueryRun                            queryRun;

    // Contract class declaration
    MarginAnalysisReportContractV2Sw    dataContract;

    // Contract class parameters
    ProjDateCriteriaSw                  dateType;

    // Views declaration
    ProjTableDateViewSw                 projTableDateViewSw;

    // This will retrieve the above ProjTable query
    // Select * from ProjTable
    query = this.parmQuery();

    dataContract  = this.parmDataContract();
    dateType        = dataContract.parmDateType();
    asOfDate        = dataContract.parmAsOfDate();

    this.addParameterRanges();

    queryRun = new QueryRun(query);

    while(queryRun.next())
    {
        projTable = queryRun.get(tablenum(ProjTable));
        this.insertTmpTable(ProjTable);
    }
}

addParameterRanges() Method
private void addParameterRanges()
{
    QueryBuildDataSource    qbds1;
    QueryBuildRange            qbr1;

// add ProjTableDate table into query's parent datasource projtable
qbds1= query.dataSourceTable(tableNum(projTable)).addDataSource(tableNum(ProjTableDate));
// defining relation based on ProjId
qbds1.addLink(fieldNum(ProjTable, ProjId), fieldNum(ProjTableDate, ProjId));
// defining join mode
qbds1.joinMode(JoinMode::ExistsJoin);
// adding range on ActualEndDate field of ProjTableDate table
// setting value <= asOfDate which is considered as per date
qbds1.addRange(fieldNum(ProjTableDate,ActualEndDate)).value(queryRange(dateNull(), asOfDate));
}

Resultant X++ Query
SELECT * FROM ProjTable(ProjTable) 
EXISTS JOIN * FROM ProjTableDateViewSw(ProjTableDateViewSw_1) 
WHERE ProjTable.ProjId = ProjTableDateViewSw.ProjId AND 

      ((ActualEndDate<={ts '2015-05-14 00:00:00.000'}))


Adding OR and AND in query
QueryBuildRange                 qbr;
qbr = query.dataSourceTable(tableNum(Table)).addRange(fieldNum(Table, TransDate));
qbr.value(strFmt('((%1 != %2) || (%3 == %4))',
                      fieldStr(Table, ModelId),
                      queryvalue("Latest"),
                      fieldStr(Table, TransDate),
                      asOfDate
                      ));

qbr.value(strFmt('((%1 != %2) && (%3 == %4))',
                      fieldStr(Table, ModelId),
                      queryvalue("Latest"),
                      fieldStr(Table, TransDate),
                      asOfDate
                      ));
Using enum value in query as a range
qbr.value(strFmt('(Status == %1)', any2int(ProjStatus::active)));

Check modifiedDate value of the table
qbr.value(strFmt('(ModifiedDate > ProjTable.ModifiedDate)'));

Using wildcards in query
You can use LIKE keyword for wildcards in ranges
qbr.value(strFmt('(ProjName LIKE "*Builder*")'));

6 comments:

  1. My name is Jane Wembli Josh and i live in the USA California and life is worth living right now for me and my family and all this is because of one man sent by GOD to help me and my family, i once had a life filled with sorrow because my first SON needed a kidney transplant and all our savings were going towards his medications and this normally leaves us with no money to pay our bills or even put enough food on our table and our rent was due and no funds to pay these bills and life felt so unfair to me and every night my wife will cry herself to sleep every night until one day, i was browsing through yahoo answers and i saw a striking advert of a man that gives out personal loans and that the offer is opened to all and i have heard so many things about internet scams but at this point of my life, i was very desperate and ready to take any risk and at the end of the day, i applied for this loan and from one step to another, i got my loan within 12 hours through bank transfer and you know, it was all like a dream and i called Rev. Fr. Kevin Doran A Man who is the GOD sent lender i found and said, i have received my loan and he smiled stating that to GOD be the glory and i was so shocked as i have never ever seen anyone with such a GOD fearing and kind heart and today, i am the happiest man on earth because by GOD’S grace, my SON kidney transplant was successful and today he is healthy, i and my family are living very comfortable and happy and if you are in my former situation or in serious and legitimate need of a loan, you can reach this GOD sent lender via consumerloanfirm@gmail.com

    ReplyDelete
  2. invest funds as little as $ 200 and start earning $ 2000 weekly, alot of people has benefited from this investment offer this pandemic,the cryptocurrencies trades market has been doing wonders if you need help paying bills and you want to start making profit weekly contact Whats-App: +27633586789 or email wilsontradeszone@gmail.com

    ReplyDelete
  3. i was lost with no hope for my wife was cheating and had always got away with it because i did not know how or
    always too scared to pin anything on her. with the help a friend who recommended me to who help hack her phone,
    email, chat, sms and expose her for a cheater she is. I just want to say a big thank you to
    HACKINTECHNOLOGY@CYBERSERVICES.COM . am sure someone out there is looking for how to solve his relationship problems, you can also contact him for all sorts of hacking job..he is fast and reliable. you could also text +1 213-295-1376(whatsapp) contact and thank me later

    ReplyDelete
  4. DO YOU NEED A PERSONAL/BUSINESS/INVESTMENT LOAN? CONTACT US TODAY VIA WhatsApp +19292227023 Email drbenjaminfinance@gmail.com

    HELLO
    Loan Offer Alert For Everyone! Are you financially down and you need an urgent credit/financial assistance? Or are you in need of a loan to start-up/increase your business or buy your dream house. GET YOUR INSTANT LOAN APPROVAL 100% GUARANTEED TODAY NO MATTER YOUR CREDIT SCORE. WhatsApp:+19292227023 Email: drbenjaminfinance@gmail.com

    ReplyDelete
  5. Dear Sir/Madam,

    Finance cash no collateral required is now approved. You may view it We give financial assistance to who so ever that is interested to get support to finance his/her business Interested people in Financial assistance should please contact us for more details Via (Whats App) number:+919394133968 patialalegitimate515@gmail.com Mr Jeffery

    ReplyDelete
  6. HAVE YOU LOST YOUR MONEY TO BINARY OPTION SCAM OR ANY ONLINE SCAM WHATSOEVER?.DO YOUR DESIRE CREDIT REPAIR[EQUIFAX, EXPERIAN, TRANSUNION? WELL, YOU HAVE FOUND REDEMPTION IN ASORE CORP.



    Asore Corp is a group of multinational Hacker's, an affiliate of Evil Corp. We make sure by all means necessary that our clients get the best of services on a🔐PAYMENT AFTER JOB IS DONE BASIS✅. Rather than send money and trust a criminal to fulfill your deal, you can make sure the job is done before WORKMANSHIP is paid for. You'll get excellent customer service.

    That's a 100% guarantee. Our Cyber security Technicians are on standby 24/7 to receive your job requests.



    ⚠️ BEWARE OF FRAUDSTARS looking to hoax.

    if you have been a VICTIM, contact : ✉️cyberprecinct@gmail.com for directives.

    Here, it's always a win for you.



    🔸OUR SERVICES🔸

    ➡️Binary Option funds recovery

    ➡️Social media hack

    ➡️Recovery of loan scam

    ➡️Credit repair (Equifax,Experian,Transunion)

    ➡️Email hack

    ➡️College score upgrade

    ➡️Android & iPhone Hack

    ➡️Website design

    ➡️Website hack

    And lots more.



    DISCLAIMER: Asore Cyber Corp accepts no responsibility for any information,previously given to anybody by clients on as regarding the job. Asore Cyber Corp will not distribute contact information collected on any hacking job other than in the Asore corps Hacker's listings themselves, and will not sell contact information to third parties.



    CONTACT INFO:

    📧 asorehackcorp@gmail.com

    cyberprecinct@gmail.com



    Copyright ©️

    Asore Cyber Corp 2021.

    All rights reserved.

    ReplyDelete

I will appreciate your comments !

How to enable new Microsoft teams - Public Preview!

New Microsoft Teams is just AWESOME, quick but useful post below shows how you have this preview feature to make your life EASY!  Open Micr...