- This is happening because the defaults are being taken from the item or customer master record.
- That specific item or customer card does not have a default account setup on it.
- Review the accounts, and set defaults.
Tuesday, April 25, 2017
eOne Flexicoder - When some segments are set to "Do Nothing" blanks are used for the segments instead of the default account segments
Monday, April 24, 2017
SQL - Determining the number of active connections on your SQL server (may be causing SQL to close connections if overloaded)
https://blogs.msdn.microsoft.com/developingfordynamicsgp/2014/04/22/more-on-sql-server-connection-issues-with-microsoft-dynamics-gp/
SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
Wednesday, April 19, 2017
Wednesday, April 12, 2017
Dynamics GP - Automatically Print separate remittance for check with more than 12 lines in remittance
This cannot be done, you must tick "Separate Remittance" when printing your checks.
To control the number of records displayed on the check stub
https://community.dynamics.com/gp/f/32/t/181070
In cases where there are too many lines to print on the
remittance,when printing checks you need to
- tick “Separate Remittance”
- Print your checks as
normal
- When the screen comes up to post checks, click “process”
- A new windows will open to prompt for a separate remittance. Select Remittance form, and print document, then click Process
- This will prompt you to
print the remittance separately.
https://community.dynamics.com/gp/f/32/t/181070
Go to LAyout>Tools>Section Options
Make sure that in Report Writer, your "Remittance Header" has a "Records Per Report Body" set to 12, and that the "No Break At Record Count" checkbox is unmarked.
Make sure that in Report Writer, your "Remittance Header" has a "Records Per Report Body" set to 12, and that the "No Break At Record Count" checkbox is unmarked.
This is the default setup for check formats.
Tuesday, April 11, 2017
Dynamics GP - Stuck SOP documents - This order has already been posted
https://community.dynamics.com/gp/b/gplearn/archive/2009/12/14/sop-34-this-document-has-been-posted-34-error-38-resolution
----------------------------------------------------------------------
--Check which tables have data
----------------------------------------------------------------------
select * from sop10100 where sopnumbe = '11746'
select * from sop30200 where sopnumbe = '11746'
select * from sop30300 where sopnumbe = '11746'
------------------------------------------------------------------------
--If the transaction is not in the GL, it did not post
------------------------------------------------------------------------
select * from gl10000 where DTAControlNum = '11746'
select * from gl20000 where orctrnum = '11746'
select * from gl30000 where ORCTRNUM = '11746'
------------------------------------------------------------------------
Sometimes, when an sop transaction gets stuck or corrupted, you may not be able to reopen it.
Ensure that you update the CUSTNMBR, BACHNUMB and BCHSOURC fields to allow the transaction to be visible in the SOP lookup menu.
update sop10100 set custnmbr = 'AARON001',BACHNUMB = 'RECOVER', bchsourc = 'Sales Entry' where sopnumbe = 'ORD001'
----------------------------------------------------------------------
--Check which tables have data
----------------------------------------------------------------------
select * from sop10100 where sopnumbe = '11746'
select * from sop30200 where sopnumbe = '11746'
select * from sop30300 where sopnumbe = '11746'
------------------------------------------------------------------------
--If the transaction is not in the GL, it did not post
------------------------------------------------------------------------
select * from gl10000 where DTAControlNum = '11746'
select * from gl20000 where orctrnum = '11746'
select * from gl30000 where ORCTRNUM = '11746'
------------------------------------------------------------------------
Sometimes, when an sop transaction gets stuck or corrupted, you may not be able to reopen it.
Ensure that you update the CUSTNMBR, BACHNUMB and BCHSOURC fields to allow the transaction to be visible in the SOP lookup menu.
update sop10100 set custnmbr = 'AARON001',BACHNUMB = 'RECOVER', bchsourc = 'Sales Entry' where sopnumbe = 'ORD001'
Monday, April 10, 2017
Dynamics NAV - Write to text file in CAL
https://dynamicsuser.net/nav/f/developers/8867/best-way-tro-write-to-a-file?pi2079=1
FileMyHTML.TEXTMODE:=TRUE;
FileMyHTML.CREATE('C:\TEST\main.txt');
FileMyHTML.WRITE('<html>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('<head>');
FileMyHTML.WRITE('<title>My Page</title>');
FileMyHTML.WRITE('</head>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('<P>Hello world!</p>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('</html>');
FileMyHTML.CLOSE();
FileMyHTML.TEXTMODE:=TRUE;
FileMyHTML.CREATE('C:\TEST\main.txt');
FileMyHTML.WRITE('<html>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('<head>');
FileMyHTML.WRITE('<title>My Page</title>');
FileMyHTML.WRITE('</head>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('<P>Hello world!</p>');
FileMyHTML.WRITE('');
FileMyHTML.WRITE('</html>');
FileMyHTML.CLOSE();
Variables
Name DataType Subtype Length
FileMyHTML File
Wednesday, April 5, 2017
SQL How to decrypt encrypted stored procedures
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e7056ca8-94cd-4d36-a676-04c64bf96330/decrypt-the-encrypted-store-procedure-through-the-tsql-programming-in-sql-server-2005?forum=transactsql
CREATE PROCEDURE dbo.ShowDecrypted(@ProcName SYSNAME = NULL) AS --Jon Gurgul 27/09/2010 --Adapted idea/code from shoeboy/joseph gama SET NOCOUNT ON IF EXISTS ( SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e on (ec.[endpoint_id]=e.[endpoint_id]) WHERE e.[name]='Dedicated Admin Connection' AND ec.[session_id] = @@SPID ) BEGIN DECLARE @i BIGINT,@a NVARCHAR(MAX),@b NVARCHAR(MAX),@d NVARCHAR(MAX),@c NVARCHAR(MAX) SET @a=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1) SET @b='ALTER PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS '+REPLICATE('-', 8000) BEGIN TRANSACTION EXECUTE (@b) SET @c=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1) ROLLBACK TRANSACTION SET @d = REPLICATE(N'A', (DATALENGTH(@a) /2 )) SET @i=1 WHILE @i<=(DATALENGTH(@a)/2) BEGIN SET @d = STUFF(@d, @i, 1,NCHAR(UNICODE(SUBSTRING(@a, @i, 1)) ^(UNICODE(SUBSTRING('CREATE PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS ' + REPLICATE('-', 8000), @i, 1)) ^UNICODE(SUBSTRING(@c, @i, 1))))) SET @i=@i+1 END SELECT @d [StoredProcedure] END ELSE BEGIN PRINT 'Use a DAC Connection' END SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Dynamics GP - Stock Count Variance View
/****** Object: View [dbo].[BI_INV_StockCountVariance] Script Date: 17/08/2017 11:40:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*This view can be used to review and print completed stock counts before they have been posted.
-------------------------------------------------------------------*/
CREATE VIEW [dbo].[BI_INV_StockCountVariance]
AS
SELECT dbo.IV10300.STCKCNTID, dbo.IV10300.STCKCNTDSCRPTN, dbo.IV10300.STCKCNTSTTS, dbo.IV10300.DOCDATE, dbo.IV00101.ITMCLSCD AS ItemClassCode, dbo.IV40400.ITMCLSDC AS ItemClassDesc,
dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.CURRCOST, dbo.IV10301.LOCNCODE, dbo.IV10301.BINNMBR, dbo.IV10301.COUNTDATE, dbo.IV10301.DECPLQTY, dbo.IV10301.ITMTRKOP,
dbo.IV10301.IVIVINDX, dbo.IV10301.IVVARIDX, CASE WHEN VERIFIED = 1 THEN 'Yes' ELSE 'No' END AS Verified, dbo.IV40201.BASEUOFM, dbo.GL00100.ACTDESCR AS IVActDesc,
dbo.GL00105.ACTNUMST AS IVActNum, GL00100_1.ACTDESCR AS VarActDesc, GL00105_1.ACTNUMST AS VarActNum, dbo.IV10301.CAPTUREDQTY AS QtyOnHand,
dbo.IV10301.CAPTUREDQTY * dbo.IV00101.CURRCOST AS AmtOnHand, dbo.IV10301.COUNTEDQTY AS QtyCounted, dbo.IV10301.COUNTEDQTY * dbo.IV00101.CURRCOST AS AmtCounted,
dbo.IV10301.COUNTEDQTY - dbo.IV10301.CAPTUREDQTY AS VarQty, (dbo.IV10301.COUNTEDQTY - dbo.IV10301.CAPTUREDQTY) * dbo.IV00101.CURRCOST AS VarAmt
FROM dbo.IV10300 INNER JOIN
dbo.IV10301 ON dbo.IV10300.STCKCNTID = dbo.IV10301.STCKCNTID LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV40201 INNER JOIN
dbo.IV00101 ON dbo.IV40201.UOMSCHDL = dbo.IV00101.UOMSCHDL ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.IV10301.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.GL00105 AS GL00105_1 INNER JOIN
dbo.GL00100 AS GL00100_1 ON GL00105_1.ACTINDX = GL00100_1.ACTINDX ON dbo.IV10301.IVVARIDX = GL00100_1.ACTINDX LEFT OUTER JOIN
dbo.GL00105 INNER JOIN
dbo.GL00100 ON dbo.GL00105.ACTINDX = dbo.GL00100.ACTINDX ON dbo.IV10301.IVIVINDX = dbo.GL00100.ACTINDX
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*This view can be used to review and print completed stock counts before they have been posted.
-------------------------------------------------------------------*/
CREATE VIEW [dbo].[BI_INV_StockCountVariance]
AS
SELECT dbo.IV10300.STCKCNTID, dbo.IV10300.STCKCNTDSCRPTN, dbo.IV10300.STCKCNTSTTS, dbo.IV10300.DOCDATE, dbo.IV00101.ITMCLSCD AS ItemClassCode, dbo.IV40400.ITMCLSDC AS ItemClassDesc,
dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.CURRCOST, dbo.IV10301.LOCNCODE, dbo.IV10301.BINNMBR, dbo.IV10301.COUNTDATE, dbo.IV10301.DECPLQTY, dbo.IV10301.ITMTRKOP,
dbo.IV10301.IVIVINDX, dbo.IV10301.IVVARIDX, CASE WHEN VERIFIED = 1 THEN 'Yes' ELSE 'No' END AS Verified, dbo.IV40201.BASEUOFM, dbo.GL00100.ACTDESCR AS IVActDesc,
dbo.GL00105.ACTNUMST AS IVActNum, GL00100_1.ACTDESCR AS VarActDesc, GL00105_1.ACTNUMST AS VarActNum, dbo.IV10301.CAPTUREDQTY AS QtyOnHand,
dbo.IV10301.CAPTUREDQTY * dbo.IV00101.CURRCOST AS AmtOnHand, dbo.IV10301.COUNTEDQTY AS QtyCounted, dbo.IV10301.COUNTEDQTY * dbo.IV00101.CURRCOST AS AmtCounted,
dbo.IV10301.COUNTEDQTY - dbo.IV10301.CAPTUREDQTY AS VarQty, (dbo.IV10301.COUNTEDQTY - dbo.IV10301.CAPTUREDQTY) * dbo.IV00101.CURRCOST AS VarAmt
FROM dbo.IV10300 INNER JOIN
dbo.IV10301 ON dbo.IV10300.STCKCNTID = dbo.IV10301.STCKCNTID LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV40201 INNER JOIN
dbo.IV00101 ON dbo.IV40201.UOMSCHDL = dbo.IV00101.UOMSCHDL ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.IV10301.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.GL00105 AS GL00105_1 INNER JOIN
dbo.GL00100 AS GL00100_1 ON GL00105_1.ACTINDX = GL00100_1.ACTINDX ON dbo.IV10301.IVVARIDX = GL00100_1.ACTINDX LEFT OUTER JOIN
dbo.GL00105 INNER JOIN
dbo.GL00100 ON dbo.GL00105.ACTINDX = dbo.GL00100.ACTINDX ON dbo.IV10301.IVIVINDX = dbo.GL00100.ACTINDX
GO
Monday, April 3, 2017
SQL and EXCEL - How to get Sheet names from an Excel Workbook using SQL, then use those to cycle through each sheet. Import data from multiple excel sheets.
CREATE TABLE [dbo].[STFNAMES](
[TABLE_CAT] [nchar](10) NULL,
[TABLE_SCHEM] [nchar](10) NULL,
[TABLE_NAME] [varchar](50) NULL,
[Table_Type] [varchar](50) NULL,
[Remarks] [varchar](50) NULL
) ON [PRIMARY]
GO
exec sp_dropserver 'ExcelSource','droplogins'
exec sp_addlinkedserver 'ExcelSource','', 'Microsoft.ACE.OLEDB.12.0','E:\myfile.xlsx',NULL, 'Excel 12.0'
exec sp_addlinkedsrvlogin 'ExcelSource','false'
delete STFNAMES
insert into STFNAMES EXEC sp_tables_ex 'ExcelSource'
drop table STFNAMES2
select ROW_NUMBER() OVER(ORDER BY TableNm ASC) AS Rownum, TableNm into STFNAMES2 from (
select distinct left(right(table_name,len(Table_Name)-1),charindex('$',Table_Name)-1) as TableNm from stfnames where Table_Name not like '%Name%' and Table_Name not like '%Cases%') as Tbl
select * from STFNAMES2
Subscribe to:
Posts (Atom)