Date from dialog field does not work properly

(0) Share

Report

Posted on by Khaled_Fahim

Hi,

I have a problem in a query value when i set the date field to the date dialog field value the query retrieves all data but when i set it to mkdate(31,1,2019) as an example it filters the data as needed

Here is my code

//ClassDeclaration

FromDate fromDate;
ToDate toDate;
DialogField dlgFromDate;
DialogField dlgToDate;
#define.CurrentVersion(1)
#localmacro.CurrentList
fromDate,
toDate
#endmacro

//Dialog

public Object dialog(Object _dialog)
{

_dialog.addGroup("@SYS74558");
dlgFromDate = _dialog.addFieldValue(typeid(FromDate),fromDate,"@SYS24050");
dlgToDate = _dialog.addFieldValue(typeid(ToDate),toDate,"@SYS14656");

return _dialog;
}

//getFromDialog

fromDate = dlgFromDate.value();
toDate = dlgToDate.value();

//buildPeriodQuery and add date range (here is the problem)

private Query buildPeriodQuery()
{
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
Query queryBuild;
;
queryBuild = new Query(this.query().pack());

queryBuildDataSource = queryBuild.dataSourceTable(tablenum(CustInvoiceJour));
queryBuildRange = SysQuery::findOrCreateRange(queryBuildDataSource, fieldnum(CustInvoiceJour, InvoiceDate));

queryBuildRange.value(queryRange(fromDate, toDate)); // When i set toDate = mkdate(31,1,2019) it works fine other wise it gets all data ignoring toDate value
info(queryBuildDataSource.toString());
return queryBuild;
}

and this is the query result

SELECT FIRSTFAST * FROM CustInvoiceJour
WHERE CustTable.AccountNum = CustInvoiceJour.OrderAccount
AND ((RefNum = 0))
AND ((InvoiceDate>={ts '2019-01-01 00:00:00.000'} AND InvoiceDate<={ts '2109-01-31 00:00:00.000'})) // it gets all data the filter of date as it does not exist
JOIN FIRSTFAST * FROM CustInvoiceTrans
WHERE CustInvoiceJour.SalesId = CustInvoiceTrans.SalesId
AND CustInvoiceJour.InvoiceId = CustInvoiceTrans.InvoiceId
AND CustInvoiceJour.InvoiceDate = CustInvoiceTrans.InvoiceDate
AND CustInvoiceJour.numberSequenceGroup = CustInvoiceTrans.numberSequenceGroup

Thanks in advance

All responses (9)

Answers (2)

Khaled_Fahim 15 on at

Like (1)

Report

RE: Date from dialog field does not work properly

Many thanks ^_^

I'm over 3 days trying to solve this.

I think I need to change my glasses ^_^
Verified answer

Martin Dráb 234,163 Most Valuable Professional on at

Like (1)

Report

RE: Date from dialog field does not work properly

Thank you, your screenshot proves that I was right and you overlooked the problem. The year is indeed 2109, not 2019.
Khaled_Fahim 15 on at

Like (0)

Report

RE: Date from dialog field does not work properly

And this is whats happens when i go through query Run next()

it loops until 2020
Khaled_Fahim 15 on at

Like (0)

Report

RE: Date from dialog field does not work properly
Verified answer

Martin Dráb 234,163 Most Valuable Professional on at

Like (0)

Report

RE: Date from dialog field does not work properly

Do you want to say that *after* that line, toDate contains a data value representing 31 January 2019?

Are you 100% sure that it's 2019 and not 2109 (which you have in the resulting query string)?
Khaled_Fahim 15 on at

Like (0)

Report

RE: Date from dialog field does not work properly

toDate = dlgToDate.value(); //31-01-2019
Khaled_Fahim 15 on at

Like (0)

Report
RE: Date from dialog field does not work properly

Sorry for inconveniences

