executing a query in navision

jks

Hi all,

Is it possible to retrieve the records from more than one table based on some join condition in navision(without using automation variable)?

DenSter

You can't use SQL if that's what you mean. It is possible to set filters and display rows of data in Navision, if you know how to program it. Let us know what it is exactly what you need and we'll help you along.

jks

What i want is, i have two tables in navision e.g A and B. There is a relation between these two tables.
Now On a form i have placed one button. Inside Onpush trigger of this button i want to retrieve some records by joining these two tables based on some join condition.

For this do i need to create an variable of type automation and then connect to sql server using connection string(ie. provider, datasource, initial catalog etc)?

Isn't there any facility in navision so that i can do the same?

Please help

Eugene

whether you can perform a join depends on the exact relation of your tables - what exactly the join condition is ?

jks

query is
select * from B where B.userid=A.userid;

DenSter

B.RESET;
B.SETFILTER(userid,'=%1',A.userid);
IF B.FIND('-') THEN BEGIN
  // do some of your stuff here
  REPEAT
    // loop through the records
  UNTIL B.NEXT = 0;
END;

jks

It worked.

Thanks

DenSter

You're welcome, glad I could help

jks

Hi all,

Still one problem is there.
I wrote following c/al code

btable.RESET;
atable.RESET;
btable.SETFILTER(User_Id,'=%1',atable.UserID);
MESSAGE('count is %1',btable.COUNT);
IF btable.FIND('-') THEN BEGIN
REPEAT
MESSAGE('value is %1',btable.Record_Id);
UNTIL btable.NEXT = 0;
END;

Now eventhough atable and btable have matching records on userid, btable.count gives zero i.e btable is empty.

User id is of type text(30) in both the tables. Do i need to do some formating of the text?

Please help.

jks

Ok now i got the solution:
We need to go through a loop.

FOR i:=1 TO btable.COUNT
DO BEGIN
btable.SETFILTER(User_Id,'=%1',atable.UserID);
btable.NEXT;
atable.NEXT;
END;

Thanks

DenSter

No that's not right. If you want to have an accurate count of the numer of records within your filter, you do use the COUNT command, but not where you put it. Put it AFTER you do FIND, like so:

B.RESET;
B.SETFILTER(userid,'=%1',A.userid);
IF B.FIND('-') THEN BEGIN
  // do some of your stuff here
  MESSAGE('count is %1',B.COUNT);
  REPEAT
    // loop through the records
  UNTIL B.NEXT = 0;
END;

THe way you're doing it is COUNT returns the number of records without a filter, and for each on you set another filter. You have to keep the sequence of your statements in order. You can't COUNT the records until after you retrieve them. Similarly, you can't retrieve your records until after you set the correct filters.