Friday, August 30, 2013

Dynamics GP Extended Pricing - Cost Plus, % Markup

Dynamics GP Extended Pricing DOES NOT DO Cost Plus or Extended Pricing
Myridas Extended Pricing will do this.

Dynamics GP - eOne Flexicoder Does not Launch - Windows do not open

Related Issues:
System hangs
Extender 12.00.0080
Flexicoder 12.0.3

  1. Flexicoder is now integrated with Extender
  2. When you first run flexicoder, it will attempt to scan all extender records and builds some giant temp table forever
  3. To resolve this issue
    1. Install .net 4.5
    2. Export all old extender data using views in sql
    3. record all solution configurations
    4. delete solution
    5. Install newest extender
    6. rebuild solution from scratch
      1. you do not HAVE to rebuild the entire solution, you could probably just add a string to each window, and move it up to be the first field, but i have not tested this.
    7. THE FIRST FIELD ON EVERY WINDOW MUST BE A STRING
    8. reimport all data
    9. Install flexicoder

UPDATE
These issues seem to have been resolved with the latest version of flexicoder on 2013

Wednesday, August 28, 2013

Dynamics GP - Extended Pricing Tables

SOP10109-Price Books
SOP10110-Price Sheets
RM00500-Price sheet cust/book Assignment
IV10400-Price Group Detail
SOP10108-Price Group Headers
SOP40706-Price Book/Price Detail
IV10401-EXTENDED PRICE HEADER
IV10402-EXTENDED PRICE DETAIL
IV10403-Promotional free item detail
SOP10205-Book/Customer assignment


If importing prices, ensure that the Sheet names used are the EXACT same. Remove ALL trailing spaces.

Thursday, August 22, 2013

Dynamics GP - Field Service Tables

https://www.gptablereference.com/2010/Group/Field%20Service

---------------------------------------
Preventative Maintenance Tables
---------------------------------------


Tables found under Fieldservice>Project
--------------------------------------
Service Call Management Tables
---------------------------------------
RM00101 - Customer Master
RM00102 - Customer Address Master

SVC00300 - Equipment Serial Master
SVC00911 - Equipment Status Master

SVC00100 - Tech Master
SVC00905 - Tech Status Codes
SVC00907 - Problem Codes
SVC00901 - Cause Codes
SVC00908 - Repair Codes
SVC00909 - Service Area Codes
SVC00913 - Service Call Status Codes

SVC00200 - Service Call Work
SVC00201 - Service Call Problem, Cause, Repair Codes
SVC00202 - Service Call Equipment
SVC00203 - Parts and Labor Lines
SVC00207 - If Multiple Techs are assigned to a service call, this table is populated, otherwise it is blank

SY03900 - Service Call Note
SVC00220-Service Invoice History
SVC30220-Service Invoice History History
---------------------------------------
Contract Administration Tables
----------------------------------------
SVC00600 - Contract Hdr
SVC00601 - Contract Lines (use a macro import to get these in)

SVC00300 - Serial Numbers for Items
SVC00301 - Posted Meter Readings
SVC00609 - Contract Line Distribution

SVC00300 - Serial Master - Equipment Master
SVC00951 - Extensions-Item

SVC00615 - Contract Billing Temp
-----------------------------------------
Inventory Tables
------------------------------------------
IV30300 - Item transaction detail
SVC00710 - Inventory Requirements Work
SVC30700 - In-Transit Transfer History Hdr
SVC30701 - In-Transit Transfer History Dtl


http://dyndeveloper.com/DynTable.aspx?ModuleID=SVC

Wednesday, August 14, 2013

Dynamics GP - Macro to convert Tab-delimited AP export to Useful Excel

Sub Macro1()
' This will only work for US formatted dates, default AP TB
' Export the APTB as a tab delimited file
' Import the data into a Macro Enabled Excel Sheet
' Copy and paste this code into a VB Module, and run it against your sheet
' It will reformat the data to a more useful state


'
    Dim a, b, TheVend, LineCheck3, LineCheck1, LineCheck7, LC2, LC3, LC5, LC6, LC8, LC15
    a = 0
' this controls how many columns to the right it looks for the outstanding amounts
    b = 6
   
     
'Insert index
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
   

'Insert vendor column
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
   
    Do Until ActiveCell.Offset(a, 2).Value = "Vendor Totals:"
    ActiveCell.Offset(a, 1).Value = a
    LC2 = ActiveCell.Offset(a, 2).Value
    LC3 = ActiveCell.Offset(a, 3).Value
    LineCheck3 = ActiveCell.Offset(a, 4).Value
    LineCheck1 = ActiveCell.Offset(a, 2).Value
    LineCheck7 = ActiveCell.Offset(a, 7).Value
    LC5 = ActiveCell.Offset(a, 5).Value
    LC6 = ActiveCell.Offset(a, 6).Value
    LC8 = ActiveCell.Offset(a, 8).Value
   
        If ActiveCell.Offset(a, 2).Value = "Vendor ID:" Then
        TheVend = ActiveCell.Offset(a, 3).Value
        End If
        If (Len(Trim(LineCheck3)) <= 3) And (LineCheck3 <> "") And (LC2 <> "") And (LC5 <> "") And (LineCheck1 <> "Aged Totals:") Then
        ActiveCell.Offset(a, 0).Value = TheVend
       
        ' scan all columns from the right for the first value it can find for outstanding doc amt
            If LineCheck7 <> "" Then
            ActiveCell.Offset(a, 8).Value = LineCheck7
            Else
                Do While LC8 = "" And b < 15
                LC15 = ActiveCell.Offset(a, b).Value
                ActiveCell.Offset(a, 8).Value = LC15
                LC8 = ActiveCell.Offset(a, 8).Value
                ActiveCell.Offset(a, b).Value = ""
                b = b + 1
                Loop
                b = 6
            End If
        End If
    a = a + 1
    Loop
   
   
' Sort all records with a vendor
 Range("A1:Q50000").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:Q50000")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
' Put in proper headers
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "VendorID"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Index"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "VchrNmbr"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "DocNo"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Type"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "DocDate"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "DueDate"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "OrigDocAmt"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "OSDocAmt"
    Range("A1").Select
   
    ' Copy good records to new sheet
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
   
    ' Remove unwanted characters like $
    Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
       
    'Format amount column as currency
            Columns("H:I").Select
    Selection.Style = "Currency"
End Sub

Thursday, August 8, 2013

Dynamics GP Manufacturing - Set Item Enginering Data for all Items with BOMS to "MAKE"

This situation may happen after you have imported BOMS, and need to switch the items i nthe item engineering data table to "Make"

update ivr10015 set makebuycode_i = 1 where ITEMNMBR in (select ITEMNMBR from BM010415)