SQL 2012 - Enterprise Edition - Evaluation period has expired - Downgrade to SQL Standard Edition

https://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/

• You cannot downgrade from enterprise.
• You must backup your databases, or capture your mdf and ldf files
• Uninstall SQL completely
• Reinstall the correct version of SQL
• Restore all databases

Dynamics GP - Modifier, VBA Visual Basic, Sanscript, ADO Loop SQL Query to populate a dynamic drop-down list, and maintain arrays of result values

    'Global Variables
    Dim TheLoan(1 To 50)
    Dim TheRemLoanAmt(1 To 50)

Private Sub DDLM158_Changed()
If DDLM158 > 1 Then
PONumber = TheLoan(DDLM158 - 1)
LoanAmt = TheRemLoanAmt(DDLM158 - 1)
Else
PONumber = ""
LoanAmt = 0
End If
End Sub

Private Sub Purchases_Changed()
If VendorID > "" And CCur(Purchases) > CCur(IIf(LoanAmt = "", 0, LoanAmt)) And LoanAmt > "0" Then
MsgBox "Purchases cannot exceed Loan Amount of " & CCur(LoanAmt) & ". Please enter a new amount to purchase."
Purchases.Focus
End If
End Sub
Private Sub VendorID_Changed()
If DocumentType = 3 And VendorID > "" Then
DDLM158.Enabled = True
    'Dim CompilerApp As New Dynamics.Application
    Dim CompilerApp As Object
    Dim CompilerMessage As String
    Dim CompilerError As Integer
    Dim CompilerCommand As String
    ' Create link without having reference marked
    Set CompilerApp = CreateObject("Dynamics.Application")
   
    CompilerCommand = ""
    CompilerCommand = CompilerCommand & "clear '(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry;" & vbCrLf
    CompilerCommand = CompilerCommand & "add item "" <Available Loans>"", 0" & vbCrLf
    CompilerCommand = CompilerCommand & "   to '(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry;" & vbCrLf
    CompilerCommand = CompilerCommand & "'(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry = 1;" & vbCrLf
'Populate dropdown
    Dim objRec
     Dim objConn
     Dim cmdString
     Dim i
   
     i = 1
     Set objRec = CreateObject("ADODB.Recordset")
     Set objConn = CreateObject("ADODB.Connection")
   
     objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DEVDB;Initial Catalog=TWO;User Id=sa;Password=pw"
     objConn.Open
   
   
     cmdString = "SELECT LoanID, RemLoanAmt, VENDORID FROM BI_AvlblLoans where (VendorID='" + VendorID + "')"
   
     Set objRec = objConn.Execute(cmdString)
   
       If Not (objRec.EOF And objRec.BOF) Then
            Do While Not objRec.EOF 'Start looping through the records
                'Grab record Data
                TheLoan(i) = RTrim(objRec!LoanID)
                TheRemLoanAmt(i) = objRec!RemLoanAmt
                'add a dropdown line
                If Not VendorID.Empty Then
                CompilerCommand = CompilerCommand & "add item " & """" & TheLoan(i) & " | " & TheRemLoanAmt(i) & """" & ", " & i & vbCrLf
                CompilerCommand = CompilerCommand & "   to '(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry;" & vbCrLf
                i = i + 1
                End If
                objRec.MoveNext
            Loop
        End If
   
    objConn.Close
   
    ' Execute SanScript
    CompilerApp.CurrentProductID = 0 ' DYNAMICS
    CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
    CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)
    If CompilerError <> 0 Then
        MsgBox CompilerMessage
    End If
Else
DDLM158.Enabled = False
End If
    
    
End Sub
Private Sub Window_AfterOpen()
DDLM158.Enabled = False
    'Dim CompilerApp As New Dynamics.Application
    Dim CompilerApp As Object
    Dim CompilerMessage As String
    Dim CompilerError As Integer
    Dim CompilerCommand As String
    ' Create link without having reference marked
    Set CompilerApp = CreateObject("Dynamics.Application")
   
    CompilerCommand = ""
    CompilerCommand = CompilerCommand & "clear '(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry;" & vbCrLf
    CompilerCommand = CompilerCommand & "add item "" <Available Loans>"", 0" & vbCrLf
    CompilerCommand = CompilerCommand & "   to '(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry;" & vbCrLf
    CompilerCommand = CompilerCommand & "'(L) DDLM158' of window PM_Transaction_Entry of form PM_Transaction_Entry = 1;" & vbCrLf
    ' Execute SanScript
    CompilerApp.CurrentProductID = 0 ' DYNAMICS
    CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
    CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)
    If CompilerError <> 0 Then
        MsgBox CompilerMessage
    End If
