[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.axapta.programming

how can I use one query to get 2 tables( parent tbl and child tb

elton

12/13/2005 9:05:00 AM


Salesline is child datasource in parent datasource SalesTable in a report
design.
my Fetch Method like this:

SalesTable _salesTable;
Query qSalesTable;
Queryrun qrSalesTable;

SalesTable _salesLine;
Query qSalesLine;
Queryrun qrSalesLine;


;
qSalestable = element.query();
qSalestable.dataSourceTable(tablenum(SalesTable));
qrSalesTable = new queryrun(qSalesTable);

qSalesLine = new query();
qSalesLine.addDataSource(tablenum(SaleLine));

while( qrSalesTable.next)
{
_salesTable = qrsalesTable.get(tablenum(SalesTable));
element.send(_salestable)
qSalesLine.DataSourceTable(tablenum(SaleLine)).addRange(fieldnum(SaleLine,SalesId).value(_salesTable. SalesId);

qrSalesLine = new queryrun(qSalesLine);
while (qrSalesLine)
_SalesLine = qrSalesLine.get(tablenum(Salesline));
element.send(_salesLine);

}
}
??how can I get SalesLine table without creating a new qSalesLine? because
Salesline is child datasource in SalesTable datasource on report design.
Please help me. Thanks.
10 Answers

Luegisdorf

12/13/2005 10:26:00 AM

0

Hi Elton

You don't have to create a 'sub-query' if your query already contains an
embedded SalesLine-DataSource. You just have tho check what cursor has
changed if you run the query.

make it lik this one:

Query query = element.query;
Queryrun queryRun = new QueryRun(query);

;
while (queryRun.next())
{
if (queryRun.changed(tablenum(SalesTable)))
{
element.send(queryrRun.get(tablenum(SalesTable)));
}
else if (queryRun.changed(tablenum(SalesLine)))
{
element.send(queryrRun.get(tablenum(SalesLine)));
}
}

Hope this helps.
Best regards
Patrick

"elton" wrote:

>
> Salesline is child datasource in parent datasource SalesTable in a report
> design.
> my Fetch Method like this:
>
> SalesTable _salesTable;
> Query qSalesTable;
> Queryrun qrSalesTable;
>
> SalesTable _salesLine;
> Query qSalesLine;
> Queryrun qrSalesLine;
>
>
> ;
> qSalestable = element.query();
> qSalestable.dataSourceTable(tablenum(SalesTable));
> qrSalesTable = new queryrun(qSalesTable);
>
> qSalesLine = new query();
> qSalesLine.addDataSource(tablenum(SaleLine));
>
> while( qrSalesTable.next)
> {
> _salesTable = qrsalesTable.get(tablenum(SalesTable));
> element.send(_salestable);
> qSalesLine.DataSourceTable(tablenum(SaleLine)).addRange(fieldnum(SaleLine,SalesId).value(_salesTable. SalesId);
>
> qrSalesLine = new queryrun(qSalesLine);
> while (qrSalesLine)
> _SalesLine = qrSalesLine.get(tablenum(Salesline));
> element.send(_salesLine);
>
> }
> }
> ??how can I get SalesLine table without creating a new qSalesLine? because
> Salesline is child datasource in SalesTable datasource on report design.
> Please help me. Thanks.
>

elton

12/14/2005 3:29:00 AM

0

Thanks for your help so much, Luegisdorf .

But, how to Addrange to everytable in this case?
I mean value in SalesLine fieldrange get from Salestable.
I want my report result will be:

Mr A:(SalesTable)
-Item1(SalesLine)
-Item2(SalesLine)
...
Mr B:(SalesTable)
-Item1(SalesLine)
-Item3(SalesLine)
...

So do I need 2 queryruns and 2 "while statement"?
If only 1 queryrun . How to get every table in every "while statement" with
queryrun.exchanged?
Please show me the best way to do.

Example:

Query query = element.query;
Queryrun queryRun1 = new QueryRun(query);
Queryrun queryRun2 =??


queryrun1.query().dataSourceTable(tablenum(Salestable)).addRange(fieldnum(SalesTable,CustAccount)).value(queryvalue("MrA"));

while (queryRun1.next())
{
if (queryRun1.changed(tablenum(SalesTable)))
{
_SalesTable =queryrRun.get(tablenum(SalesTable));

element.send(_SalesTable );
}
while (queryRun2.next())
//else if (queryRun.changed(tablenum(SalesLine)))
{

queryrun2.query().dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(SalesLine,SalesID)).value(queryvalue(_SaleTable.SalesID));
element.send(queryrRun.get(tablenum(SalesLine)));
}
}

Thanks

Luegisdorf

12/14/2005 8:37:00 AM

0

Hi Elton

I guess you want to set a relation from salesTable to salesLine? If yes you
just have to set the property "Relations" to 'Yes' in AOT query datasource
'SalesLine'. After that you can view the relations node on query datasource
the relations 'salesTable.salesId == salesLine.salesId'.

Of course you could set this by code, but because the query is defined in a
AOT report I suppose to use this method.

If I missunderstood you please reply!

Best regards
Patrick

"elton" wrote:

> Thanks for your help so much, Luegisdorf .
>
> But, how to Addrange to everytable in this case?
> I mean value in SalesLine fieldrange get from Salestable.
> I want my report result will be:
>
> Mr A:(SalesTable)
> -Item1(SalesLine)
> -Item2(SalesLine)
> ...
> Mr B:(SalesTable)
> -Item1(SalesLine)
> -Item3(SalesLine)
> ...
>
> So do I need 2 queryruns and 2 "while statement"?
> If only 1 queryrun . How to get every table in every "while statement" with
> queryrun.exchanged?
> Please show me the best way to do.
>
> Example:
>
> Query query = element.query;
> Queryrun queryRun1 = new QueryRun(query);
> Queryrun queryRun2 =??
>
>
> queryrun1.query().dataSourceTable(tablenum(Salestable)).addRange(fieldnum(SalesTable,CustAccount)).value(queryvalue("MrA"));
>
> while (queryRun1.next())
> {
> if (queryRun1.changed(tablenum(SalesTable)))
> {
> _SalesTable =queryrRun.get(tablenum(SalesTable));
>
> element.send(_SalesTable );
> }
> while (queryRun2.next())
> //else if (queryRun.changed(tablenum(SalesLine)))
> {
>
> queryrun2.query().dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(SalesLine,SalesID)).value(queryvalue(_SaleTable.SalesID));
> element.send(queryrRun.get(tablenum(SalesLine)));
> }
> }
>
> Thanks
>

