Faulting module path: C:\WINDOWS\system32\igd10umd32.dll

Uninstall Intel graphics driver 3000 and reinstall.

Dynamics NAV - LS Retail - Block purchasing or sales

Item Status Link - set blocking rules
This affects Purchasing, Sales

Table 39 - Checkitemstatus
C 99001452 - boutils, finditemstatuslink

The suspend routine checks the Document date, and the line location.
If a suspend status is set for a specific time range, that item will be suspended until another active line date is entered.

SQL - how to use temp tables

sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO 

--Select output from sp into temp table

 SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;', 'EXEC getBusinessLineHistory')

SSRS Fails - Reports timeout, SSRS does not export to Excel, Reports take extremely long to run. OutofResources exception error

All of these errors are related to having some sql or ssrs function or formula that is being fed nulls.

• Ensure that all lookup queries do not return any nulls for drop-down lookups
• Ensure that no nulls are passed to any SQL formulas in views you are using
• Ensure that no nulls are passed to any SSRS formulas in your report
• Ensure that all SSRS outputs are 0 or "(none)" but never NaN or Null or Infinite
• All nulls should be handled in SQL by ISNULL([field],0) or ISNULL([field],"(none)")
• All nulls should be handled in SSRS by
• =iif(isNothing(Fields!FV1.Value), Fields!FV2.Value, Fields!FV1.Value)

LS Retail - Create a Button that prompts an Infocode

Use the INFO_K function on the button
Parameter  = Infocode Code

Dynamics GP - When running Item Check Links you get the error 'A get/change next operation on table "IV_Item_MSTR" failed. A records was already locked.'

https://www.gpug.com/communities/community-home/digestviewer/viewthread?GroupId=247&MID=30191&tab=digestviewer&CommunityKey=4754a624-39c5-4458-8105-02b65a7e929e


The IV00101.Purchase_Tax_Options field for those records is 0.
Valid values are

1 – Taxable

2 – Nontaxable
3 – Base on customer / vendor


Update all affected items and item classes accordingly to resolve the error message.


--Alternatively, you can also run this script to set all items without a purchase tax schedule to non-taxable.
--Update Items

select * from iv00101 where Purchase_Tax_options = 0

update iv00101 set Purchase_Tax_Options = 2 where Purchase_Tax_options = 0

--Also run this to update the item classes to prevent new items from starting with 0
--Update Item Classes

select * from iv40400 where Purchase_Tax_options = 0

update iv40400 set Purchase_Tax_Options = 2 where Purchase_Tax_options = 0

Dynamics GP - SQL View - SRMaster - Salesperson and Territory

CREATE VIEW [dbo].[BI_SRMaster]
AS
SELECT     dbo.RM00301.SLPRSNID, dbo.RM00303.SALSTERR, dbo.RM00303.SLTERDSC, dbo.RM00301.EMPLOYID, dbo.RM00301.SLPRSNFN, dbo.RM00301.SPRSNSLN,
                      RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SrNm, RTRIM(dbo.RM00301.SLPRSNID) + ' | ' + RTRIM(dbo.RM00301.SALSTERR)
                      + ' | ' + RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SrTerrLbl, dbo.RM00301.STATE, dbo.RM00301.INACTIVE AS SRInactive,
                      dbo.RM00303.INACTIVE AS TerrInactive
FROM         dbo.RM00301 INNER JOIN
                      dbo.RM00303 ON dbo.RM00301.SALSTERR = dbo.RM00303.SALSTERR

GO

Dynamics GP - Force-Changing Item Valuation Methods

IV00101 - Item Master
VCTNMTHD - Valuation method 1 - FIFO, 3-Average

If you have any open sales orders, it will prevent the change valuation routine from running.

You can work around this issue using the following method

https://dynamicsuser.net/gp/f/users/40649/inventory-valuation-change

