Building a query on a form problem

[Abraham Zwygart]

I am in AX2009 using a form. There are four tables
InventTable, InventDim, InventSum and InventPieceSum

In the InventDim and InventSum I added a init method to these tables
Something like:
public void init()
{
;
super();

this.query().dataSourceTable(tablenum(InventSum)).addRange(fieldnum(InventSum, Closed)).value('0');

}

And this works Great.
On InventPieceSum I am linking back to InventSum and it needs to link by the
ItemId and InventDimId. It always links by itemId (even if I reverence a key
that has both fields), so I thought I could add something like this in it’s
init method:

this.query().dataSourceTable(tablenum(InventPieceSum)).addRange(fieldnum(InventPieceSum, InventDimId)).value(queryValue(InventSum.InventDimId));

I get the following query resualts:
SELECT * FROM InventTable
JOIN * FROM InventSum WHERE InventTable.ItemId = InventSum.ItemId AND
((Closed = 0))
JOIN * FROM InventDim WHERE InventSum.InventDimId = InventDim.inventDimId
AND ((NOT (InventLocationId = ' ')))
JOIN * FROM InventPieceSum WHERE InventSum.ItemId = InventPieceSum.ItemId
AND ((InventDimId = ' '))

The ‘(InventDimId = ' ')’ need to be ‘(InventPieceSum.InventDimId =
InventSum.InventDimId ')

How do I do this??

Thanks for helping
Abraham Z

[amir nazim]

You should replace the addrange on table InventPieceSum with addLink method,
code will be like below. AddRange is something like where clause of the query
not the relation.

this.query().dataSourceTable(tablenum(InventPieceSum)).addLink(fieldnum(InventPieceSum, InventDimId), fieldNum(InventSum.InventDimId));

Thanks
Amir
Blog:http://msdax.wordpress.com/

Post by Abraham Zwygart
I am in AX2009 using a form. There are four tables
InventTable, InventDim, InventSum and InventPieceSum
In the InventDim and InventSum I added a init method to these tables
public void init()
{
;
super();
this.query().dataSourceTable(tablenum(InventSum)).addRange(fieldnum(InventSum, Closed)).value('0');
}
And this works Great.
On InventPieceSum I am linking back to InventSum and it needs to link by the
ItemId and InventDimId. It always links by itemId (even if I reverence a key
that has both fields), so I thought I could add something like this in it’s
this.query().dataSourceTable(tablenum(InventPieceSum)).addRange(fieldnum(InventPieceSum, InventDimId)).value(queryValue(InventSum.InventDimId));
SELECT * FROM InventTable
JOIN * FROM InventSum WHERE InventTable.ItemId = InventSum.ItemId AND
((Closed = 0))
JOIN * FROM InventDim WHERE InventSum.InventDimId = InventDim.inventDimId
AND ((NOT (InventLocationId = ' ')))
JOIN * FROM InventPieceSum WHERE InventSum.ItemId = InventPieceSum.ItemId
AND ((InventDimId = ' '))
The ‘(InventDimId = ' ')’ need to be ‘(InventPieceSum.InventDimId =
InventSum.InventDimId ')
How do I do this??
Thanks for helping
Abraham Z

[Abraham Zwygart]

Thank you so much.
This got me on the right track. My final results was to remove the table
InventPriceSum off of the Data Source and add the following code to the init
method the InventSum table:

//BP Deviation documented
public void init()
{
QueryBuildDataSource qbds1;
QueryBuildDataSource qbds2;
Query query;
;

super();
// only select if not Closed

this.query().dataSourceTable(tablenum(InventSum)).addRange(fieldnum(InventSum, Closed)).value('0');

query = this.query();
qbds1 = query.dataSourceName(this.name());
qbds1 = qbds1.addDataSource(tablenum(AKRInventPieceSum));
qbds1.addLink(fieldnum(InventPieceSum, ItemId), fieldNum(InventSum,
ItemId));
qbds1.addLink(fieldnum(InventPieceSum, InventDimId), fieldNum(InventSum,
InventDimId));
qbds1.joinMode(JoinMode::OuterJoin);

}

Thanks again for your help
Abraham Z.

Post by amir nazim
You should replace the addrange on table InventPieceSum with addLink method,
code will be like below. AddRange is something like where clause of the query
not the relation.
this.query().dataSourceTable(tablenum(InventPieceSum)).addLink(fieldnum(InventPieceSum, InventDimId), fieldNum(InventSum.InventDimId));
Thanks
Amir
Blog:http://msdax.wordpress.com/

Post by Abraham Zwygart
I am in AX2009 using a form. There are four tables
InventTable, InventDim, InventSum and InventPieceSum
In the InventDim and InventSum I added a init method to these tables
public void init()
{
;
super();
this.query().dataSourceTable(tablenum(InventSum)).addRange(fieldnum(InventSum, Closed)).value('0');
}
And this works Great.
On InventPieceSum I am linking back to InventSum and it needs to link by the
ItemId and InventDimId. It always links by itemId (even if I reverence a key
that has both fields), so I thought I could add something like this in it’s
this.query().dataSourceTable(tablenum(InventPieceSum)).addRange(fieldnum(InventPieceSum, InventDimId)).value(queryValue(InventSum.InventDimId));
SELECT * FROM InventTable
JOIN * FROM InventSum WHERE InventTable.ItemId = InventSum.ItemId AND
((Closed = 0))
JOIN * FROM InventDim WHERE InventSum.InventDimId = InventDim.inventDimId
AND ((NOT (InventLocationId = ' ')))
JOIN * FROM InventPieceSum WHERE InventSum.ItemId = InventPieceSum.ItemId
AND ((InventDimId = ' '))
The ‘(InventDimId = ' ')’ need to be ‘(InventPieceSum.InventDimId =
InventSum.InventDimId ')
How do I do this??
Thanks for helping
Abraham Z

[Abraham Zwygart]

Hi again,
Getting back to the project again:)

Question: 1
How do I retrieve/print information from the 'InvoicePriceSum' table??
Overview: This is the table using:
qbds1 = qbds1.addDataSource(tablenum(InventPieceSum));
qbds1.addLink(fieldnum(InventPieceSum, ItemId), fieldNum(InventSum,
ItemId));
qbds1.addLink(fieldnum(InventPieceSum, InventDimId), fieldNum(InventSum,
InventDimId));
qbds1.joinMode(JoinMode::OuterJoin);

Part 2
I have a button that goes out and select all InventTable entries via
different attributes. When it comes back how do I re-apple the query changes
on the new selection?

Thanks for you help
Abraham Z.

PS: most of the new Tables and fields that I create have 'AKR' in front. I
try to remove them before posting code.

Post by amir nazim
You should replace the addrange on table InventPieceSum with addLink method,
code will be like below. AddRange is something like where clause of the query
not the relation.
this.query().dataSourceTable(tablenum(InventPieceSum)).addLink(fieldnum(InventPieceSum, InventDimId), fieldNum(InventSum.InventDimId));
Thanks
Amir
Blog:http://msdax.wordpress.com/

Post by Abraham Zwygart
I am in AX2009 using a form. There are four tables
InventTable, InventDim, InventSum and InventPieceSum
In the InventDim and InventSum I added a init method to these tables
public void init()
{
;
super();
this.query().dataSourceTable(tablenum(InventSum)).addRange(fieldnum(InventSum, Closed)).value('0');
}
And this works Great.
On InventPieceSum I am linking back to InventSum and it needs to link by the
ItemId and InventDimId. It always links by itemId (even if I reverence a key
that has both fields), so I thought I could add something like this in it’s
this.query().dataSourceTable(tablenum(InventPieceSum)).addRange(fieldnum(InventPieceSum, InventDimId)).value(queryValue(InventSum.InventDimId));
SELECT * FROM InventTable
JOIN * FROM InventSum WHERE InventTable.ItemId = InventSum.ItemId AND
((Closed = 0))
JOIN * FROM InventDim WHERE InventSum.InventDimId = InventDim.inventDimId
AND ((NOT (InventLocationId = ' ')))
JOIN * FROM InventPieceSum WHERE InventSum.ItemId = InventPieceSum.ItemId
AND ((InventDimId = ' '))
The ‘(InventDimId = ' ')’ need to be ‘(InventPieceSum.InventDimId =
InventSum.InventDimId ')
How do I do this??
Thanks for helping
Abraham Z