Table calling other tables

Alkro

Hi!

I have an idea, but i don´t know how to implement...

I want create a table with other table fields. Explain.

New Table
Field1
Field2
Field3


Navision Standar Table1
Field1
Field2
Field50 [Option]


Navision Standar Table2
Field1
Field3



New table must have all NSTable1 records with a particular Field50 Option.
Too must have all NSTable2 records (Field3).

3 Tables have Field1 with Key.

Why? Because i need a list form with Field1, Field2 and Field3, but only records with Field50 with Option1.

I don't find the way...

Belias

can you link ns1 and ns2 in some way?if so, it's easy...if you can't link those tables it is impossible

Alkro

Belias wrote:

can you link ns1 and ns2 in some way?if so, it's easy...if you can't link those tables it is impossible

Yes. NS1 and NS2 can linked with Field1 and other Filed more.

Easy?? .... :oops:

Belias

ok, you should create a new table with your fields, then create a form, based on a temptable (if you're in version 5.00 or above)...obviously the table must be the one you've created.

trigger onopenform: fill this temptable. in this way:

temptable.reset;
temptable.deleteall;
nstable1.setrange(myoption,myoption::option1);
if nstable1.findset then begin
  repeat
    temptable.init;
    temptable.field1 := nstable1.field1; //no problem for this field
    temptable.field2 := nstable1.field2; //no problem for this field
    nstable2.setrange(field1,nstable1.field1);
    //add other filters if necessary
    if nstable2.findset then begin
      repeat
        temptable.field3 := nstable2.field3;
        temptable.insert;
      until nstable2.next = 0;
    end;
  until nstable1.next = 0;
end;

i wrote temptable, but you should use rec variable of the form instead.
also, if you have a 1 on 1 relation between ns1 and ns2 you don't need the second repeat-until (because you have only one combination of field1,field2 and field3)

p.s.: this method is good if you don't need to insert/modify records in the temptable.
if you need a static table, there is a way for sure, just ask if you need

garak

other way if you are on sql.

you can create a view in your dateabase if you work on sql server. then you create a table in navision and set property linkedobject. See also in documents on CD/DVD there are examples. Also note, if you use a view. If you create / delete / modify a company you must also alter your view (the select statement in the view), but these is not a problem and very easy.

if you work under native database you can use the temptab solution.

Regards

Alkro

Works fine!

\:D/

Alkro

Belias wrote:

if you need a static table, there is a way for sure, just ask if you need

Curiosity...

I ask How can i do if a need static table?

Best Regards

Belias

you won't base the form on a temporary table and you write in the onopen (assuming that the key is field1,field2,field3):

temptable.reset; 
//temptable.deleteall; 
nstable1.setrange(myoption,myoption::option1); 
if nstable1.findset then begin 
  repeat 
    temptable.init; 
    temptable.field1 := nstable1.field1; //no problem for this field 
    temptable.field2 := nstable1.field2; //no problem for this field 
    nstable2.setrange(field1,nstable1.field1); 
    //add other filters if necessary 
    if nstable2.findset then begin 
      repeat 
        temptable.field3 := nstable2.field3; 
        if temptable.insert then;          //to insert only non-existing records 
      until nstable2.next = 0; 
    end; 
  until nstable1.next = 0; 
end;

in this way the form will open after a few seconds...for a large amount of records it's better to add a button in the form ("update records") and call the above function in the onpush.
for this second method, don't put the code in the onopenform

edit:

you can create a view in your dateabase if you work on sql server. then you create a table in navision and set property linkedobject.

this is static and auto updating

Alkro

Is a good way, thanks a lot, but when i tested in a real DB, form is very, very slow...

I have 10.000 records in nstable1, and 20 records (by nstable1 record) in nstable2...

I need a static table, and a process that fill this table when user Register an Output.

Then, when open the new Form, only make calculation between fields.

Regards

Belias

I need a static table, and a process that fill this table when user Register an Output.

Then, when open the new Form, only make calculation between fields.

I can't understand these points, sorry... [-o<