new Menu item

doubtfire

How could I link a SQL stored procedure to a new Menu item ? Thanks.

kapamarou

Create a Codeunit that executes the procedure and add it to the menu...

doubtfire

I have the SP named as [Get customer last year stats], could you please
simply show me where and how I should plug the code in the code unit.
Thank you very much ! I am very new to Navision coding and do not have formal training.

kapamarou

By default Navision does not allow you to interact with SQL. You will have to use an ADOConnection. Search this forum for ADOConnection, see how it is used, and then create a Codeunit / Form / Report (Anything that suites you) to call the object that will actually use the ADOConnection. Be careful though since with ADO you are "Outside of Navision" (No flowfields, etc...)

Eugene

the question really is why do you put business logic coding into MS SQL server and not into Navision client ?

Have you investigated the possibility of creating a VIEW in MS SQL and defining a navision table object that would be linked to that view ?

doubtfire

Doing this, is it possible for the users to run to get the latest data !? Any better idea, or doing a report as an object to let users run whenever they prefer !? Thanks.

DeputyDogh

doubtfire wrote:

I have the SP named as [Get customer last year stats], could you please
simply show me where and how I should plug the code in the code unit.
Thank you very much ! I am very new to Navision coding and do not have formal training.

I've tried to interact with SQL by using the following trick:

Create a trigger in SQL which fires when a field/record in a table changes. Use a table which is almost never changed like a Setup table (or a new table)
In this trigger you place the statements you wish to execute. In navision you change the value in the field. SQL with activate the trigger and the SQL statements will be executed. You could use the value which was entered in the field to decide which SQL statement will be executed.

To make it a little more userfriendly I created a button "Update Statistics" which did nothing more then changing the value in a new boolean field "Start Statistics" into TRUE in the SQL trigger I put "Start Statistics" back to FALSE.

There is a little downside :
The Navision client will be waiting for the execution of the trigger to complete zo the client seems to be "hang" or be "blocked" for a moment.

doubtfire

Thank you for the suggestion. Would it be possible to put as a report to have a form for displaying the Customer statistics status like running/ending ? (This is a customized statistics and NOT like the original statistics offered by Navision, so there is new field on the Customer form) Thanks.