SQL statement in datasource.

lallemand

Hi,

I would like to specifie a sql statement in the datasource of a forms and i don't know how to do.

kjærsgaard

HI,

please tell us what you want to achieve with the SQL-statement. Maybe it can be achieved without SQL.

lallemand

that's my init method of my forms :

Query query;
QueryBuildDatasource qbs;

super();

query = new query() ;
qbs = query.addDataSource(tablenum(InventTrans));
qbs.name(inventTrans_ds.name());

qbs.addSortField(fieldNum(inventTrans,Direction));

qbs.addRange(fieldNum(inventTrans,Direction)).value(queryValue(InventDirection::Issue));

qbs.fields().addField(fieldNum(inventTrans,qty),SelectionField::SUM);
//qbs.fields().addField(fieldnum(inventTrans,DatePhysical));


qbs.orderMode(OrderMode::GROUPBY);

inventTrans_ds.query(query);


I want to display the month + the year of the DatePhysical and group by the result.

kjærsgaard

Hi,

Correct me if I dont understand your problem well. I presume that InventTrans table is your forms datasource and thereby the field DatePhysical already is there. Anyway, in your code you add the table as datasource.

The DatePhysical is of the type ETD DatePhysical. This ETD has some properties DateYear, DateMonth and DateDay. If you set the last(DateDay) to "Day not shown", doesn't that solve the problem?

lallemand

no

i would like to code a statement like that :

qbs.fields().addField(fieldnum(inventTrans,DatePhysical),mthofyr(DatePhysical) + "/" + year(DatePhysical));

and i want this result :


03/2006 5
04/2006 -3
05/2006 65
....

kjærsgaard

Okay,

I understand. I think it is something like below:

void init()
{
InventTrans tblInventTrans;
Str dateStr;
Int intmth;
Int intyear;
;

super();

while select DatePhysical from tblInventTrans
group by DatePhysical
asc
{
intmth = mthofyr(tblInventTrans.DatePhysical);
intyear = year(tblInventTrans.DatePhysical);
dateStr = int2str(intmth)+ "/" +int2str(intyear);
info(strfmt("Month and year of transaction: %1", dateStr));
dateStr = "";
}
}

I hope you can use my answer.

lallemand

But i have to display this information in a form's datagrid and not in a dialogbox.

In a report that's easy but in forms i don't know how to do.

Thanks for your help.

kjærsgaard

Hi again,

is it in new form the result should be shown?

lallemand

yes
In the inventable form, i made a menuItem wich call a new form with the itemid in parameters(already make).

In this new form i want to display all inventtrans with sum of quantity and group by month (in a datagrid).

kjærsgaard

Hi again,

have you found a solution to your problem?