Classic forms, reports and dataports ..... They will be gone in V7. In V7 they are replaced by more mordern concepts: Pages, RDL reports and XML-port.
Original Forum Link Below Check this out.
Mircosoft Dynamics NAV, Navision, Sri Lanka, ERP ,Technical , Knowledge Base
Thursday, January 26, 2012
Tuesday, January 24, 2012
Destination root by Online Map in Dynamics NAV 2009 R2
I got a requirement to develop to show the destination root.
Sales order contain Customer Ship to City between Item Location Code (city of
the address).
Created a function in Sales Line table called DisplayMap Define
Local Variables
Name DataType Subtype
MapPoint Record
Online Map Setup
MapMgt Codeunit Online Map Management
RVCustomer
Record Customer
RVLocation Record Location
DisplayMap Function Contains the below code.
GetSalesHeader;
IF
RVCustomer.GET("Sell-to Customer No.") THEN;
IF
Rvlocation.GET("Location Code") THEN;
IF
MapPoint.FIND('-') THEN
MapMgt.MakeSelection1(DATABASE::"Sales
Header",SalesHeader.GETPOSITION,Rvlocation.GETPOSITION,"Location
Code")
ELSE
MESSAGE(Text50000);
{Text50000 -
Contains
’ Before you
can use Online Map, you must fill in the Online Map Setup window.\See Setting
Up Online Map in Help.’
}
In Online Map Management you have
to create a new function MakeSelection1 .
MakeSelection1(TableID
: Integer;Position : Text[1000];LocationPosition : Text[1000];LocationCode : Code[20])
MapSetup.GET;
IF
MapSetup."Default Language Code" = '' THEN
ERROR(STRSUBSTNO(Text003,MapSetup.FIELDCAPTION("Default Language
Code")));
SelectAddress1(TableID,Position,1,MapSetup."Distance
In",MapSetup.Route,LocationPosition);
In Online Map Management you have
to create a new function SelectAddress1
SelectAddress1(TableNo
: Integer;RecPosition : Text[1000];Direction : 'To Other,From Other,To
Company,From Company';Distance : 'Miles,K
IF Direction
IN [Direction::"To Other",Direction::"From Other"] THEN
BEGIN
OnlineMapSetup.GET;
Distance :=
OnlineMapSetup."Distance In";
Route :=
OnlineMapSetup.Route;
END ELSE BEGIN
OnlineMapSetup.GET;
CompanyInfo.GET;
END;
CASE Direction
OF
Direction::"To Other":
BEGIN
ProcessDirections(
TableNo,RecPosition,
OnlineMapAddressSelector.GetTableNo,OnlineMapAddressSelector.GetRecPosition,
Distance,Route)
END;
Direction::"From Other":
BEGIN
ProcessDirections(
DATABASE::Location,LocationCode,
TableNo,RecPosition,
Distance,Route);
END;
Direction::"To Company":
BEGIN
ProcessDirections(
TableNo,RecPosition,
DATABASE::"Company
Information",CompanyInfo.GETPOSITION,
OnlineMapSetup."Distance
In",OnlineMapSetup.Route);
END;
Direction::"From Company":
BEGIN
ProcessDirections(
DATABASE::"Company
Information",CompanyInfo.GETPOSITION,
TableNo,RecPosition,
OnlineMapSetup."Distance
In",OnlineMapSetup.Route);
END;
END
Create an action to Call DisplayMap
Function in Sales Line Table.
Wednesday, January 18, 2012
Math Functions in Dynamics NAV
I have created the Math functions codeunit 50010
Use when you want any calculations using Math functions
OBJECT Codeunit 50010 Math Functions
{
OBJECT-PROPERTIES
{
Date=31/01/11;
Time=[ 6:59:10 PM];
Modified=Yes;
Version List=Math_SR;
}
PROPERTIES
{
OnRun=BEGIN
END;
}
CODE
{
PROCEDURE
"!"@1000000000(x@1000000000 : Decimal) Factorial : Decimal;
BEGIN
Factorial := x;
WHILE x > 1 DO
BEGIN
x -= 1;
Factorial :=
Factorial * x
END;
END;
PROCEDURE
Sin@1000000001(x@1000000000 : Decimal) : Decimal;
BEGIN
EXIT(x*(1 - (x*x)*(1 -
(x*x)*(1 - (x*x)*(1 - (x*x)*(1 - (x*x)*(1 - (x*x)*(1 - (x*x)*(1 - (x*x)
/(16*17) )/(14*15)
)/(12*13) )/(10*11) )/(8*9) )/(6*7) ) / (4*5) ) /(2*3) ));
END;
PROCEDURE
Cos@1000000003(x@1000000000 : Decimal) Cos : Decimal;
BEGIN
EXIT(Sin(x+Pi/2));
END;
PROCEDURE
Tan@1000000013(x@1000000000 : Decimal) : Decimal;
BEGIN
EXIT(Sin(x) / Cos(x));
END;
PROCEDURE
ArcSin@1000000005(x@1000000000 : Decimal) a : Decimal;
BEGIN
IF x < 0 THEN
EXIT(-ArcSin(-x))
ELSE
IF x = 1 THEN
EXIT(Pi / 2)
ELSE
EXIT(ArcTan(x /
Sqrt(1 - x * x)));
END;
PROCEDURE
ArcCos@1000000006(x@1000000000 : Decimal) a : Decimal;
BEGIN
IF x < 0 THEN
EXIT(Pi -
ArcCos(-x))
ELSE
IF x = 0 THEN
EXIT(Pi / 2)
ELSE
EXIT(ArcTan(Sqrt(1 -
x * x) / x));
END;
PROCEDURE
ArcTan@1000000008(x@1000000000 : Decimal) ATan : Decimal;
VAR
Sum@1000000001 :
Decimal;
i@1000000002 :
Integer;
Inverting@1000000003 :
Boolean;
BEGIN
Inverting := (x < -1) OR (x > 1);
IF Inverting THEN
x := 1/x;
CLEAR(ATan);
FOR i := 1 TO 100 DO
BEGIN
ATan += (POWER(x,i)
/ i) - (POWER(x,i+2) / (i+2));
i += 3;
END;
IF Inverting THEN
IF x > 0 THEN
EXIT(Pi/2-ATan)
ELSE
EXIT(-Pi/2-ATan)
END;
PROCEDURE
Pi@1000000004() : Decimal;
BEGIN
EXIT(3.14159265358979323);
END;
PROCEDURE
Sqrt@1000000009(x@1000000000 : Decimal) : Decimal;
BEGIN
EXIT(POWER(x,0.5));
END;
BEGIN
{
Austral Sameera
23.12.2010 -- Math Functions to Use
Store Locator
}
END.
}
}
Monday, January 16, 2012
Access MS SQL Database from MS Dynamics NAV Native Database
One of my clients wants to synchronize data with different
database.
01 ) I Created a new
codeunit and created below new global/Global variables
RsReceiveData -- Automation -- 'Microsoft ActiveX Data
Objects 2.7 Library'.Recordset
RsFReceiveData --Automation -- 'Microsoft ActiveX Data Objects 2.7
Library'.Fields_Deprecated
FieldReceiveData -- Automation-- 'Microsoft ActiveX Data Objects 2.7 Library'.Fields
ADO -- Automation-- 'Microsoft
ActiveX Data Objects 2.7 Library'.Connection
01)
Connection Open.
You should Create 4 fields in a setup
table. I created in Synchronize Setup(Customize new table)
SynchSetup.GET;
SynchSetup.TESTFIELD("SQL Server
Name");
SynchSetup.TESTFIELD("Database
Name");
SynchSetup.TESTFIELD("User ID");
CREATE(ADO);
ADO.Open('Provider=SQLOLEDB.1;initial
catalog=' + SynchSetup."Database
Name" + ';' +
'data source=' + SynchSetup."SQL Server Name" +
';' +
'user id=' + SynchSetup."User ID" + ';' +
'password=' + SynchSetup.Password);
ADO.CommandTimeout(600);
02)
After
this you should write the sql update sql command as below.
//Open RecordSet
CREATE(RsReceiveData);
RsReceiveData.Open ('Select isnull(t11_010,'''') as "Vendor
Code",isnull(t11_020,'''') as "Store Code",isnull(t11_030,'''')
as "Invoice date",' +
'isnull(t11_050,'''') as "Invoice Number",
isnull(t11_090,'''') as "Item Group", isnull(t11_100,'''') as
"Item Code",' +
'convert(char(20),isnull(t11_170,0)) as "Real Receive Amount",
isnull(t11_220,'''') as "Order Number",' + 'isnull(t11_040,'''') as "Receive
Div",isnull(t11_900,'''') as "Update Div",isnull(t11_990,'''')
as "Disposal Date", ' + 'isnull((Select top 1 isnull(m1_600,'''') from
mgsm01 where m1_020=t11_100 and t11_090=''6''),'''') '+ 'as "Item Sub Group" from mgst11 '
+ 'where t11_900=''1'' and t11_990 =
'+ConvertToSQLDate(RecSynchLog."Disposal Date")+' '+
'and t11_020 = '+ RecSynchLog."Store Code" +' '+ 'ORDER BY "Disposal Date"
DESC',ADO);
// Get the Data to the Variables define in
Globals.
RsFReceiveData :=RsReceiveData.Fields();
RecordCount := 0;
WHILE (NOT RsReceiveData.EOF) AND (NOT
RsReceiveData.BOF) DO BEGIN
VendorCode := RsFReceiveData.Item('Vendor Code').Value;
StoreCode := RsFReceiveData.Item('Store Code').Value;
DateVar := FORMAT(RsFReceiveData.Item('Invoice date').Value);
InvoiceDate := ConvertToDate(DateVar);
InvoiceNo := RsFReceiveData.Item('Invoice Number').Value;
ItemGroup := RsFReceiveData.Item('Item Group').Value;
ItemCode := RsFReceiveData.Item('Item Code').Value;
ItemSubGroup := FORMAT(RsFReceiveData.Item('Item Sub Group').Value);
EVALUATE(RealReceiveAmount,FORMAT(RsFReceiveData.Item('Real Receive
Amount').Value));
OrderNo := RsFReceiveData.Item('Order Number').Value;
ReceiveDiv := RsFReceiveData.Item('Receive Div').Value;
UpdateDiv := RsFReceiveData.Item('Update Div').Value;
DateVar := FORMAT(RsFReceiveData.Item('Disposal Date').Value);
DisposalDate := ConvertToDate(DateVar);
IF
NOT(SetGlobalDisposalDate) THEN BEGIN
GlobalDisposalDate := DisposalDate;
SetGlobalDisposalDate := TRUE;
END;
RecordCount := RecordCount + 1;
InsertReceiveData;
RsReceiveData.MoveNext;
END;
//Close RecordSet
RsReceiveData.Close;
//Close Connection
CloseConnection;
Access Microsoft Dynamics NAV 5.0 Database from web through C/Front dll .
Access Microsoft Dynamics NAV 5.0 Database from web through
C/Front dll .
Three years back I got a requirement to create approval module
to Customer/Vendor Payments from
Inside as well as outside. Challenge was access from the
outside. I used to cfront dll to do this. In this article I have mentioned how
to change the Native DB Password from outside the NAV.
01 ) I designed below layout.
02) Write the code (C#.Net) inside the btnChangePassword_Click
Event using Microsoft.Navision.CFront
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Navision.CFront;
public partial class ChangePassword
: System.Web.UI.Page
{
protected void
btnChangePassword_Click(object sender, EventArgs e)
{
int CT;
int CR;
string NewConPassword;
string NewPassword1;
if (txtNewPassword.Text.Trim() ==
txtConfirmPassword.Text.Trim())
{
NewConPassword = txtConfirmPassword.Text.Trim();
NewPassword1 = txtNewPassword.Text.Trim();
try
{
NavisionNetType NetType;
NavisionDriverType DriverType;
NavisionCode Data;
NavisionText Password;
DriverType = NavisionDriverType.Native;
NetType = NavisionNetType.NativeTcp;
CFrontDotNet.DriverType = DriverType;
CFrontDotNet.NavisionPath =
System.Configuration.ConfigurationManager.AppSettings.Get("NavisionPath");
CFrontDotNet.Instance.ConnectServerAndOpenDatabase(System.Configuration.ConfigurationManager.AppSettings.Get("Database"), NetType, null, 2000, true, false, textUsername.Text.Trim(),
textPassword.Text.Trim());
CFrontDotNet.Instance.OpenCompany("CompanyName");
CT = CFrontDotNet.Instance.OpenTable(2000000002);
CR = CFrontDotNet.Instance.AllocRecord(CT);
CFrontDotNet.Instance.SetFilter(CT,
1, textUsername.Text.Trim());
try
{
if (CFrontDotNet.Instance.FindRecord(CT,
CR, "-"))
{
Data = CFrontDotNet.Instance.GetFieldData(CT, CR, 1);
Password = CFrontDotNet.Instance.GetFieldData(CT, CR, 2);
CFrontDotNet.Instance.CryptPassword(Data.ToString(),
NewConPassword.ToString());
CFrontDotNet.Instance.BeginWriteTransaction();
CFrontDotNet.Instance.SetFieldData(CT, CR, 2, NavisionFieldType.Text, GetBytesByType(NavisionFieldType.Text, NewConPassword.ToString(),
2000000002, 2));
CFrontDotNet.Instance.ModifyRecord(CT, CR);
CFrontDotNet.Instance.EndWriteTransaction();
lblErrorMassage.Text = "Password has been changed";
}
else
{
lblErrorMassage.Text = "Invalid User Id/Password";
}
}
finally
{
CFrontDotNet.Instance.FreeRecord(CR);
}
}
catch(Exception
ex)
{
lblErrorMassage.Text = "Invalid User
Id/Password";
}
}
else
{
lblErrorMassage.Text = "New Password
and Confirm password are miss match";
}
}
03) New function to convert data types.
private byte[]
GetBytesByType(NavisionFieldType type, String value, int
tableHandle, int
fieldNo)
{
switch (type)
{
case NavisionFieldType.Text:
{
NavisionText n = NavisionText.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.Option:
{
NavisionOption n = new NavisionOption(Convert.ToInt32(value));
return n.GetBytes();
}
case NavisionFieldType.BigInteger:
{
NavisionBigInteger
n = NavisionBigInteger.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.Binary:
{
NavisionBinary n = NavisionBinary.Parse(value);
return
n.GetBytes();
}
case NavisionFieldType.Blob:
{
NavisionBlob n = NavisionBlob.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.Boolean:
{
NavisionBoolean n = NavisionBoolean.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.Code:
{
NavisionCode n = NavisionCode.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.Date:
{
NavisionDate n = NavisionDate.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.DateFormula:
{
NavisionDateFormula n = NavisionDateFormula.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.DateTime:
{
NavisionDateTime n = NavisionDateTime.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.Decimal:
{
NavisionDecimal n = NavisionDecimal.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.Duration:
{
NavisionDuration n = NavisionDuration.Parse(value);
return
n.GetBytes();
}
case NavisionFieldType.Guid:
{
NavisionGuid n = NavisionGuid.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.Integer:
{
NavisionInteger n = new NavisionInteger(int.Parse(value));
return n.GetBytes();
}
case NavisionFieldType.RecordId:
{
NavisionRecordId n = NavisionRecordId.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.TableFilter:
{
NavisionTableFilter n = NavisionTableFilter.Parse(value);
return n.GetBytes();
}
case NavisionFieldType.Time:
{
NavisionTime n = NavisionTime.Parse(value);
return n.GetBytes();
}
}
return new byte[] { };
}
protected void
Page_Load(object sender, EventArgs e)
{
}
protected void
Page_unload(object sender, EventArgs e)
{
CFrontDotNet.Instance.CloseDatabase();
CFrontDotNet.Instance.DisconnectServer();
}
}
Subscribe to:
Posts (Atom)