End Sub

Dynamics GP - Modifier and VBA Visual Basic - Dynamic Drop down list from sql view using sanscript

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2008/07/24/hybrid-dynamically-populating-a-modifier-added-list-example/

Powerpoint Training Agenda

Formatting text - 10 mins

Changing templates and themes - 10 mins

Modifying Master Slides - 5 mins

Using photos, shapes, and charts in PowerPoint - 15 mins

Adding video - 5 mins

Animating your slideshow - 15 mins

Excel Training Agenda

• The Basics - 5 mins
• Help – F1, Google
• Creating a New Workbook
• Navigating in Excel
• Ctrl + Arrow
• Shift + Arrow
• End, Home
• Select all, Row, Column
• Previewing and Printing a Worksheet
• Saving a Workbook & Re-opening a saved workbook
• Formatting a Worksheet - 15 mins
• Creating Headers, Footers, and Page Numbers
• Adjusting Page Margins and Orientation
• Adding Print Titles and Gridlines, rows to repeat at top of each page
• Formatting Fonts & Values
• Adjusting Row Height and Column Width
• Changing Cell Alignment
• Adding Borders
• Applying Colors and Patterns
• Using the Format Painter
• Using AutoFormat
• Merging Cells, Rotating Text, and using AutoFit
• Using AutoFill
• Managing your workbooks - 15 mins
• Switching Between Sheets in a Workbook
• Inserting and Deleting Worksheets
• Renaming and Moving Worksheets
• Protecting a Workbook
• Hiding Columns, Rows and Sheets
• Splitting and Freezing a Window
• Inserting Page Breaks
• Advanced Printing Options
• Editing a Workbook - 20 mins
• Entering Date Values and using AutoComplete
• Editing, Clearing, and Replacing Cell Contents
• Cutting, Copying, and Pasting Cells
• Moving and Copying Cells with Drag and Drop
• Collecting and Pasting Multiple Items
• Using the Paste Special Command
• Inserting and Deleting Cells, Rows, and Columns
• Using Undo, Redo, and Repeat
• Checking Your Spelling
• Finding and Replacing Information
• Inserting Cell Comments
• BREAK - 5 mins
• Formulas - 20 mins
• Creating a basic Formula - Math, F4$, Cell References
• Calculating Value Totals with AutoSum
• Editing & Copying Formulas
• Fixing Errors in Your Formulas
• Formulas with Several Operators and Cell Ranges
• Vlookup
• If
• Len, left, right, find
• Working with the Forms Menu - 5 mins
• Sorting, Subtotaling & Filtering Data
• Copy & Paste Filtered Records
• Using Data Validation
• Charts
• Data Analysis & Pivot Tables - 25 mins
• Creating a PivotTable
• Modifying a Pivot Table
• Change Pivot Table Options
• Working with Other Programs - 5 mins
• Inserting an Excel Worksheet into a Word Document
• Modifying an Inserted Excel Worksheet
• Inserting a Linked Excel Chart in a Word Document
• Inserting a Graphic into a Worksheet
• Connect to external data sources
• BREAK - Q&A 5 mins
• Exercises - 1 hr
• Build a Shopping List, make it pretty
• Add calculations
• Add advanced formulas
• Create Chart
• Create Pivot Table, make it pretty
• Create Pivot Chart
• Make changes, watch how everything links

Dynamics GP - Manufacturing Views

 -------------------------------------------------------------------------
--All Manufacturing G/L Entries
---------------------------------------------------------------------------



