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

I have the same question (0)

All responses (9)

Answers (2)

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report
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

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

What value do you have in toDate variable in buildPeriodQuery()?

Was this reply helpful? Yes No
Khaled_Fahim 15 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Khaled_Fahim 15 on at

Like (0)

Report

toDate = dlgToDate.value(); //31-01-2019

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

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)?

Was this reply helpful? Yes No
Khaled_Fahim 15 on at

Like (0)

Report

Was this reply helpful? Yes No
Khaled_Fahim 15 on at

Like (0)

Report

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

it loops until 2020

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (1)

Report

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

Was this reply helpful? Yes No
Khaled_Fahim 15 on at

Like (1)

Report

Many thanks ^_^

I'm over 3 days trying to solve this.

I think I need to change my glasses ^_^

Was this reply helpful? Yes No