eOne Smartconnect - Failed to convert parameter value from a String to a Int16

There is nothing wrong with anything, you just have the wrong data type being submitted to a number, amount, or List field.

Chances are, you are submitting a text value to a list field instead of the numerical index.

Recheck your map, and look for all list fields.
Submit the number that corresponds to the option instead of text.

Dynamics NAV - How to Update a value in the current record

This example updates the posting date to the current date when a user attempts to post a specific warehouse shipment

---------------------------------------------------------------------
WhseShptHeader.COPY(Rec);  //Copy the current record to memory
Rec := WhseShptHeader; //Set rec = current record

WITH WhseShptHeader DO BEGIN
  SETCURRENTKEY("No."); // Go to the record currently displayed
  SETRANGE("No.","No."); //Limit the records to the range of records displayed

MESSAGE(FORMAT(TODAY)); //Format date a text and display it on screen as message box
WhseShptHeader."Posting Date" :=TODAY; //change posting date to today's date for this record
WhseShptHeader.MODIFY; //Modify the record
COMMIT; //only necessary if doing multiple modifies
END;
CurrForm.WhseShptLines.FORM.PostShipmentYesNo;

-----------------------------------------------------------------------

Dynamics GP - PDK Tables

select * from pdk10000 Timesheet HDR
select * from pdk10001 Timesheet Line
select * from pdk01601 Timesheet Line notes

PA01201 - Project Master

Management Reporter - Records are missing in MR - MR does not match GP

MR does not match GP
Are you using AA?
chances are, there is corrupted data in the AA tables for some transactions that is preventing them from being sent to the MR data warehouse.

Follow the steps here to resolve

http://support.microsoft.com/kb/2910626


You may need to add/update records in the following tables
aag30001,aag30002
using data from the aag30000 table and the output from the script

Follow all instructions in the scripts, and verify all records before adding or updating.

Dynamics GP - Change the Vendor Doc Number on Payables Transactions

You need to update the docnumber / vnddocnum in these tables

update PM20000 set DOCNUMBR = '584771' where VCHRNMBR = '002779'
update POP30300 set VNDDOCNM = '584771' where POPRCTNM = 'rct001575'
update PM00400 set DOCNUMBR = '584771' where DOCNUMBR = 'po001170'

this is a script i ended up using

--This script will identify  all transactions with a specific Vendor Document Number, and switch them to a new document number.

declare @CurrVendDocNo varchar(30), @NewVendDocNo varchar(30)
select @CurrVendDocNo = 'po001170'  -- Enter Current Vendor Doc Number to Search For
select @NewVendDocNo = '584771' -- Enter New Vendor Doc number to switch it to

select vendorid, pordnmbr,DOCNUMBR  from pm20000 where DOCNUMBR = @CurrVendDocNo
select vendorid, POPRCTNM,VNDDOCNM   from pop30300 where vnddocnm = @CurrVendDocNo
select VENDORID, docnumbr from pm00400 where DOCNUMBR = @CurrVendDocNo
--Run the script up to here to view the records that will be affected by this change

update PM20000 set DOCNUMBR = @NewVendDocNo  where DOCNUMBR = @CurrVendDocNo
update POP30300 set VNDDOCNM = @NewVendDocNo  where  VNDDOCNM = @CurrVendDocNo
update PM00400 set DOCNUMBR = @NewVendDocNo  where DOCNUMBR = @CurrVendDocNo

select vendorid, pordnmbr,DOCNUMBR  from pm20000 where DOCNUMBR = @NewVendDocNo
select vendorid, POPRCTNM,VNDDOCNM   from pop30300 where vnddocnm = @NewVendDocNo
select VENDORID, docnumbr from pm00400 where DOCNUMBR = @NewVendDocNo

eOne Smartconnect - Excel Data sources cannot be found

Excel data sources are missing, unavailable, do not display in the drop down list, or are displayed, but smartconnect throws an error saying it cannot find a valid data source or driver.

