Accessing flowfields from VB.NET (SQL Server)

KRISHNA

Hi: my company is using Navision Attain v3.6 on SQL Server. It seems very easy to access data from the SQL server except I can't figure out how to access the flowfield data. For example I'd like to access the customer's current balance. And later an item's inventory availability.
Is the data in separate tables?
Is it possible to access via VB.NET?
Thanks,

Marco_Ferrari

As far as I know you cannot access a FlowField from SQL Server if you don't use the Navision Client, because those fields are not visible. So the only way I know is to calculate the value using the SIFT Tables created in the SQL database.
If you don't create SIFT tables for calculating FlowFields then I cannot help you!

Just read the manual in order to know how is the structure of a SIFT table.

Bye,
Marco

sabato

Hey,

well first of all you can't Access the Flowfield, because they are Runtime calculated field. therefore they only exist if you work with them in navision.

But you could use the NAS (Navision Application Server) to Acces these Fields. If you are planning to do this, i could helo you a little more.

The method of using the sift tables is a bit complicated. And i don't really know if you can access these tables. But if you can, then just go ahead

Greetings

dtruppi

Acces via NAS seems interesting, sabato, please go on...


Krishna, with VB.NET you can use C/font dll, or C/OCX, seek for the appropriate documentation in the Attain CD.

8)

sabato

Hey,
well first of all, the NAS is not more than a Navision CLient without the Graphickal Interface. In Codeunit 1 you can implement different Procedures for the NAS. The Function ist called NAS Handler.
You can describe what the NAS has to do when it gets incoming querys from Outside Navision.
Just to give you a small Example. You sad you wanna have the Balace for a specific Customer.
First of all we need to establish a connection. I'm using a Socket Connection.
This is the codeunit:

OnRun()
//Check if ComCOm is clear
  IF ISCLEAR(ComCom) THEN
    IF NOT CREATE(ComCom) THEN
      ERROR('Failed to create Communication Component');
//Check if Socket Bus Adapter is clear
  IF ISCLEAR(SBA) THEN
    IF NOT CREATE(SBA) THEN
      ERROR('Failed to create SBA');

// Add the SBA to the Comunication Component
  ComCom.AddBusAdapter(SBA, 0);

//Open the Socket on Port 8085, the name of the Socket ist mysocket
SBA.OpenSocket(8085, 'mysocket');
SBA.ReceivingTimeout := 20000;

ComCom::MessageReceived(VAR InMessage : Automation "''.IDISPATCH")

//Displays a Message if something happens
  MESSAGE('Message Received!');

  InMsg := InMessage;
  InStr := InMsg.GetStream();
//Just a small Function which gets the Balace(LCY) from the given Customer
  InStr.READTEXT(str);
  IF str <> '' THEN BEGIN
    re_customer.SETRANGE("No.", str);
    IF re_customer.FIND('-') THEN BEGIN
      re_customer.CALCFIELDS("Balance (LCY)");
      saldo := re_customer."Balance (LCY)";
    END;
  END;

//Create Reply
OutMsg:= InMsg.CreateReply; 

//Write something into the OutStream 
OutStr:= OutMsg.GetStream();

//Copy Incoming Msg into Outgoing Msg 
OutStr.WRITETEXT(STRSUBSTNO('Debitor # '+ str + ' ' + '%1',FORMAT(saldo)));

//Send Reply Msg - the 0 means send it right away.
OutMsg.Send(0); 
InMsg.CommitMessage(); 

The other Code is a PHP Code which sends the Number 10000 to Navision

<?
//Simply creates the Socket Connection to Port 8085
$fp = fsockopen ("10.100.4.98", 8085, $errno, $errstr, 30);
if (!$fp) {
   echo "$errstr ($errno)<br />\n";
} else {
   //If Connection is established, send the Numer 10000 to Navision
     gzwrite($fp,10000);
   while (!feof($fp)) {
       //Gets the Reply Msg
     echo gzgets($fp,128);
   }
//Close Socket
   fclose($fp);
}
?>

Hope this helps a bit. Its Quite easy to work with Navision from Outside the Program. This Example runs without the NAS. The NAS implementation would just be another step. You would have to change CU 1.
Because right now, you only get the Balance(LCY), but if you want Navision to do more you just have to add SOme Parameter to CU1.
For Example:

  CASE Parameter OF
    'SBA':
      BEGIN
        CODEUNIT.RUN(CODEUNIT::"Socket Server");
      END;

If the NAS gets the Parameter SBA it runs the Codeunit "Socket Server".
And you can define x Parameter, so just try it an have fun.


Hope that helps a bit, to understand the COnnection Problems
Otherwise just let me know and I will help some more.

Greetings
sabato

dtruppi

:shock:
Very interesting examples, sabato (uh, my name is domenico and for the italian language is the next day after sabato! I mean, sabato=saturday, domenica=sunday...curious, isn't it?).

But some problems still remain... there's no simple way to get from sql server the flowfields as in navision native db.

...Damn it! :evil:

sabato

No there is not a simple way, but I would use the NAS. As i told you before. But I dont know if you have an NAS.

Another way is you do a Subselect Query to the SQL Database. Just create a query like Navision would do anyways.
Well this is not working properly but something like this should help you.

SELECT 
  table1.Nr_ , 
  table1.Name, 
  table1.Adresse, 
  table1.City, 
  Sum (table2.Amount(LCY)) as Saldo 
FROM
  Customer AS table1 , Detailed Cust. Ledg. Entry AS table2";
WHERE
  'table2.Nr_' = 'table1.Debitorennr_' 
AND 
  'Initial Entry Global Dim. 1' = 'table1.Global Dimension 1 Filter 
AND
  'Initial Entry Global Dim. 2' = 'Global Dimension 2 Filter'
AND 
  'table2.Currency Code' = 'table1.Currency Filter'";

This is an SQl Query, but it doesnt work right know, but something like this schould do it, the only problem is the performance. It takes quite a while to load the site then.
Maybe you can fix my problem, but I dont have all the time to fix the query problem right now.
Like usal -> hope this helps


Yeah its quite a coinsidence with the name. But very funny. How's it going in italy anyways (temparture?).

Greetings
sabato

// edit something

dtruppi

I agree,
but i haven't NAS and (for me) NAS is another cost for the customers.

My task is to simplify the life of my customers' internal EDPs, to reach that goal the simplest way is to build some stored procedures or views for them.

I hope that MBS will develop a sort of C/ODBC driver that will see all the flowfield in a simple way for SQL server, too. :roll:

P.S.
In Italy it's cold now, but in south italy (where i live) a little less, 10° C at now.

Bye!

KRISHNA

Thanks for all the replies. I was kind of surprised since I didn't get any replies for a few days. Now I find so many good ideas which I will have to study.