CREATE VIEW [dbo].[BI_GL_Trx] AS SELECT gl.trx_status, gl.trxdate AS trx_date, gl.jrnentry AS journal_entry, Rtrim(gm.actnumst) AS account_number, Rtrim(ga.actdescr) AS account_description, gl.debitamt AS debit_amount, gl.crdtamnt AS credit_amount, gl.refrence AS reference, gl.sourcdoc AS source_document, gl.ortrxsrc AS originating_trx_source, gl.ormstrid AS originating_master_id, gl.ormstrnm AS originating_master_name, gl.ordocnum AS originating_doc_number, gl.curncyid AS currency_id, gl.lastuser AS last_user, gl.uswhpstd AS user_who_posted, CASE WHEN sourcdoc = 'mfgadj' THEN jrnentry + 1 ELSE jrnentry END AS rctjrn FROM ( SELECT actindx, trxdate, sourcdoc, jrnentry, ortrxsrc, refrence, ordocnum, ormstrid, ormstrnm, debitamt, crdtamnt, curncyid, 'Open' AS trx_status, lastuser, uswhpstd FROM dbo.gl20000 WHERE ( sourcdoc NOT IN ('BBF', 'P/L')) AND ( voided = 0) UNION ALL SELECT actindx, trxdate, sourcdoc, jrnentry, ortrxsrc, refrence, ordocnum, ormstrid, ormstrnm, debitamt, crdtamnt, curncyid, 'History' AS trx_status, lastuser, uswhpstd FROM dbo.gl30000 WHERE ( sourcdoc NOT IN ('BBF', 'P/L')) AND ( voided = 0) UNION ALL SELECT gd.actindx, gh.trxdate, gh.sourcdoc, gh.jrnentry, gh.ortrxsrc, gh.refrence, gd.ordocnum, gd.ormstrid, gd.ormstrnm, gd.debitamt, gd.crdtamnt, gh.curncyid, 'Work' AS trx_status, gh.lastuser, gh.uswhpstd FROM dbo.gl10000 AS gh INNER JOIN dbo.gl10001 AS gd ON gh.jrnentry = gd.jrnentry WHERE ( gh.voided = 0)) AS gl INNER JOIN dbo.gl00105 AS gm ON gl.actindx = gm.actindx INNER JOIN dbo.gl00100 AS ga ON gl.actindx = ga.actindx 　



go

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

--All Manufacturing Receipt Journals

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

CREATE VIEW [dbo].[BI_Jrn_Rct_Index]

AS

SELECT dbo.bi_gl_trx.journal_entry,

dbo.bi_gl_trx.originating_doc_number,

derivedtbl_1.moprctnm

FROM dbo.bi_gl_trx

INNER JOIN (SELECT DISTINCT ivdocnbr,

moprctnm

FROM dbo.ivic0101 AS IVIC0101_1) AS derivedtbl_1

ON dbo.bi_gl_trx.originating_doc_number = derivedtbl_1.ivdocnbr

WHERE ( dbo.bi_gl_trx.source_document = 'ivadj' )

GROUP BY dbo.bi_gl_trx.journal_entry,

dbo.bi_gl_trx.originating_doc_number,

derivedtbl_1.moprctnm



go



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

--All Manufacturing Inventory Transactions

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

CREATE VIEW [dbo].[BI_Mfg_InvTrx]
AS
SELECT        dbo.IV30200.SRCRFRNCNMBR, dbo.IV30200.TRXSORCE, dbo.IV30200.BCHSOURC, dbo.IV30200.BACHNUMB, dbo.IV30200.DOCDATE, dbo.IV30200.GLPOSTDT, dbo.IV30300.ITEMNMBR, dbo.IV00101.ITEMDESC,
                         dbo.IV30300.UOFM, dbo.IV30300.TRXQTY, dbo.IV30300.UNITCOST, dbo.IV30300.EXTDCOST, dbo.IV30300.TRXLOCTN, dbo.GL00105.ACTNUMST AS IVActNum, dbo.GL00100.ACTDESCR AS IVActDesc,
                         GL00105_1.ACTNUMST AS IVOfstNum, GL00100_1.ACTDESCR AS IVOfstDesc, LEFT(dbo.IV00101.ITEMNMBR, 1) AS ItemType
FROM            dbo.GL00100 AS GL00100_1 INNER JOIN
                         dbo.GL00105 AS GL00105_1 INNER JOIN
                         dbo.IV00101 INNER JOIN
                         dbo.IV30200 INNER JOIN
                         dbo.IV30300 ON dbo.IV30200.TRXSORCE = dbo.IV30300.TRXSORCE AND dbo.IV30200.IVDOCTYP = dbo.IV30300.DOCTYPE AND dbo.IV30200.DOCNUMBR = dbo.IV30300.DOCNUMBR ON
                         dbo.IV00101.ITEMNMBR = dbo.IV30300.ITEMNMBR ON GL00105_1.ACTINDX = dbo.IV30300.IVIVINDX ON GL00100_1.ACTINDX = GL00105_1.ACTINDX INNER JOIN
                         dbo.GL00105 ON dbo.IV30300.IVIVOFIX = dbo.GL00105.ACTINDX INNER JOIN
                         dbo.GL00100 ON dbo.GL00105.ACTINDX = dbo.GL00100.ACTINDX
