This is a bug with Integration manager 2013.
You transactions integrated just fine.
If you ran it multiple times, you will need to void the additional transactions.
Increase the number of errors allowed ,and run your integration once.
Wednesday, October 30, 2013
Dynamics GP Report Writer Tricks
All calculated string fields can show up to 80 characters. However, report writer truncates what is displayed based on the size of the text box, and the font size being used. To get the full 80 characters,drop the font size to 6, and expand the entire field all the way out.
This will allow it to export correctly for your word form template.
This will allow it to export correctly for your word form template.
Dynamics GP - Default Smartlist Security
There is no default smartlist security.
All users have access to all smartlists by default.
To limit access, create a task and a role.
In the role, select Smartlist, smartlist objects, smartlist objects, and select the specific smartlists to grant access.
Assign this role to a user.
This will override the default "all access"
All users have access to all smartlists by default.
To limit access, create a task and a role.
In the role, select Smartlist, smartlist objects, smartlist objects, and select the specific smartlists to grant access.
Assign this role to a user.
This will override the default "all access"
Tuesday, October 29, 2013
Dynamics GP - Report Writer Description fields not showing all characters on reports.
Notes:
Report Writer WILL wrap text if you stretch the field downward, but the default field only shows 50 characters.
Report Writer WILL NOT wrap text if there are no spaces in the data, even if you stretch the field downward.
Issue:
GP Item Description can take 100 characters.
Report Writer Fields are limited to 50 Characters (this is 80 in newer version of gp)
Report Writer Calculated Fields are limited to 40 Characters (this is 80 in newer versions of gp)
This applies to a number of different fields.
Solution 1: Report Writer functions
Create Multiple calculated fields to chop the description into 40,80,100, and pull all three fields onto the report.
This will cause hard cuts, and may cut a word in the middle
Use the RW_Substring function under user-defined functions
http://support.microsoft.com/kb/950780
Solution 2: Report Writer with VBA
Add VB code to connect directly to table, get the full description, then cut the fields up based on spaces between the words.
I would just pass this to a label field instead to prevent the need to cut based on spaces in the words, but there is also a limit on the label field.
Related links:
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/05/25/showing-the-full-item-description-on-invoices-example.aspx
ADO Sample
-----------------------------------------------------
Option Explicit
Dim objRec
Dim objConn
Dim cmdString
'Dim cn As New ADODB.Connection
Private Sub Report_BeforeBody(SuppressBand As Boolean)
End Sub
Private Sub report_start()
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DYNGP;Initial Catalog=TWO;User Id=sa;Password="
'cn = UserInfoGet.CreateADOConnection
' cn.CursorLocation = 3
' cn.DefaultDatabase = UserInfoGet.IntercompanyID
' cn.Open
objConn.Open
End Sub
Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand As Boolean)
cmdString = "select Source_Document_Number from pop10110 where PONumber = '" + RTrim(PONumber) + "' and itemnmbr = '" + RTrim(ItemNumber) + "'"
Set objRec = objConn.Execute(cmdString)
If objRec.EOF = True Then
xReqNo = ""
Else
xReqNo.Value = objRec!Source_Document_Number
End If
End Sub
Private Sub report_end()
objConn.Close
End Sub
Ensure that you add all fields you need to VB
Ensure you run GP as administrator or the VB will not save
Call this in the body to execute for each record
ManufacturingOrderReceiptEn.CurrencyM105 is an empty calculated field
You must put the calculated field in the BODY or the appropriate section where the VB code is running
Report Writer WILL wrap text if you stretch the field downward, but the default field only shows 50 characters.
Report Writer WILL NOT wrap text if there are no spaces in the data, even if you stretch the field downward.
Issue:
GP Item Description can take 100 characters.
Report Writer Fields are limited to 50 Characters (this is 80 in newer version of gp)
Report Writer Calculated Fields are limited to 40 Characters (this is 80 in newer versions of gp)
This applies to a number of different fields.
Solution 1: Report Writer functions
Create Multiple calculated fields to chop the description into 40,80,100, and pull all three fields onto the report.
This will cause hard cuts, and may cut a word in the middle
Use the RW_Substring function under user-defined functions
- The format is
- Functions>User-defined>System
- RW_Substring add
- Item Description add
- start position constant add
- substring length constant add
Do not remove any characters, do not add any other fields or characters
So for example, if i wanted to chop the item description start at character 81, and i wanted my string to be 20 characters long, it would look like
RW_Substring Item Description 81 20
in the report writer editor
For the Check Formats, you can split the PM10300.STRGA255 to replace the STRGA255 and STRGB255 fields on the report. (does not work, cuts cents badly)
Create two calculated string fields with formulas that will look like
Or use the Purchasing>RW_Splitwordamt function
https://blogs.msdn.microsoft.com/developingfordynamicsgp/2010/05/23/announcing-report-writer-function-rw_converttowordsandnumbersparse/
Related links:For the Check Formats, you can split the PM10300.STRGA255 to replace the STRGA255 and STRGB255 fields on the report. (does not work, cuts cents badly)
Create two calculated string fields with formulas that will look like
- Function Script(RW_Substring PM_Payment_Work.STRGA255 1 60)
- Function Script(RW_Substring PM_Payment_Work.STRGA255 61 60)
Or use the Purchasing>RW_Splitwordamt function
- Create 3 calculated fields
- Function Script(RW_Splitwordamt PM_Payment_Work.STRGA255 PM_Payment_Work.STRGB255 75 1)
- Function Script(RW_Splitwordamt PM_Payment_Work.STRGA255 PM_Payment_Work.STRGB255 75 2)
- Function Script(RW_Splitwordamt PM_Payment_Work.STRGA255 PM_Payment_Work.STRGB255 75 3)
https://blogs.msdn.microsoft.com/developingfordynamicsgp/2010/05/23/announcing-report-writer-function-rw_converttowordsandnumbersparse/
- FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbersParse cyPrintTotal (c) Currency ID 0 80 1)
- FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbersParse cyPrintTotal (c) Currency ID 0 80 2)
- FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbersParse cyPrintTotal (c) Currency ID 0 80 3)
http://support.microsoft.com/kb/950780
Solution 2: Report Writer with VBA
Add VB code to connect directly to table, get the full description, then cut the fields up based on spaces between the words.
I would just pass this to a label field instead to prevent the need to cut based on spaces in the words, but there is also a limit on the label field.
Related links:
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/05/25/showing-the-full-item-description-on-invoices-example.aspx
ADO Sample
-----------------------------------------------------
Option Explicit
Dim objRec
Dim objConn
Dim cmdString
'Dim cn As New ADODB.Connection
Private Sub Report_BeforeBody(SuppressBand As Boolean)
End Sub
Private Sub report_start()
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DYNGP;Initial Catalog=TWO;User Id=sa;Password="
'cn = UserInfoGet.CreateADOConnection
' cn.CursorLocation = 3
' cn.DefaultDatabase = UserInfoGet.IntercompanyID
' cn.Open
objConn.Open
End Sub
Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand As Boolean)
cmdString = "select Source_Document_Number from pop10110 where PONumber = '" + RTrim(PONumber) + "' and itemnmbr = '" + RTrim(ItemNumber) + "'"
Set objRec = objConn.Execute(cmdString)
If objRec.EOF = True Then
xReqNo = ""
Else
xReqNo.Value = objRec!Source_Document_Number
End If
End Sub
Private Sub report_end()
objConn.Close
End Sub
Ensure that you add all fields you need to VB
Ensure you run GP as administrator or the VB will not save
Call this in the body to execute for each record
ManufacturingOrderReceiptEn.CurrencyM105 is an empty calculated field
You must put the calculated field in the BODY or the appropriate section where the VB code is running
Sunday, October 27, 2013
SQL - Scan all tables and fields for a text string
Original Post
http://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-sqll-server-db
--------------------------------------------------------------------------------------
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'pal'
DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM [' + @table_name + '] WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
--print @sql_string
EXECUTE(@sql_string)
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur
http://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-sqll-server-db
--------------------------------------------------------------------------------------
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'pal'
DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM [' + @table_name + '] WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
--print @sql_string
EXECUTE(@sql_string)
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur
Saturday, October 26, 2013
Dynamics GP - Install smartconnect 2013
Pre-Requisites
You may need to install the following on the client depending on the types of integrations you intend to run
Confirm the Smartconnect User Password if you have already setup smartconnect previously.
Install smartconnect 2013
Run smartconnect, setup gp connector
Run gp, include code, update resource cache
Run smartconnect, run maintenance
You may need to install the following on the client depending on the types of integrations you intend to run
- eConnect 12 (On GP Install disk)
- Data Access Components 32-bit
- dot Net 4.5
Confirm the Smartconnect User Password if you have already setup smartconnect previously.
Install smartconnect 2013
Run smartconnect, setup gp connector
Run gp, include code, update resource cache
Run smartconnect, run maintenance
Dynamics GP - How to upgrade a forms / Reports dictionary using Dynamics GP Utilities
Copy Old GP Folder
Install New GP
Copy Old dictionary to New GP Data folder
Run GP Utilities
Select Upgrade forms/Reports dictionary
Select OLD Dynamics.set
Select Reports.dic you copied into NEW GP
Process
Install New GP
Copy Old dictionary to New GP Data folder
Run GP Utilities
Select Upgrade forms/Reports dictionary
Select OLD Dynamics.set
Select Reports.dic you copied into NEW GP
Process
Friday, October 25, 2013
Dynamics GP - Inventory Internet Information Table - Additional Information
SY01200 - Inventory Internet Information
Tuesday, October 22, 2013
SSRS - How to convert Numbers to Words in SQL for use with SSRS
This can be achieved by creating a lookup table, and a function to convert numbers to words.
Original Post
http://stackoverflow.com/questions/1673265/how-to-write-number-to-word-function-in-sql-server
--Create conversion table
CREATE TABLE [dbo].[BI_Sequence]
(
seq INTEGER NOT NULL UNIQUE,
word [varchar](25) NOT NULL
)
INSERT INTO [BI_Sequence] SELECT 0, ''
INSERT INTO [BI_Sequence] SELECT 1, 'One'
INSERT INTO [BI_Sequence] SELECT 2, 'Two'
INSERT INTO [BI_Sequence] SELECT 3, 'Three'
INSERT INTO [BI_Sequence] SELECT 4, 'Four'
INSERT INTO [BI_Sequence] SELECT 5, 'Five'
INSERT INTO [BI_Sequence] SELECT 6, 'Six'
INSERT INTO [BI_Sequence] SELECT 7, 'Seven'
INSERT INTO [BI_Sequence] SELECT 8, 'Eight'
INSERT INTO [BI_Sequence] SELECT 9, 'Nine'
INSERT INTO [BI_Sequence] SELECT 10, 'Ten'
INSERT INTO [BI_Sequence] SELECT 11, 'Eleven'
INSERT INTO [BI_Sequence] SELECT 12, 'Twelve'
INSERT INTO [BI_Sequence] SELECT 13, 'Thirteen'
INSERT INTO [BI_Sequence] SELECT 14, 'Fourteen'
INSERT INTO [BI_Sequence] SELECT 15, 'Fifteen'
INSERT INTO [BI_Sequence] SELECT 16, 'Sixteen'
INSERT INTO [BI_Sequence] SELECT 17, 'Seventeen'
INSERT INTO [BI_Sequence] SELECT 18, 'Eighteen'
INSERT INTO [BI_Sequence] SELECT 19, 'Nineteen'
INSERT INTO [BI_Sequence] SELECT 20, 'Twenty'
INSERT INTO [BI_Sequence] SELECT 30, 'Thirty'
INSERT INTO [BI_Sequence] SELECT 40, 'Forty'
INSERT INTO [BI_Sequence] SELECT 50, 'Fifty'
INSERT INTO [BI_Sequence] SELECT 60, 'Sixty'
INSERT INTO [BI_Sequence] SELECT 70, 'Seventy'
INSERT INTO [BI_Sequence] SELECT 80, 'Eighty'
INSERT INTO [BI_Sequence] SELECT 90, 'Ninety'
--Create Function
create FUNCTION dbo.BI_NumToWords (
@num AS INTEGER
) RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @words AS VARCHAR(50)
IF @num = 0 SELECT @words = 'Zero'
ELSE IF @num < 20 SELECT @words = word FROM BI_Sequence WHERE seq = @num
ELSE IF @num < 100 (SELECT @words = TTens.word + ' ' + TUnits.word
FROM BI_Sequence AS TUnits
CROSS JOIN BI_Sequence AS TTens
WHERE TUnits.seq = (@num % 100) % 10
AND TTens.seq = (@num % 100) - (@num % 100) % 10
)
ELSE IF @num = 100 (SELECT @words = THundreds.word + ' Hundred'
FROM BI_Sequence AS THundreds
WHERE THundreds.seq = (@num / 100)
)
ELSE IF @num < 1000 (
SELECT @words = THundreds.word + ' Hundred and '
+ TTens.word + ' ' + TUnits.word
FROM BI_Sequence AS TUnits
CROSS JOIN BI_Sequence AS TTens
CROSS JOIN BI_Sequence AS THundreds
WHERE TUnits.seq = (@num % 100) % 10
AND TTens.seq = (@num % 100) - (@num % 100) % 10
AND THundreds.seq = (@num / 100)
)
ELSE IF @num = 1000 (SELECT @words = TThousand.word + ' Thousand'
FROM BI_Sequence AS TThousand
WHERE TThousand.seq = (@num / 1000)
)
ELSE IF @num < 10000 (
SELECT @words = TThousand.word + ' Thousand '
+ THundreds.word + ' Hundred and '
+ TTens.word + ' ' + TUnits.word
FROM BI_Sequence AS TUnits
CROSS JOIN BI_Sequence AS TTens
CROSS JOIN BI_Sequence AS THundreds
CROSS JOIN BI_Sequence AS TThousand
WHERE TUnits.seq = (@num % 100) % 10
AND TTens.seq = (@num % 100) - (@num % 100) % 10
AND THundreds.seq = (@num / 100) - (@num / 1000) * 10
AND TThousand.seq = (@num / 1000)
)
ELSE SELECT @words = STR(@num)
RETURN @words
END
--Call the function by using
--Select dbo.BI_NumtoWords(9)
--Where 9 is any number
Original Post
http://stackoverflow.com/questions/1673265/how-to-write-number-to-word-function-in-sql-server
--Create conversion table
CREATE TABLE [dbo].[BI_Sequence]
(
seq INTEGER NOT NULL UNIQUE,
word [varchar](25) NOT NULL
)
INSERT INTO [BI_Sequence] SELECT 0, ''
INSERT INTO [BI_Sequence] SELECT 1, 'One'
INSERT INTO [BI_Sequence] SELECT 2, 'Two'
INSERT INTO [BI_Sequence] SELECT 3, 'Three'
INSERT INTO [BI_Sequence] SELECT 4, 'Four'
INSERT INTO [BI_Sequence] SELECT 5, 'Five'
INSERT INTO [BI_Sequence] SELECT 6, 'Six'
INSERT INTO [BI_Sequence] SELECT 7, 'Seven'
INSERT INTO [BI_Sequence] SELECT 8, 'Eight'
INSERT INTO [BI_Sequence] SELECT 9, 'Nine'
INSERT INTO [BI_Sequence] SELECT 10, 'Ten'
INSERT INTO [BI_Sequence] SELECT 11, 'Eleven'
INSERT INTO [BI_Sequence] SELECT 12, 'Twelve'
INSERT INTO [BI_Sequence] SELECT 13, 'Thirteen'
INSERT INTO [BI_Sequence] SELECT 14, 'Fourteen'
INSERT INTO [BI_Sequence] SELECT 15, 'Fifteen'
INSERT INTO [BI_Sequence] SELECT 16, 'Sixteen'
INSERT INTO [BI_Sequence] SELECT 17, 'Seventeen'
INSERT INTO [BI_Sequence] SELECT 18, 'Eighteen'
INSERT INTO [BI_Sequence] SELECT 19, 'Nineteen'
INSERT INTO [BI_Sequence] SELECT 20, 'Twenty'
INSERT INTO [BI_Sequence] SELECT 30, 'Thirty'
INSERT INTO [BI_Sequence] SELECT 40, 'Forty'
INSERT INTO [BI_Sequence] SELECT 50, 'Fifty'
INSERT INTO [BI_Sequence] SELECT 60, 'Sixty'
INSERT INTO [BI_Sequence] SELECT 70, 'Seventy'
INSERT INTO [BI_Sequence] SELECT 80, 'Eighty'
INSERT INTO [BI_Sequence] SELECT 90, 'Ninety'
--Create Function
create FUNCTION dbo.BI_NumToWords (
@num AS INTEGER
) RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @words AS VARCHAR(50)
IF @num = 0 SELECT @words = 'Zero'
ELSE IF @num < 20 SELECT @words = word FROM BI_Sequence WHERE seq = @num
ELSE IF @num < 100 (SELECT @words = TTens.word + ' ' + TUnits.word
FROM BI_Sequence AS TUnits
CROSS JOIN BI_Sequence AS TTens
WHERE TUnits.seq = (@num % 100) % 10
AND TTens.seq = (@num % 100) - (@num % 100) % 10
)
ELSE IF @num = 100 (SELECT @words = THundreds.word + ' Hundred'
FROM BI_Sequence AS THundreds
WHERE THundreds.seq = (@num / 100)
)
ELSE IF @num < 1000 (
SELECT @words = THundreds.word + ' Hundred and '
+ TTens.word + ' ' + TUnits.word
FROM BI_Sequence AS TUnits
CROSS JOIN BI_Sequence AS TTens
CROSS JOIN BI_Sequence AS THundreds
WHERE TUnits.seq = (@num % 100) % 10
AND TTens.seq = (@num % 100) - (@num % 100) % 10
AND THundreds.seq = (@num / 100)
)
ELSE IF @num = 1000 (SELECT @words = TThousand.word + ' Thousand'
FROM BI_Sequence AS TThousand
WHERE TThousand.seq = (@num / 1000)
)
ELSE IF @num < 10000 (
SELECT @words = TThousand.word + ' Thousand '
+ THundreds.word + ' Hundred and '
+ TTens.word + ' ' + TUnits.word
FROM BI_Sequence AS TUnits
CROSS JOIN BI_Sequence AS TTens
CROSS JOIN BI_Sequence AS THundreds
CROSS JOIN BI_Sequence AS TThousand
WHERE TUnits.seq = (@num % 100) % 10
AND TTens.seq = (@num % 100) - (@num % 100) % 10
AND THundreds.seq = (@num / 100) - (@num / 1000) * 10
AND TThousand.seq = (@num / 1000)
)
ELSE SELECT @words = STR(@num)
RETURN @words
END
--Call the function by using
--Select dbo.BI_NumtoWords(9)
--Where 9 is any number
Friday, October 18, 2013
SSRS - Export a PDF as Landscape instead of Portait
Other Related Issues:
How to set report properties
How to change default printing layout
Solution:
How to set report properties
How to change default printing layout
Solution:
- In the Report Builder, Right click on the grey area outside the report
- click on Report Properties
To fix it in Internet Explorer if printing directly
- Click the Gear
- Click Print
- Click Page Setup
Sunday, October 13, 2013
Dynamics GP - Field Service Contract Line Mail Merge Macro
ActivateWindow dictionary 'FieldService' form 'SVC_Contract_Maintenance' window
'SVC_Contract_Maintenance'
ClickHit field
'Contract Record Type' item 2 #
'Contract'
MoveTo field
'Contract Number'
TypeTo field
'Contract Number' , '«ContNo»'
MoveTo field
'Customer Number'
MoveTo field '(L)
Contract Lines'
ClickHit field '(L)
Contract Lines'
NewActiveWin dictionary 'FieldService' form 'SVC_Contract_Maintenance' window
'SVC_Contract_Line_Entry'
ScrollByLine down scrollwin
'SVC_Contract_Line_Entry_Scroll'
ScrollByLine down
scrollwin 'SVC_Contract_Line_Entry_Scroll'
ScrollByLine down
scrollwin 'SVC_Contract_Line_Entry_Scroll'
ScrollByLine down
scrollwin 'SVC_Contract_Line_Entry_Scroll'
ScrollByLine down
scrollwin 'SVC_Contract_Line_Entry_Scroll'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field 'Item Number'
# Key 1: '0', '', '0.00000'
TypeTo line «Line» scrollwin 'SVC_Contract_Line_Entry_Scroll'
field 'Item Number' , '«ServiceItemNumber»'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field '(L) Serial Number'
ActivateWindow dictionary 'FieldService' form 'SVC_Contract_Maintenance' window
'SVC_Contract_Line_Entry'
# Key 1: '0', '', '0.00000'
TypeTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field '(L) Serial Number' , '«EquipmentNumber»'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field 'U Of M'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field QTY
# Key 1: '0', '', '0.00000'
TypeTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field QTY , '1'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field '(L) SVC Monthly Price'
ActivateWindow dictionary 'FieldService' form 'SVC_Contract_Maintenance' window
'SVC_Contract_Line_Entry'
# Key 1: '0', '', '0.00000'
TypeTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field '(L) SVC Monthly Price' , '«RlCost»'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field 'Item Description'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field 'Config Reference'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field 'Start Date'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field 'End Date'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field 'Bill Start'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field 'Bill End'
# Key 1: '0', '', '0.00000'
MoveTo line «Line» scrollwin
'SVC_Contract_Line_Entry_Scroll' field '(L) Total'
MoveTo field 'OK
Button'
ClickHit field 'OK
Button'
NewActiveWin dictionary 'FieldService' form 'SVC_Contract_Maintenance' window
'SVC_Contract_Maintenance'
MoveTo field 'Save
Button'
ClickHit field 'Save
Button'
Thursday, October 10, 2013
Dynamics GP Manufacturing - Wrong quantities are calculated in the Planned Qty to Backflush
Problem:
When doing a receipt for an MO, the backflush quantities do not calculate correctly for inventory items.
This happens whenever you recieve additional receipts against a completed MO
When doing a receipt for an MO, the backflush quantities do not calculate correctly for inventory items.
This happens whenever you recieve additional receipts against a completed MO
Tuesday, October 8, 2013
Dynamics GP - Cash Receipts - Another user is editing this document
RM00401 has the transaction status record.
Delete this record to free up the document.
delete from RM00401 where docnumbr = 'mydocnum'
Delete this record to free up the document.
delete from RM00401 where docnumbr = 'mydocnum'
Dynamics GP - How to fix average cost
Problem:
Average cost is all wrong because of data entry errors.
Solution:
If you want to reset the costing for all of your inventory, the simplest method is to remove all of your stock, adjust the iv10200 table to set the qtyonhnd to 0 for the last record, then adjust everything back in at the proper cost.
http://support.microsoft.com/kb/2706556
If you kno wthe exact problem, or the exact cost layer that's causing the bad average calculation, just modify the cost layer by going to
Tools>Utilities>Inventory>Adjust Costs
This will adjust the specific cost layer, and post the differences to the GL.
If the price still does not look correct, run an inventory reconcile on the item to have it recalculate the average cost.
Average cost is all wrong because of data entry errors.
Solution:
If you want to reset the costing for all of your inventory, the simplest method is to remove all of your stock, adjust the iv10200 table to set the qtyonhnd to 0 for the last record, then adjust everything back in at the proper cost.
http://support.microsoft.com/kb/2706556
If you kno wthe exact problem, or the exact cost layer that's causing the bad average calculation, just modify the cost layer by going to
Tools>Utilities>Inventory>Adjust Costs
This will adjust the specific cost layer, and post the differences to the GL.
If the price still does not look correct, run an inventory reconcile on the item to have it recalculate the average cost.
Monday, October 7, 2013
Dynamics GP - this document number already exists you must provide a new document number
If you have "Delete documents" unticked for a specific sales order type, GP will automatically create a record for each SOP transaction in the SOP30200 and SOP30300 tables from the moment you enter any transaction for that document type, even if you don't post it.
To resolve this, tick the "Delete documents" for the sales order type, and delete the records in the sop30200 and sop30300 tables that should not be there.
To resolve this, tick the "Delete documents" for the sales order type, and delete the records in the sop30200 and sop30300 tables that should not be there.
Dynamics GP eOne SmartConnect - Could not log on to SQL server
If you get any errors pertaining to logging on or connecting to SQL, chances are the default Smartconnect user is not setup correctly.
- Install dot net 4.5
- Turn off all firewalls
- Log in to SQL server
- Delete the Smartconnect user (Or confirm the password by logging into SQL with the smartconnect account)
- Reinstall Smartconnect
- Log into GP
- Include Code
- GP>Smartconnect>Build cache
- Launch Smartconnect
Wednesday, October 2, 2013
Dynamics GP - In-Transit Transfer Tables and Views
https://www.gptablereference.com/2010/Group/Field%20Service
SVC00700 - In-Transit Transfer Header Work
SVC00701 - In-Transit Transfer Line Work
IV30300 - Item transaction detail
SVC30700 - In-Transit Transfer History Hdr
SVC30701 - In-Transit Transfer History Dtl
/****** Object: View [dbo].[BI_ITTRHist] Script Date: 03/29/2017 11:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ITTRHist]
AS
SELECT dbo.SVC30700.ORDDOCID, dbo.SVC30700.RFRNCDOC, dbo.SVC30700.LOCNCODE, dbo.SVC30700.TRNSFLOC, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC,
dbo.SVC30701.UOFM, dbo.SVC30701.TRNSFQTY, dbo.SVC30701.QTYFULFI, dbo.SVC30701.QTYSHPPD, dbo.SVC30701.QTY_To_Receive, dbo.SVC30701.QTYRECVD,
dbo.SVC30701.LNITMSEQ, dbo.SVC30701.STATUS, dbo.SVC30701.QTYBSUOM, dbo.SVC30701.TRNSFLOC AS Expr1, dbo.SVC30701.TRNSTLOC,
dbo.SVC30701.Reason_Code, dbo.SVC30701.ITLOCN
FROM dbo.SVC30700 INNER JOIN
dbo.SVC30701 ON dbo.SVC30700.ORDDOCID = dbo.SVC30701.ORDDOCID INNER JOIN
dbo.IV00101 ON dbo.SVC30701.ITEMNMBR = dbo.IV00101.ITEMNMBR
GO
SVC00700 - In-Transit Transfer Header Work
SVC00701 - In-Transit Transfer Line Work
IV30300 - Item transaction detail
SVC30700 - In-Transit Transfer History Hdr
SVC30701 - In-Transit Transfer History Dtl
/****** Object: View [dbo].[BI_ITTRHist] Script Date: 03/29/2017 11:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ITTRHist]
AS
SELECT dbo.SVC30700.ORDDOCID, dbo.SVC30700.RFRNCDOC, dbo.SVC30700.LOCNCODE, dbo.SVC30700.TRNSFLOC, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC,
dbo.SVC30701.UOFM, dbo.SVC30701.TRNSFQTY, dbo.SVC30701.QTYFULFI, dbo.SVC30701.QTYSHPPD, dbo.SVC30701.QTY_To_Receive, dbo.SVC30701.QTYRECVD,
dbo.SVC30701.LNITMSEQ, dbo.SVC30701.STATUS, dbo.SVC30701.QTYBSUOM, dbo.SVC30701.TRNSFLOC AS Expr1, dbo.SVC30701.TRNSTLOC,
dbo.SVC30701.Reason_Code, dbo.SVC30701.ITLOCN
FROM dbo.SVC30700 INNER JOIN
dbo.SVC30701 ON dbo.SVC30700.ORDDOCID = dbo.SVC30701.ORDDOCID INNER JOIN
dbo.IV00101 ON dbo.SVC30701.ITEMNMBR = dbo.IV00101.ITEMNMBR
GO
Tuesday, October 1, 2013
Dynamics GP - Unable to open customizations dictionary
Problem:
This is happening because someone has Dynamics GP open on their machine.
Solution:
Make sure no one is actually using the system, or doing work before kicking them out, or just killing GP.
Any one of the following will work
This is happening because someone has Dynamics GP open on their machine.
Solution:
Make sure no one is actually using the system, or doing work before kicking them out, or just killing GP.
Any one of the following will work
- Ask all users to log out and CLOSE GP
- Disable the network connection to kill all connections to the file, then re-enable it
- If you're on a terminal server, open the task manager, show processes from all users and kill all instances of dynamics*32.exe
Dynamics GP - Smartlist Builder - You do not have security privileges to view all of the tables used in this SmartList
Problem:
Users get the error
"You do not have security privileges to view all of the tables used in this SmartList"
Even though you have set the SQL table security, and the Smartlist security correctly
Solution:
You will need to add permissions to the View SmartLists with SQL Tables operation. Here are the settings under Security Tasks:
Product: SmartList Builder
Type: SmartList Builder Permissions
Series: SmartList Builder Permissions or SmartList Builder (depends on the Service Pack)
Operations: View SmartLists with SQL Tables
Original Post
http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/
Users get the error
"You do not have security privileges to view all of the tables used in this SmartList"
Even though you have set the SQL table security, and the Smartlist security correctly
Solution:
You will need to add permissions to the View SmartLists with SQL Tables operation. Here are the settings under Security Tasks:
Product: SmartList Builder
Type: SmartList Builder Permissions
Series: SmartList Builder Permissions or SmartList Builder (depends on the Service Pack)
Operations: View SmartLists with SQL Tables
Original Post
http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/
Subscribe to:
Posts (Atom)