elton

12/15/2005 7:59:00 AM

0

Thanks you so much, Patrick.
Apart from the relationship beetween tables I usually filter values get from
dialog or this tables have no relation together .
as:

queryrun.query().dataSourceTable(tablenum(SalesTable)).addRange(fieldnum(SalesTable,SalesID)).value("00001");


queryrun.query().dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(SalesLine,ItemID)).value("Item1");

"00001" and "Item1" get from dialog.

Can you show me set this by code ?
Thanks :)).

Luegisdorf

12/15/2005 8:31:00 AM

0

Hi Elton

Don't know exactly what you want to see, your phrase end "... or this tables
have no relations together." confuses me a little bit with the phrase middle
".. usually filter values get from dialog". Between these both parts I can't
see nor relation too .. ;-)

Here's an example how to link a main entity and an entity like salesTable
and salesLine:

queryRun.query().dataSourceTable(tablenum(SalesLine)).relations(true);

Was this what you want to see? If it's not, just let me know what else you
desire.

Best regards
Patrick

"elton" wrote:

> Thanks you so much, Patrick.
> Apart from the relationship beetween tables I usually filter values get from
> dialog or this tables have no relation together .
> as:
>
> queryrun.query().dataSourceTable(tablenum(SalesTable)).addRange(fieldnum(SalesTable,SalesID)).value("00001");
>
>
> queryrun.query().dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(SalesLine,ItemID)).value("Item1");
>
> "00001" and "Item1" get from dialog.
>
> Can you show me set this by code ?
> Thanks :)).
>
>

elton

12/16/2005 9:32:00 AM

0

hi Patrick,

In summary, my datasource in report contains 2 tables, you
showed me use only 1 queryrun to get each table by using:

while (queryRun.next())
{
if (queryRun.changed(tablenum(SalesTable)))
{
element.send(queryrRun.get(tablenum(SalesTable)));
}
else if (queryRun.changed(tablenum(SalesLine)))
{
element.send(queryrRun.get(tablenum(SalesLine)));
}
}

Now, I want to know the way to addrange on each table without set "Yes" in
"relations" properties. Because the value in field range is got from dialog
report but not in relation fields.

