UNION Query in NAV's dataitem

xman2u

To whom it may concern,

i'm facing a problem in microsoft navision on 'Dataitem' portion:

Question:
How can i do 'UNION' tables in dataitem. In normal SQL Query,
we can use union statement to combine multiple recordset. So how to do in NAV?

This is because i already have a dataitem that join the sale invoice header & sale invoice line, so
now i would like to UNION another recordset (A) to this dataitem where the recordset (A) should able
to perform the following query:

e.g.: select * from tableA where field1 not in (select field1 from tableB where field2='abc')

By the way, can we use custom query for dataitem in NAV?

kine

1) Can you describe by words what you want to do? It is good to clear the meaning of all that around...
2) You can use variables to find out if something exists or not and skip the record in the dataiem if...
3) You can use the nested dataitem which selects the data related to the parent dataitem, and you can use the ShowIfDetails property of the parent to show just records having some record in the nested dataitem...

garak

You will find all records from table A whose field 1 is not in table B where there is in field 2 of tableB the value 'abc' :?:

Your dataItem is the tableA

then in DataItem you type following.

//These can also be develop with an Sub Dataitem with using Link Property
TableB.setrange(Field1,TableA.Field1);
if TableB.isempty then
CurrReport.skip; /There is no Rec in TabB with the same value in Field 1


TableB.setrange(Field2,'abc');
if not TableB.isempty then
currReport.Skip; //skip the current record in TableA if there is no one in TableB with 'abc in field 2 of table B

These gives only out record from TableA whose have in TableB not the value 'abc' in field 2

regards

xman2u

Hi garak,

What you have mentioned is what i'm looking at, but can we UNION (combine) the 2 dataitem become 1 dataitem?

E.g.:

select a,b from tableA
union all
select a,b from table B

jonsan21

If it's in a report, just loop 2 times, once for the table A, and once for table B. So instead of 1 data item, you will have 2 dataitems, both non indented. Once you looped through the first dataitem, you will loop the though the second one. This should produce the same result as UNION.

The tricky part is if you want to do it in a form. Thanks to the new functionality in 5.0, we can easily have a form with temporary table. You can create a physical table as a union of table A and B and set the form to link to this temporary table and set the SourceTableTemporary property of the form to Yes. If you're using below 5.0, look into this post:
http://www.mibuso.com/howtoinfo.asp?FileID=16

I hope I'm making any sense...

Rgds,

Jon.