//ClassDeclaration FromDate fromDate; ToDate toDate; DialogField dlgFromDate; DialogField dlgToDate; #define.CurrentVersion(1) #localmacro.CurrentList fromDate, toDate #endmacro //Dialog public Object dialog(Object _dialog) { _dialog.addGroup("@SYS74558"); dlgFromDate = _dialog.addFieldValue(typeid(FromDate),fromDate,"@SYS24050"); dlgToDate = _dialog.addFieldValue(typeid(ToDate),toDate,"@SYS14656"); return _dialog; } //getFromDialog public boolean getFromDialog() { ; fromDate = DateStartMth(dlgFromDate.value()); toDate = endmth(dlgToDate.value()); return true; } private Query buildPeriodQuery() { QueryBuildDataSource queryBuildDataSource; QueryBuildRange queryBuildRange; Query queryBuild; ; queryBuild = new Query(this.query().pack()); queryBuildDataSource = queryBuild.dataSourceTable(tablenum(CustInvoiceJour)); queryBuildRange = SysQuery::findOrCreateRange(queryBuildDataSource, fieldnum(CustInvoiceJour, InvoiceDate)); //when I set toDate = mkdate(31,1,2019) it works fine other wise it gets data without toDate range queryBuildRange.value(queryRange(fromDate, toDate)); info(queryBuildDataSource.toString()); return queryBuild; } //info query result with toDate SELECT FIRSTFAST * FROM CustInvoiceJour WHERE CustTable.AccountNum = CustInvoiceJour.OrderAccount AND ((RefNum = 0)) AND ((InvoiceDate>={ts '2019-01-01 00:00:00.000'} AND InvoiceDate<={ts '2109-01-31 00:00:00.000'})) JOIN FIRSTFAST * FROM CustInvoiceTrans WHERE CustInvoiceJour.SalesId = CustInvoiceTrans.SalesId AND CustInvoiceJour.InvoiceId = CustInvoiceTrans.InvoiceId AND CustInvoiceJour.InvoiceDate = CustInvoiceTrans.InvoiceDate AND CustInvoiceJour.numberSequenceGroup = CustInvoiceTrans.numberSequenceGroup
Martin Dráb 234,163 Most Valuable Professional on at

Like (0)

Report

RE: Date from dialog field does not work properly

What value do you have in toDate variable in buildPeriodQuery()?
Martin Dráb 234,163 Most Valuable Professional on at

Like (0)

Report
RE: Date from dialog field does not work properly

Your code is very difficult to read. Please use Insert > Insert code in the rich formatting view, which preserves indentation and it even supports syntax highlighting (the list of supported languages doesn't include X , but you can use C#, for instance). Look:

//ClassDeclaration FromDate fromDate; ToDate toDate; DialogField dlgFromDate; DialogField dlgToDate; #define.CurrentVersion(1) #localmacro.CurrentList fromDate, toDate #endmacro //Dialog public Object dialog(Object _dialog) { _dialog.addGroup("@SYS74558"); dlgFromDate = _dialog.addFieldValue(typeId(FromDate), fromDate, "@SYS24050"); dlgToDate = _dialog.addFieldValue(typeId(ToDate), toDate, "@SYS14656"); return _dialog; } //getFromDialog fromDate = dlgFromDate.value(); toDate = dlgToDate.value(); //buildPeriodQuery and add date range (here is the problem) private Query buildPeriodQuery() { Query queryBuild = new Query(this.query().pack()); QueryBuildDataSource queryBuildDataSource = queryBuild.dataSourceTable(tablenum(CustInvoiceJour)); QueryBuildRange queryBuildRange = SysQuery::findOrCreateRange(queryBuildDataSource, fieldnum(CustInvoiceJour, InvoiceDate)); queryBuildRange.value(queryRange(fromDate, toDate)); // When i set toDate = mkdate(31,1,2019) it works fine other wise it gets all data ignoring toDate value info(queryBuildDataSource.toString()); return queryBuild; }

SELECT FIRSTFAST * FROM CustInvoiceJour WHERE CustTable.AccountNum = CustInvoiceJour.OrderAccount AND ((RefNum = 0)) AND ((InvoiceDate>={ts '2019-01-01 00:00:00.000'} AND InvoiceDate<={ts '2109-01-31 00:00:00.000'})) // it gets all data the filter of date as it does not exist JOIN FIRSTFAST * FROM CustInvoiceTrans WHERE CustInvoiceJour.SalesId = CustInvoiceTrans.SalesId AND CustInvoiceJour.InvoiceId = CustInvoiceTrans.InvoiceId AND CustInvoiceJour.InvoiceDate = CustInvoiceTrans.InvoiceDate AND CustInvoiceJour.numberSequenceGroup = CustInvoiceTrans.numberSequenceGroup