I have to do a Form query by code. The datasource table is CustomVendTable(is a custom table).
I open a form and in my init method I get the table caller :
public void init ()
{
VendTable = myVendTableCaller;
myVendTableCaller = element.args().record();
// There is a dialog and get a date by a _Dialog_ and save in a date variable
super();
}
In my data source I have a build a query. The table in my datasource is related whit VendTable. I filter by code my DataSource by myVendTableCaller.RecId and variable date dateByDialog inserted in the opening dialog
My query is this:
public void executeQuery()
{
query q = new Query();
QueryBuildRange qbr;
QueryBuildDataSource qbds ;
QueryRun queryRun;
qbds = q.addDataSource(tableNum(CustomVendTable) );
qbds.addRange(fieldNum(CustomVendTable, ValidFrom)).value(SysQuery::value( strFmt ("<=%1 ", _dateByDialog)) ) ;
qbds.addRange(fieldNum(CustomVendTable, ValidTo)).value(SysQuery::value( strFmt (">=%1 ", _dateByDialog))) ;
qbds.addRange(fieldNum(CustomVendTable, Vendor )).value(SysQuery::value(myVendTableCaller.recId));
queryRun = new QueryRun (q);
CustomVendTable_ds.query(queryRun.query());
super();
}
*For information there is a Table Relation CustomVendTable.Vendor == VendTable.RecId
So, I have some problems! I think not to be able to make a correct query by date. The fields ValidFrom - ValidTo are UTCdatetime type.
1) I have to convert my _dateByDialog in UTC ? How to do ? It' s correct my query date way ?
Considering that the conversion is not impossible, my BIG problem is that by filtering by date, if I have only one record with recid same range and dates I can somehow see it, BUT if there are more record with these same characteristics (If I have 2 record) I don't see nothing! My Form Grid is void!
I have read that you should set the Session date time (I'm talking about this ) to have control over dates.
I still believe that I do not build my Query very well.
Do you have an idea how can I do ?
If you used DateTimeUtil::newDateTime and made new UTCDateTime parameters for the dates?