This is because Smartconnect is using the 32 bit Microsoft Access Database Engine required for Excel data connections. (This is dependant on the original drivers used to create the original map. If the original map was created using 64-bit drivers, it will need 64 bit drivers on all clients. If the original map was created using 32-bit drivers, it will need 32-bit drivers on all clients)

These components are usually installed when you do a full install of office, however, if the client is using 64-bit Office, then it installs the 64-bit Access Database engine.

To resolve this issue, you must install the 32-bit Microsoft Access Database Engine, however, if you alrayd have 64-bit office installed, you will get an error saying you cannot install the 32-bit drivers because 64-bit drivers are already installed.

To get around this, run the install from a command line or batch file with the /passive flag

Example

C:\Accessdatabaseengine.exe /passive

will force the install even if 64-bit is already installed.

Access Database Engine Downloads
http://www.microsoft.com/en-us/download/details.aspx?id=13255


Original Post
http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/

Dynamics NAV - Installation checklist

1        MS SQL Installation

1.          Ensure the Server name is accurate if it is a new server.

2.          Make sure the date and time is set accurately.

3.          SQL Server Installation:

a)          Configure Windows Firewall: http://technet.microsoft.com/en-us/library/cc646023.aspx

                                                       i.     Database Engine access requirements:

1.          Inbound rule for local port 1433

2.          Inbound rule for SQL application C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe

                                                       ii.     Analysis Services access requirements:

1.          For a default instance of SQL, create an inbound rule for port 2383. The execute the following commands:

a.          netsh advfirewall firewall add rule name="SQL Server Analysis Services (tcp-in) on 54321" dir=in action=allow protocol=TCP localport=54321 profile=domain

b.          netsh advfirewall firewall add rule name="SQL Server Analysis Services inbound on TCP 2383" dir=in action=allow protocol=TCP localport=2383 profile=domain

2.          For a default instance of SQL, create an inbound rule for port 2382. Verify that you can browse to the instance by trying to connect to the instance using the syntax <servername>\<instancename>.

                                                       iii.     Report Server access requirements:

1.          Inbound rule for local port 80.

b)          Activate .Net 3.5 if the machine is Server 2012 using Add Roles and Features from Server Manager. Server 2010 has .Net 4.0 installed but SQL searches for 3.5. (Recommended to add the Application Server Role on the Server)

c)          Once all the prerequisite checks are complete and all successful, you will be required to select SQL Server Features to install. The following are the minimum features required for NAV:

                                                       i.     Database Engine Services.

                                                       ii.     Management Tools – Basic

                                                       iii.     Management Tools – Complete

                                                       iv.     Analysis Services (Optional)

                                                       v.     Reporting Services (Optional)

2        NAV Server Installation

1.      Ensure that Microsoft Outlook and Excel 2010 or later versions are installed on the server.

2.      Ensure that the Microsoft Search service is installed and activated.

3.      Ensure that the “SQL Server”, “SQL Database Agent”, “SQL Server Browser”, “SQL Server Analysis Service” and SQL Server Reporting Services” services startup type is set to automatic and are running.

4.      Launch Setup file from installation media.

5.      Select “Installation Option” from the Dynamics NAV Setup screen.

a)      Note: Monitor the installation ensure that SQL Express is not installed if SQL Standard is already installed and will be the default database application. If SQL Standard is not installed or recognized, the installation process will install SQL Express Server 2010.

6.      Select “Server” for the installation option.

7.      Highlight all options top install and the click on “Apply” to start the installation.

8.      Upon completion of the installation, log into SQL Management Studio to confirm that the “Demo” database was successfully created.

3        NAV Developer and Client Installation

1.      Launch the setup from installation media.

2.      Select “Add or remove components”

3.      Under Microsoft Dynamics NAV, select “Client” to install and then select “Development Environment” and “Microsoft Office Excel Add-in” and then click next.

4.      In the “Specify Parameters” window, type “Demo” in the SQL Database field, then click “Apply”.

5.      Launch Dynamics NAV Administration Tool, select the DynamicsNAV71 instance, and click on Edit” and update the database server field with the appropriate name and then click on “Save”.

6.      Restart the DynamicsNAV71 service by right clicking on the instance and click “Restart”.

