USING SQL Automations/ Add Ins

PaddyMullaney

I am building a view in SQL which will provide data for Navision. The view is external to Navision. The aim is to get the data back into Navision, I was going to use a table and link the two, which is not a problem.

My problem is that I want to send parameters to a SQL function in order to get different results.

Looking through the automations, I am trying to find a way of building a SQL statement and passing the variables that way. It is in this case necessary to build this function outside of the Navision code.

Any ideas on how to to encase a statement such as
(select a from b where c = 'aVariable') to SQL?

Paddy

fb

Lemme see if I've got this right: -- You seem to know that:

• You could create a View in the NaviSQL db, something like this:

  CREATE VIEW dbo."<MyCompany$>MyView" AS SELECT a, c FROM b
  

• Then you could create a Navision Table with two columns 'a' and 'c' and attach it to the view.
• Finally, you could create a Navision Form with just the one column 'a', and apply the 'aVariable' filter

But you don't like that idea (perhaps because 'b' is huge, and the WHERE c = 'aVariable' filter gets you down to a manageable number of records)?

So, if that's the case, here's an idea:

• Create the Navision Table, to be used as a TEMP table (never to actually contain records)...
• In the Form OnOpen (or in some object somewhere), use ADO's Connection/Command/Recordset objects to get the records of interest, and walk thru the Recordset INSERTing records into the TEMP Navision Table...
• Run the form on the TEMP table, or override the Form's OnFindRecord/OnNextRecord triggers to display recs from the TEMP table...

???

PaddyMullaney

Yes that is pretty much it, though the reason for using the view is a little more involved.

I need to create the view in order to store dynamically special offers based upon a user defined set of criteria. These criteria are subject to change, so the rules need to be managed by the users rather than the developers, and as they have the in house skills we are hoping to provide these in an accessible way for them.

The criteria will need to take into account combinations of lines on an order, so as new line is entered it will need to re-calculate. The standard internal Navision functionality is not sufficent.

So the idea is that upon inserting/changing of records Navision needs to call a SQL statement directly using the automations. My problem is I am struggling with the automations.

Paddy

PaddyMullaney

I think that the Post

http://www.mibuso.com/forum/viewtopic.p ... ght=sqldmo

maybe what I am looking for. Thanks fb