• This method is not perfect, and may cause costing errors in the future. If the cost of your stock needs to be accurately maintained, all stock should be removed, valuation changed, then re-entered
• This method should not be used in a production environment
• Backup the current sop10200 table to track the original amounts and data
• select * into sop10200_bak from sop10200
• Backup the current iv00102 table to track original allocated amounts
• select * into iv00102_bak from iv00102
• Set all sop lines to non-inventory
• Update sop10200 set NONINVEN = 1
• Remove all allocated stock temporarily
• update sop10200 set atyalloc = 0
• update iv00102 set ATYALLOC = 0
• Zero out all stock using inventory adjustments
• Run Valuation Change Utility
• Put back in all stock using inventory adjustments
• Set the allocation back to the original values 
• update sop10200 set sop10200.atyalloc = sop10200_bak.atyalloc inner join sop10200_bak on sop10200_bak.dex_row_id = sop10200.dex_row_id
• Set the non-inventory flag back to original values
• update sop10200 set sop10200.noninven = sop10200_bak.noninven inner join sop10200_bak on sop10200_bak.dex_row_id = sop10200.dex_row_id
• Set the allocated qty back to original values
• update iv00102 set iv00102.atyalloc = iv00102_bak.atyalloc inner join iv00102_bak on iv00102_bak.dex_row_id = iv00102.dex_row_id
• Run reconcile and check links on everything

Dynamics GP - SQL Views - ItemMaster, ItemQty

SELECT     dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.UOMSCHDL, dbo.IV40201.BASEUOFM, dbo.IV40201.UMDPQTYS, dbo.IV00101.ITMCLSCD, dbo.IV00101.CURRCOST, dbo.IV00101.ITMTRKOP, dbo.IV00101.LOTTYPE, dbo.IV00101.CREATDDT,
                  dbo.IV00101.MODIFDT, DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS AgeDays, dbo.IV40400.ITMCLSDC, dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.USCATVLS_4, dbo.IV00101.USCATVLS_5,
                  dbo.IV00101.USCATVLS_6, RTRIM(dbo.IV00101.ITEMNMBR) + ' | ' + RTRIM(dbo.IV00101.ITEMDESC) AS ItemLbl, LastDt.LastTrxDt,
                  CASE WHEN ITEMTYPE = 1 THEN 'Sales Inventory' WHEN ITEMTYPE = 2 THEN 'Discontinued' WHEN ITEMTYPE = 3 THEN 'Kit' WHEN ITEMTYPE = 4 THEN 'Misc Charges' WHEN ITEMTYPE = 5 THEN 'Services' WHEN ITEMTYPE = 6 THEN 'Flat Fee' END AS ItemTypeDesc,
                   dbo.IV00101.ITEMTYPE
FROM        dbo.IV00101 INNER JOIN
                  dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDL LEFT OUTER JOIN
                      (SELECT     ITEMNMBR, MAX(DOCDATE) AS LastTrxDt
                       FROM        dbo.IV10201
                       GROUP BY ITEMNMBR) AS LastDt ON dbo.IV00101.ITEMNMBR = LastDt.ITEMNMBR LEFT OUTER JOIN
                  dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD

/****** Object:  View [dbo].[BI_ItemQty]    Script Date: 03/10/2017 09:13:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ItemQty]
AS
SELECT     dbo.IV00102.ITEMNMBR, dbo.IV00102.LOCNCODE, dbo.IV00102.QTYONHND, dbo.IV00102.ATYALLOC, dbo.IV00101.ITMTRKOP,
                      dbo.IV00102.QTYONHND - dbo.IV00102.ATYALLOC AS QtyAvlbl, dbo.IV00101.USCATVLS_2 AS Category, dbo.IV00101.CURRCOST,
                      dbo.IV00102.QTYONHND * dbo.IV00101.CURRCOST AS CostOnHand, dbo.IV00101.ITEMDESC,
                      CASE WHEN itmclscd LIKE '%fg%' THEN 'Finished Goods' ELSE 'Other' END AS FGOTH
FROM         dbo.IV00102 INNER JOIN
                      dbo.IV00101 ON dbo.IV00102.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE     (dbo.IV00102.LOCNCODE > '') AND (dbo.IV00102.QTYONHND <> 0) AND (dbo.IV00101.ITMTRKOP = 1)

GO

Registering Integration Manager

The IM keys do not need to match the GP keys

Dynamics GP - National Accounts - Parent customer cannot see child invoices in apply sales documents screen

Tables

• RM00105 - National Account master
• RM00101 - CPRCSTNM - Defines parent account

Solution

• The parent account needs to have itself as a value in the CPRCSTNM field
• run this script to set all national account parents to themselves

update rm00101 set cprcstnm = custnmbr where custnmbr in (select cprcstnm from RM00105)