SQL statement in datasource.

lallemand · 2006-09-13

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 · 2006-09-13

HI,

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

lallemand · 2006-09-13

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 · 2006-09-13

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 · 2006-09-13

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 · 2006-09-13

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 · 2006-09-14

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 · 2006-09-14

Hi again,

is it in new form the result should be shown?

lallemand · 2006-09-14

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 · 2006-09-20

Hi again,

have you found a solution to your problem?

SQL statement in datasource.

Comments

Categories