WHERE        (dbo.IV30200.SRCRFRNCNMBR > '')
GO



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

--All Manufacturing BOMS and Components

-----------------------------------------------------------------------------
/****** Object:  View [dbo].[BI_Mfg_BOM]    Script Date: 7/31/2017 2:37:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_Mfg_BOM]
AS
SELECT        TOP (100) PERCENT dbo.IV00101.ITEMNMBR AS BomNumber, dbo.IV00101.ITEMDESC AS BOMName, IV00101_1.ITEMNMBR AS CmpNumber, IV00101_1.ITEMDESC AS CmpName,
                         dbo.BM010115.QUANTITY_I AS CmpQty, dbo.BM010115.UOFM AS CmpUofM, dbo.BM010415.LOCNCODE, dbo.IV40700.STATE, RTRIM(dbo.IV00101.ITEMNMBR) + ' | ' + dbo.IV00101.ITEMDESC AS BomLookup,
                         dbo.IV00101.ITMCLSCD, dbo.IV40400.ITMCLSDC, dbo.IVR10015.ITEMSTATUS_I, dbo.IVR10015.MAKEBUYCODE_I, dbo.IVR10015.ITEMFULFILLMETHOD,
                         CASE makebuycode_i WHEN 1 THEN 'Make' WHEN 2 THEN 'Buy' WHEN 3 THEN 'Make/Buy' END AS MakeBuy,
                         CASE itemfulfillmethod WHEN 1 THEN 'Make to Stock' WHEN 2 THEN 'Make to Order-Manual' WHEN 3 THEN 'Make to Order-Silent' END AS FulfillMethod, dbo.MN010000.NOTETEXT_I AS BomNote,
                         IV00101_1.CURRCOST AS CmpCurrCost, dbo.BM010415.BOMTYPE_I, CASE itemstatus_i WHEN 1 THEN 'Active' ELSE 'Inactive' END AS ItemEngStatus,
                         CASE WHEN iv00101.ITEMTYPE = 2 THEN 'Discontinued' ELSE 'Active' END AS BOMStatus, dbo.BM010415.EFFECTIVEDATE_I AS BOMInDate, CASE WHEN BM010415.EFFECTIVEDATE_I < getdate()
                         THEN 'Active' WHEN BM010415.EFFECTIVEDATE_I = 0 THEN 'Active' ELSE 'Inactive' END AS BOMDateStatus, CASE WHEN iv00101_1.ITEMTYPE = 2 THEN 'Discontinued' ELSE 'Active' END AS BOMCmpStatus,
                         dbo.BM010115.EFFECTIVEINDATE_I AS CmpInDate, dbo.BM010115.EFFECTIVEOUTDATE_I AS CmpOutDate, CASE WHEN EFFECTIVEINDATE_I <= getdate() AND EFFECTIVEOUTDATE_I >= getdate()
                         THEN 'Active' WHEN EFFECTIVEINDATE_I <= getdate() AND EFFECTIVEOUTDATE_I = 0 THEN 'Active' ELSE 'Inactive' END AS CmpStatus
FROM            dbo.MN010000 RIGHT OUTER JOIN
                         dbo.IVR10015 ON dbo.MN010000.NOTEINDX = dbo.IVR10015.MFGNOTEINDEX_I RIGHT OUTER JOIN
                         dbo.BM010415 INNER JOIN
                         dbo.BM010115 ON dbo.BM010415.BOMCAT_I = dbo.BM010115.BOMCAT_I AND dbo.BM010415.ITEMNMBR = dbo.BM010115.PPN_I INNER JOIN
                         dbo.IV00101 ON dbo.BM010415.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                         dbo.IV00101 AS IV00101_1 ON dbo.BM010115.CPN_I = IV00101_1.ITEMNMBR INNER JOIN
                         dbo.IV40700 ON dbo.BM010415.LOCNCODE = dbo.IV40700.LOCNCODE INNER JOIN
                         dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD ON dbo.IVR10015.ITEMNMBR = dbo.BM010415.ITEMNMBR
WHERE        (dbo.BM010115.EFFECTIVEOUTDATE_I > GETDATE()) OR
                         (dbo.BM010115.EFFECTIVEOUTDATE_I = 0)
GO






/*----------------------------------------------------------------------------------------
All Manufacturing Receipts and Components
---------------------------------------------------------------------------------------*/
CREATE VIEW [dbo].[BI_Mfg_Rcts_Cmp]
AS
SELECT TOP (100) PERCENT dbo.bi_mfgbom.bomnumber,
dbo.bi_mfgbom.bomname,
dbo.mop1110.moprctnm,
dbo.mop1110.itemnmbr,
dbo.iv00101.itemdesc,
Isnull(dbo.bi_mfgbom.cmpuofm, dbo.bi_mfg_invtrx.uofm)
AS CmpUofM,
Isnull(dbo.bi_mfgbom.cmpqty, 0)
AS BomQty,
CASE
WHEN cmpuofm = 'DOL' THEN Isnull(cmpqty, 0)
ELSE Isnull(bi_mfg_invtrx.unitcost, currcost) *
Isnull(dbo.bi_mfgbom.cmpqty, 0)
END
AS BomCost,
Isnull(dbo.mop1100.qtyrecvd * dbo.bi_mfgbom.cmpqty, 0
)
AS ExtBomQty,
CASE
WHEN cmpuofm = 'DOL' THEN Isnull(cmpqty, 0)
ELSE Isnull(bi_mfg_invtrx.unitcost, currcost) *
Isnull(dbo.bi_mfgbom.cmpqty, 0)
END * Isnull(dbo.mop1100.qtyrecvd, 0)
AS ExtBomCost,
dbo.mop1110.qtytouse
AS ExtActQty,
CASE
WHEN qtytouse = 0 THEN 0
ELSE Round(item_costs_array_i_1 / mop1100.qtyrecvd,
2
)
END
AS ActUnitCost,
dbo.mop1110.item_costs_array_i_1
AS ExtActCost,
dbo.mop1110.item_costs_array_i_10,
CASE
WHEN dbo.bi_mfgbom.cmpqty IS NULL THEN qtytouse
ELSE dbo.mop1110.qtytouse - dbo.mop1100.qtyrecvd *
cmpqty
END
AS VarQty,
CASE
WHEN dbo.bi_mfgbom.cmpqty IS NULL THEN ( CASE
WHEN cmpuofm = 'DOL' THEN Isnull(cmpqty, 0)
ELSE dbo.mop1110.item_costs_array_i_1
END )
ELSE dbo.mop1110.item_costs_array_i_1 -
( CASE
WHEN
cmpuofm = 'DOL' THEN
Isnull(cmpqty, 0)
ELSE
Isnull(bi_mfg_invtrx.unitcost, currcost
)
*
Isnull(dbo.bi_mfgbom.cmpqty, 0)
END *
Isnull(dbo.mop1100.qtyrecvd, 0) )
END
AS VarCost,
( CASE
WHEN qtytouse = 0 THEN 0
ELSE item_costs_array_i_1 / mop1100.qtyrecvd
END ) - ( CASE
WHEN cmpuofm = 'DOL' THEN
Isnull(cmpqty, 0)
ELSE Isnull(bi_mfg_invtrx.unitcost,
currcost) *
Isnull(dbo.bi_mfgbom.cmpqty, 0)
END )
AS VarUnitCost,
dbo.bi_mfg_invtrx.unitcost,
dbo.mop1100.qtyrecvd,
dbo.mop1100.daterecd,
dbo.mop1100.postdate
FROM dbo.mop1110
INNER JOIN dbo.iv00101
ON dbo.mop1110.itemnmbr = dbo.iv00101.itemnmbr
LEFT OUTER JOIN dbo.bi_mfg_invtrx
ON dbo.mop1110.moprctnm = dbo.bi_mfg_invtrx.moprctnm
AND dbo.mop1110.itemnmbr = dbo.bi_mfg_invtrx.itemnmbr
LEFT OUTER JOIN dbo.mop1100
ON dbo.mop1110.moprctnm = dbo.mop1100.moprctnm
LEFT OUTER JOIN dbo.wo010032
ON dbo.mop1100.manufactureorder_i =
dbo.wo010032.manufactureorder_i
LEFT OUTER JOIN dbo.bi_mfgbom
ON dbo.wo010032.itemnmbr = dbo.bi_mfgbom.bomnumber
AND dbo.mop1110.itemnmbr = dbo.bi_mfgbom.cmpnumber
ORDER BY dbo.mop1110.moprctnm,
dbo.mop1110.itemnmbr

go

　



　

Dynamics GP 2015 - Item Lookup Search by Column fails when using smartlist favourite defaults

Usually on the item lookup screen, you can select a column and type in  a value in the find field to search that column.

However, when you set a smartlist favourite as a default view on the item lookup, it breaks the search capability of the find box.