Filter records based on a month of the year in X++

(0) Share

Report

Posted on by dhamacher

115

Hi,

I need to pull records based on a NextRenewDate column in a table. The goal is to run an automated process and fetch records for the current month i.e. February. The code below works with records past today (02/02/2016) but not for records dated on 02/01/2016. Unfortunately it also pulls records for March. I was searching the web for solutions, but I haven't found a resource that would help me to further understand working with dates in X++

/// <summary>

/// Fetch all records where the month renewal date is equal to the system date month
/// </summary>
public void getProfiles()
{
    Query                       query;
    QueryRun                    queryRun;
    QueryBuildDataSource        queryDS;
    QueryBuildRange             queryRange;

    // Get current month for query range
    date currentSysDate =       today();
    int currentMonth =          mthOfYr(currentSysDate);

    // Create Query and set the range
    query = new Query();
    queryDS = query.addDataSource(tableNum(RenewProfile));
    queryRange = queryDS.addRange(fieldNum(RenewProfile, NextRenewDate));
    queryRange.value(SysQuery::range(profiles.NextRenewDate, currentMonth));
    queryRange.value(SysQuery::range(currentMonth, profiles.NextRenewDate));

    // Run query and process result set
    queryRun = new queryRun(query);
    while (queryRun.Next())
    {
        profiles = queryRun.get(tableNum(RenewProfile));
        this.insertARRTransactionRecords(profiles.MembershipNumber);
        info(profiles.MembershipNumber);
    }
}

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (2)

Daniele Ferraretto on at

Like (0)

Report

Hi, in the field profiles.NextRenewDate there is only the month or the date?

In case there is the date probsbly you must create a range begin first of month and end of month date using function from global class.

Bye, Daniele.

Was this reply helpful? Yes No
dhamacher 115 on at

Like (0)

Report

Hi Daniele,

The column stores the Date (Date type). Do you suggest to build date objects of that specific range?

Was this reply helpful? Yes No
Verified answer

Sohaib Cheema 49,438 User Group Leader on at

Like (1)

Report

hi,

when you want to filter between two dates you can use, a simple stament as below

queryRange.value(sysQuery::range(_FromDate , _ToDate));

no need to put above line 2 times

you can build and pass any values for FromDate and ToDate. Pass date, do not pass days or int

Was this reply helpful? Yes No
Daniele Ferraretto on at

Like (1)

Report

in fact I suggest to change your filter using the follow

msdn.microsoft.com/.../sysqueryrangeutil.monthrange.aspx

or

change this

queryRange.value(SysQuery::range(profiles.NextRenewDate, currentMonth));

queryRange.value(SysQuery::range(currentMonth, profiles.NextRenewDate));

as

queryRange.value(SysQuery::range(<begin date of month>, <end date of month>));

// queryRange.value(SysQuery::range(currentMonth, profiles.NextRenewDate));

Was this reply helpful? Yes No
Verified answer

dhamacher 115 on at

Like (0)

Report

Hi,

Thank you all for the responses. I solved the problem using two Date objects (from and to) and added the values into the SysQuery::Range function as follows:

/// <summary>

/// Fetch all records where the month renewal date is equal to the system date month

/// </summary>

public void getProfiles()

{

Query query;

QueryRun queryRun;

QueryBuildDataSource queryDS;

// Get current month for query range

date currentSysDate = systemDateGet();

int currentMonth = mthOfYr(currentSysDate);

int currentYear = year(currentSysDate);

date lastDateOfMonth = endmth(currentSysDate);

date firstDateOfMonth = mkDate(1, currentMonth, currentYear);

// Create Query and set the range

query = new Query();

queryDS = query.addDataSource(tableNum(RenewProfile));

SysQuery::findOrCreateRange

(queryDS,fieldNum(RenewProfile, NextRenewDate))

.value(strFmt("%1..%2", firstDateOfMonth, lastDateOfMonth));

// Run query and process result set

queryRun = new queryRun(query);

while (queryRun.Next())

{

profiles = queryRun.get(tableNum(RenewProfile));

this.insertARRTransactionRecords(profiles.MembershipNumber);

info(profiles.MembershipNumber);

}

}

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (1)

Report

good, keep it up!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hey Dhamacher,

I wonder why you preferred mkDate, when you could have used Datestartmth to get the first day of the month. Any reason or just a choice?

Was this reply helpful? Yes No