7.      Once complete, launch Dynamics NAV client to determine if the installation was successful. If the application launches successfully into the demo company, the installation process is completed.

4        Additional resources:

b)      Basic Installation: http://blogs.msdn.com/b/nav_developer/archive/2008/11/05/basic-sql-overview-of-nav-specific-sql-features-for-application-consultants.aspx

c)      To rename a server that is running SQL , you will need to perform the following steps:

                                                       vi.     For a SQL server with a default instance:

1.          Rename the computer in Computer Properties.

2.          Execute the following query:

sp_dropserver <old_name>;

GO

sp_addserver <new_name>, local;

GO

3.          Execute the query to see if the query was successful:

SELECT @@SERVERNAME AS 'Server Name';

4.          To view the instance name:

select @@servername + '\' + @@servicename

                                                       vii.     For a server with a named instance:

1.          Rename the computer in Computer Properties.

2.          Execute the following query:

sp_dropserver <old_name\instancename>;

GO

sp_addserver <new_name\instancename>, local;

GO       

3.          Execute the query to see if the query was successful:

SELECT @@SERVERNAME AS 'Server Name';

           

Dynamics GP - Report Writer Function - RW_PMAddrIDInfo

This will allow you to get address data from a vendor

User Defined function - System - RW_PMAddrIDInfo()

The parameters are:

in 'Vendor ID' IN_Vendor_ID;
in 'Address Code' IN_Address_Code;
in integer IN_Field;

{
IN_Field should use the following values:-
1 = Contact Name
2 = Address 1
3 = Address 2
4 = Address 3
5 = City
6 = State
7 = Zip
8 = Country
9 = Phone 1
10 = Phone 2
11 = Phone 3
12 = Fax
13 = Country Code
}

Original Post

http://www.tek-tips.com/viewthread.cfm?qid=1530374

Working example:

rw_pmaddridinfo(POR_receipt_temp.vendorid "RETURN" 2)

will return the address line 1 of the current vendor in address id "RETURN"

Dynamics GP - Script to backup live company and restore into existing test company and update companyid. Copy live to test.

BACKUP DATABASE [YOURDB] TO  DISK = N'C:\Temp\YOURDB_temp.bak' WITH NOFORMAT, INIT,  NAME = N'YOURDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


Use Master
 Alter Database TEST
  SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE [TEST] FROM  DISK = N'C:\Temp\YOURDB_temp.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO



-- find the company id by using select * from dynamics.dbo.sy01500

update test.dbo.sy00100 set cmpanyid = 10

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
  declare @Statement varchar(850)
  select @Statement = 'declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')
  then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3)) 
  else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b
  where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
    and b.INTERID = db_name() and COLUMN_DEFAULT is not null
 and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
  order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
  exec (@cStatement)
  FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off'
  from SY00100
  exec (@Statement)
end
else begin
  declare @cStatement varchar(255)
  declare G_cursor CURSOR for
  select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
    then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3)) 
    else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
  from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
    where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
      and b.INTERID = db_name() and COLUMN_DEFAULT is not null
    order by a.TABLE_NAME
  set nocount on
  OPEN G_cursor
  FETCH NEXT FROM G_cursor INTO @cStatement
  WHILE (@@FETCH_STATUS <> -1)
  begin
    exec (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement
  end
  close G_cursor
  DEALLOCATE G_cursor
  set nocount off
end

Dynamics GP - IV00301 Lot Attribute Table Explanation

• If stock is increased, 
• and the lot number does not exist, an entry is created in this table
• RCRDSTTUS is set to 0
• IUSCOUNT is set to 1
• and the lot number and the lot number already exists, the entry is updated
• IUSCOUNT = iuscount + 1
• If stock is decreased or transferred
• and the lot number does not exist, an entry is created in this table
• RCRDSTTUS is set to 2
• IUSCOUNT is set to 1
• and the lot number and the lot number already exists, the entry is updated
• IUSCOUNT = iuscount + 1
• IUSCOUNT represents the number of lots with the same name (it should never be negative)