queryrun.query().dataSourceTable(tablenum(SalesTable)).addRange(fieldnum(SalesTable,SalesID)).value("00001");

queryrun.query().dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(SalesLine,ItemID)).value("Item1");

can I addrange 2 tables in datasource with only 1 queryrun? How?
Help me again, please.
Thank you.


"Luegisdorf" wrote:

> Hi Elton
>
> Don't know exactly what you want to see, your phrase end "... or this tables
> have no relations together." confuses me a little bit with the phrase middle
> ".. usually filter values get from dialog". Between these both parts I can't
> see nor relation too .. ;-)
>
> Here's an example how to link a main entity and an entity like salesTable
> and salesLine:
>
> queryRun.query().dataSourceTable(tablenum(SalesLine)).relations(true);
>
> Was this what you want to see? If it's not, just let me know what else you
> desire.
>
> Best regards
> Patrick
>
> "elton" wrote:
>
> > Thanks you so much, Patrick.
> > Apart from the relationship beetween tables I usually filter values get from
> > dialog or this tables have no relation together .
> > as:
> >
> > queryrun.query().dataSourceTable(tablenum(SalesTable)).addRange(fieldnum(SalesTable,SalesID)).value("00001");
> >
> >
> > queryrun.query().dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(SalesLine,ItemID)).value("Item1");
> >
> > "00001" and "Item1" get from dialog.
> >
> > Can you show me set this by code ?
> > Thanks :)).
> >
> >

Luegisdorf

12/16/2005 3:47:00 PM

0

Hi Elton

This one works (as you've described):
queryrun.query().dataSourceTable(tablenum(SalesTable)).addRange(fieldnum(SalesTable,SalesID)).value("00001");

queryrun.query().dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(SalesLine,ItemID)).value("Item1");

In this way you get one SalesTable (00001) AND all SaleLines for sales order
00001 which has ItemId = Item1.

But do you want to get all SalesTable with order num 00001 and theirs sales
Lines and in addition all SalesTables and SalesLines where the SalesLInes
ItemId = Item1?

Or do you want just make one Query with 2 data Sources by x++?

Please be patience with me, I think I didn't see again what you mean, but
may be I've found a trace?

Best regards and a nice weekend
Patrick



"elton" wrote:

> hi Patrick,
>
> In summary, my datasource in report contains 2 tables, you
> showed me use only 1 queryrun to get each table by using:
>
> while (queryRun.next())
> {
> if (queryRun.changed(tablenum(SalesTable)))
> {
> element.send(queryrRun.get(tablenum(SalesTable)));
> }
> else if (queryRun.changed(tablenum(SalesLine)))
> {
> element.send(queryrRun.get(tablenum(SalesLine)));
> }
> }
>
> Now, I want to know the way to addrange on each table without set "Yes" in
> "relations" properties. Because the value in field range is got from dialog
> report but not in relation fields.
>
> queryrun.query().dataSourceTable(tablenum(SalesTable)).addRange(fieldnum(SalesTable,SalesID)).value("00001");
>
> queryrun.query().dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(SalesLine,ItemID)).value("Item1");
>
> can I addrange 2 tables in datasource with only 1 queryrun? How?
> Help me again, please.
> Thank you.
>
>
> "Luegisdorf" wrote:
>
> > Hi Elton
> >
> > Don't know exactly what you want to see, your phrase end "... or this tables
> > have no relations together." confuses me a little bit with the phrase middle
> > ".. usually filter values get from dialog". Between these both parts I can't
> > see nor relation too .. ;-)
> >
> > Here's an example how to link a main entity and an entity like salesTable
> > and salesLine:
> >
> > queryRun.query().dataSourceTable(tablenum(SalesLine)).relations(true);
> >
> > Was this what you want to see? If it's not, just let me know what else you
> > desire.
> >
> > Best regards
> > Patrick
> >
> > "elton" wrote:
> >
> > > Thanks you so much, Patrick.
> > > Apart from the relationship beetween tables I usually filter values get from
> > > dialog or this tables have no relation together .
> > > as:
> > >
> > > queryrun.query().dataSourceTable(tablenum(SalesTable)).addRange(fieldnum(SalesTable,SalesID)).value("00001");
> > >
> > >
> > > queryrun.query().dataSourceTable(tablenum(SalesLine)).addRange(fieldnum(SalesLine,ItemID)).value("Item1");
> > >
> > > "00001" and "Item1" get from dialog.
> > >
> > > Can you show me set this by code ?
> > > Thanks :)).
> > >
> > >

