Query Data in one form

Hello everybody I want to create a form in Navision from the query that mix 4 table.How can I do ? In Access ,It is very easy to create query.

Somebody please correct me if I’m wrong - I always feel somewhat reluctant to say “this is not possible”, because someone else might actually know a way to do it [;)] Navision doesn’t have queries. [B)] Mixing data from several tables in a form can be achieved in three ways: By using FlowFields, with Matrix controls or by having a form with subform(s). (Anybody know some other way???) In either case, you may combine a “master table” with several other tables, but this is not transitive, i.e. you may go from table A to table B, but not further on from B to C etc. You could try and create a subform which itself includes further subforms, but this will get rather messy in no time. [xx(] And nothing of the above will give you the look & feel and the behavior of an SQL query. Reports are more powerful and allow you to combine data from an arbitrary number of tables in as many ways as you like. The SQL approach of JOINing several tables together into a “view” or “virtual table” is not the way Navision works…

Hi I don’t know how much data you have in these four tables but perhaps here is another way: I assume there is a link between the tables. - create an own temporary table - create a form for this table - transfer only the fields you want see on the form from the four basic tables to your temporary table on runtime (before you open the form) As I said this only an idea. bye André

Rather than working out how you want to get there, start by deciding where you want to go. Basically you are try to get ideas of how to turn left where there is a NO LEFT TURN sign. Just let us know where you want to go, and someone can give you a short cut. Can you give some detail of what it is you are trying to achive?

hi This is no direct way to write queries in Navision. If u want to display data from four tables use flow fields on the source table of the form (see the customer table the balance field is coming from Customer ledger entry table). Or u create a table and insert the desired data using SETRANGE or SETFILTER on the four tables, and use THIS table as the soruce table for the form. hope this will help. Hari

Hi Andr’ What do you mean? My own temporary table. It 's mean create a new table in database.I feel confuse temporary table in Navision.



Originally posted by Mena
Hi Andr’ What do you mean? My own temporary table. It 's mean create a new table in database.I feel confuse temporary table in Navision.

Sorry to misleading you. I thought about a real empty table- but as I read the other ‘Temp table’ thread I’m not sure anymore. Perhaps you can use also a real ‘temporary’ table to make this. bye André

What we have done to replicate table joins is create a Template table with about Ten fields for each datatype labeled Decimal 1 , Decimal 2, Code1 etc… This allows us to create a temporary instance of that table and load whatever fields we want to it for use while the report, form, etc… are running. Not the most elegant solution but it does serve its purpose. Rgds, Owen

Hi Owen


Originally posted by mcoAggie
… Not the most elegant solution but it does serve its purpose. …

For me is this solution (like my table) much more elegant than other possible ways in Navision [;)]. Some people here may don’t think so - but who cares[:D]. bye André

Can I add a new field in the temporary instance table?

Yes, just add it to the actual table. It then becomes available like all the other fields.

If I don’t want to add to the actual table,Is it possible to do it?

It is, but very messy. Why would you NOT want to add fields to the table?

No I don’t want to customize anything on the table.

Mena, You could base your form on one of the four tables as normal and then declare some form level variables that are populated with values from the three other tables in the OnAfterGet record trigger of the form. This works only if you want to display the values in the three other tables but if you want to be able to modify them as well then you will need to write some code in the OnAfterValidate trigger of the text boxes containing the form level variables to effect the change in the source table. I hope this helps. Chris.