elton

12/19/2005 9:11:00 AM

0

hi Patrick,

Thanks for your enthusiasm, I only want you show me the way to addrange to
each table following your code you showed me. (I want coding, I dont want to
set the property "Relations" to 'Yes' in AOT query datasource ).

while (queryRun.next())
{
if (queryRun.changed(tablenum(SalesTable)))
{
element.send(queryrRun.get(tablenum(SalesTable)));
}
else if (queryRun.changed(tablenum(SalesLine)))
{
element.send(queryrRun.get(tablenum(SalesLine)));
}
}

I think with one queryrun I can't addrange to 2 table. How can you do that?
Thanks, Have a lucky day!

Luegisdorf

12/19/2005 3:42:00 PM

0

Hi Elton

To create a query in x++ with 2 data sources?

Query query;
QueryRun queryRun;

// create a query
query = new query();

// add MainEntity and Entity (like SalesTable and SalesLine)
query.addDataSource(tablenum(MainEntity)).addDataSource(tablenum(Entity)).relations(true);

// add a range to the MainEntity
query.dataSourceTable(tablenum(MainEntity)).addRange(fieldnum(MainEntity,
TheField)).value(queryValue('WhatEverYouWishFor'));

// add a range to the Entity (which is embedded into MainEntity)
query.dataSourceTable(tablenum(Entity)).addRange(fieldnum(Entity,
TheOtherField)).value(queryValue('WhatEverYouWishForThere'));

queryRun = new QueryRun(query);

while(queryRun.next())
{
// .. do something with the retrieved records ...
}

Is it that you are searching for?

Best regards
Patrick
"elton" wrote:

> hi Patrick,
>
> Thanks for your enthusiasm, I only want you show me the way to addrange to
> each table following your code you showed me. (I want coding, I dont want to
> set the property "Relations" to 'Yes' in AOT query datasource ).
>
> while (queryRun.next())
> {
> if (queryRun.changed(tablenum(SalesTable)))
> {
> element.send(queryrRun.get(tablenum(SalesTable)));
> }
> else if (queryRun.changed(tablenum(SalesLine)))
> {
> element.send(queryrRun.get(tablenum(SalesLine)));
> }
> }
>
> I think with one queryrun I can't addrange to 2 table. How can you do that?
> Thanks, Have a lucky day!

elton

12/21/2005 8:11:00 AM

0

yes, Patrick.
That is what I expect. Now I can use only one queryrun to retrieved records
of many tables according to any condition.
Thanks a lot.
MERRY CHRISTMAS AND HAPPY NEW YEAR!
Elton.

"Luegisdorf" wrote:

> Hi Elton
>
> To create a query in x++ with 2 data sources?
>
> Query query;
> QueryRun queryRun;
>
> // create a query
> query = new query();
>
> // add MainEntity and Entity (like SalesTable and SalesLine)
> query.addDataSource(tablenum(MainEntity)).addDataSource(tablenum(Entity)).relations(true);
>
> // add a range to the MainEntity
> query.dataSourceTable(tablenum(MainEntity)).addRange(fieldnum(MainEntity,
> TheField)).value(queryValue('WhatEverYouWishFor'));
>
> // add a range to the Entity (which is embedded into MainEntity)
> query.dataSourceTable(tablenum(Entity)).addRange(fieldnum(Entity,
> TheOtherField)).value(queryValue('WhatEverYouWishForThere'));
>
> queryRun = new QueryRun(query);
>
> while(queryRun.next())
> {
> // .. do something with the retrieved records ...
> }
>
> Is it that you are searching for?
>
> Best regards
> Patrick
> "elton" wrote:
>
> > hi Patrick,
> >
> > Thanks for your enthusiasm, I only want you show me the way to addrange to
> > each table following your code you showed me. (I want coding, I dont want to
> > set the property "Relations" to 'Yes' in AOT query datasource ).
> >
> > while (queryRun.next())
> > {
> > if (queryRun.changed(tablenum(SalesTable)))
> > {
> > element.send(queryrRun.get(tablenum(SalesTable)));
> > }
> > else if (queryRun.changed(tablenum(SalesLine)))
> > {
> > element.send(queryrRun.get(tablenum(SalesLine)));
> > }
> > }
> >
> > I think with one queryrun I can't addrange to 2 table. How can you do that?
> > Thanks, Have a lucky day!