Excel - Convert anything to proper Text instead of a number, or text that does not match in vlookup

= Text (A1, “0”)

Dynamics GP - Remove Lot Tracking or Serial Tracking from a number

Adjust all stock out of the number
Manually change the Tracking option
Or run this script

--Get stock
select itemnmbr,qtyonhnd*-1,locncode,atyalloc from iv00102 where locncode = 'myloc' and QTYONHND <> 0
--Get serial numbers
select itemnmbr, SERLNMBR, locncode from iv00200 where locncode = 'F-S' and itemnmbr in (select itemnmbr from iv00102 where locncode = 'F-S' and QTYONHND <> 0)
--Change tracking option
update iv00101 set itmtrkop = 1 where Itemnmbr = 'myitem'

Dynamics GP - Get Serial Numbers for current stock in a location

select * from iv30400 where itemnmbr in (select itemnmbr from iv00102 where locncode = 'F-S' and QTYONHND <> 0)

Dynamics NAV - New Mediaset Images do not display in Image Factbox if mass imported

Modify the factbox and add the field "Image BLOB"

Dynamics NAV - You do not have the following permissions on TableData

Resolution - Set the permissions on your codeunit directly in the codeunit properties

https://www.myerrorsandmysolutions.com/you-do-not-have-the-following-permissions-on-tabledata/

Clear any records in the offending table, this will prevent the license from checking that table

If you can login as SUPER with no error message, then the issue is with your permissions, not the license
Create or modify a permission set to include permissions to the offending object

Dynamics NAV - LS Retail - OMNI - Disable Security Token

In the LSOMNI.Appsettings database set Security_Validatetoken = False
Restart NAV service
Restart LS Omni Windows Service

Excel - Filter Data Connections with Values in Sheet

https://stackoverflow.com/questions/14346563/excel-send-multiple-values-in-command-text

1. Get Date>From Other Sources>Use Microsoft Query
1. DO NOT USE Connect to SQL
2. In the SQL, use ? to define parameters
3. Select a field on the sheet to be used for the parameter value when prompted

Dynamics GP - PSTL Item Site Combiner - Delete existing transfers between sites

--PSTL Item Site combiner cannot run if there are transfers between the two sites you are trying to combine.
--Delete all transfer transactions in the IV30200 that move stock between the two locations you're trying to combine

IV30200 Trx Hist Header
IV30300 Trx Amount History
IV30301 Trx Amount Detail History
IV30500 Distribution History

--------------------------------------------------------------------------------------
declare @LOC1 as varchar(20) = 'WIP'
,@LOC2 as varchar(20) = 'MACOYA'

Select * from IV30200
--DELETE * from IV30200
WHERE IVDOCTYP = 3 and DOCNUMBR IN (
--Find all Transaction lines between the two locations
select DOCNUMBR from IV30300 where ((TRXLOCTN = @LOC1 and TRNSTLOC = @LOC2 and DOCTYPE = 3) OR
(TRXLOCTN = @LOC2 and TRNSTLOC = @LOC1 and DOCTYPE = 3))
)

Select * from IV30301
--DELETE * from IV30301
WHERE DOCTYP = 3 and DOCNUMBR IN (
--Find all Transaction lines between the two locations
select DOCNUMBR from IV30300 where ((TRXLOCTN = @LOC1 and TRNSTLOC = @LOC2 and DOCTYPE = 3) OR
(TRXLOCTN = @LOC2 and TRNSTLOC = @LOC1 and DOCTYPE = 3))
)

Select * from IV30500
--DELETE * from IV30500
WHERE IVDOCTYP = 3 and DOCNUMBR IN (
--Find all Transaction lines between the two locations
select DOCNUMBR from IV30300 where ((TRXLOCTN = @LOC1 and TRNSTLOC = @LOC2 and DOCTYPE = 3) OR
(TRXLOCTN = @LOC2 and TRNSTLOC = @LOC1 and DOCTYPE = 3))
)

Select * from IV30300
--DELETE * from IV30300
WHERE DOCTYP = 3 and DOCNUMBR IN (
--Find all Transaction lines between the two locations
select DOCNUMBR from IV30300 where ((TRXLOCTN = @LOC1 and TRNSTLOC = @LOC2 and DOCTYPE = 3) OR
(TRXLOCTN = @LOC2 and TRNSTLOC = @LOC1 and DOCTYPE = 3))
)

Dynamics NAV - LS Retail - OMNI - How to setup OMNI 2.4.4

• Import new C10012900 WI LS MGT for v110.06.00.490 and all other required objects
• Confirm that General Ledger Setup>LCY Code is a Valid code that matches an existing Currency Code
• Install OMNI Server
• Install OMNI windows Service - creates omni db
• Check web service setup
• Confirm web requests available
• Import newest web requests
• Validate web requests
• Import newest associated objects
• This is very important, replecommitems does not work if this is not done
• Create Distribution Group for ECOMM, Webshop, and add only the Web Store WEBSHOP in the subgroup
• Store Group List - Set the web store to have it's own distribution list and subgroup
• Do not leave the webshop in the same distribution group as all other stores
• Add member WEBSHOP to the subgroup
• run "truncate table [CRONUS$Preaction]" to clear any existing actions
• run "truncate table [CRONUS$WI Item With Daily Updates]" to clear any previous items
• Create all of the sync jobs by running page "WI Default Data"
• WI Stores-Set default webshop store
• Set Store Vat Bus Posting Group
• Round small decimals to 0.01
• 10012900 WI LS Mgt
• UpdateWI_LS_ProductStockQuantity(VAR WILSProduct_p : Record "WI LS Product";VAR Item_p : Record Item)
•   //UpdateWI_LS_ProductStockQuantity
•   IF WILSProduct_p.NavParentGroupedProductId = '' THEN
•     WILSProduct_p.StockQuantity := Item_p.CalcPhysInventory(WILSProduct_p.NavPK,'',StoreLocation_g)
• This piece of code breaks if you have decimals, change it to round down to 0 decimals
• WILSProduct_p.StockQuantity := Round(Item_p.CalcPhysInventory(WILSProduct_p.NavPK,'',StoreLocation_g),0,'<')
• Create POS Startup Data
• Set Item Distribution for all items for the web store with attributes and a photo
• Run ECOMMERCE JOB (C10012900 WI LS MGt-this always runs for ALL items)
• Check T10012867 WI Items with Daily Updates
• T10012912 is populated with all items to replicate to omni
• Clear all preactions, then run ecommerce job
• -ReplEcommItems uses the Preaction table to control the final action sent per item
• 10012861 WI Price provides prices to OMNI
• This table is populated when
• Sales Price has been set with a Vat Product Posting group that matches the store
• VAT Product Posting group is updated on item card
• A package import to update the vat field with the same value counts as an update to trigger the sync
• Run C10012870 WI Mgt Priceonly to update the WI Price Table
• If you're getting trouble syncing items and prices, ensure you run the jobs in this order
• Web store is in it's own store group
• Item distribution is set to the web store group
• Item Prices have the same Vat Prod Posting group as web store
• Item has attributes and images
• Item quantities are in whole numbers, or mod the codeunit to round to 0 dp
• Run ECOMMERCE JOB (C10012900 WI LS Mgt) to populate WI Products based on Item Distribution (Setup job to run every hour, updates items)
• Run C10012870 WI Mgt Priceonly to update the WI Price Table based on the WI Products table (Setup Job to run every 10mins, updates stock and prices)

• 10012860 WI Mgt 
• C99009511 WS Request has all WS request codeunits, and fires whenever called

Dynamics GP - 'An error occurred when updating permanent records. Transaction will not post.' - Check for Duplicates

This happens if the transaction number already exists in the posted transaction table.

To check for duplicates, use this script
https://blogs.msdn.microsoft.com/developingfordynamicsgp/2008/12/04/identifying-duplicate-transactions/


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

-- SOP Duplicates
select SOPTYPE, SOPNUMBE, COUNT(*) as [COUNT] from 
(
select SOPTYPE, SOPNUMBE from SOP10100 W
UNION ALL
select SOPTYPE, SOPNUMBE from SOP30200 H
) C
group by SOPTYPE, SOPNUMBE
having COUNT(*) > 1



-- IVC Duplicates
select DOCTYPE, INVCNMBR, COUNT(*) as [COUNT] from 
(
select DOCTYPE, INVCNMBR from IVC10100 W
UNION ALL
select DOCTYPE, INVCNMBR from IVC30101 H
) C
group by DOCTYPE, INVCNMBR
having COUNT(*) > 1



-- POP PO Duplicates
select PONUMBER, COUNT(*) as [COUNT] from 
(
select PONUMBER from POP10100 W
UNION ALL
select PONUMBER from POP30100 H
) C
group by PONUMBER
having COUNT(*) > 1



-- POP Receivingss Duplicates
select POPRCTNM, COUNT(*) as [COUNT] from 
(
select POPRCTNM from POP10300 W
UNION ALL
select POPRCTNM from POP30300 H
) C
group by POPRCTNM
having COUNT(*) > 1



-- RM Duplicates
select RMDTYPAL, DOCNUMBR, COUNT(*) as [COUNT] from 
(
select RMDTYPAL, RMDNUMWK as DOCNUMBR from RM10301 W
UNION ALL
select RMDTYPAL, DOCNUMBR from RM10201 W
UNION ALL
select RMDTYPAL, DOCNUMBR from RM20101 O
UNION ALL
select RMDTYPAL, DOCNUMBR from RM30101 H
) C
group by RMDTYPAL, DOCNUMBR
having COUNT(*) > 1



-- PM Duplicates
select DOCTYPE, VCHRNMBR, COUNT(*) as [COUNT] from 
(
select DOCTYPE, VCHNUMWK as VCHRNMBR from PM10000 W
UNION ALL
select DOCTYPE, VCHRNMBR from PM10300 P
UNION ALL
select DOCTYPE, VCHRNMBR from PM10400 M
UNION ALL
select DOCTYPE, VCHRNMBR from PM20000 O
UNION ALL
select DOCTYPE, VCHRNMBR from PM30200 H
) C
group by DOCTYPE, VCHRNMBR
having COUNT(*) > 1



-- IV Duplicates
select IVDOCTYP, DOCNUMBR, COUNT(*) as [COUNT] from 
(
select IVDOCTYP, IVDOCNBR as DOCNUMBR from IV10000 W
UNION ALL
select IVDOCTYP, DOCNUMBR from IV30200 H
) C
group by IVDOCTYP, DOCNUMBR
having COUNT(*) > 1



-- GL Duplicates
select JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, [YEAR], COUNT(*) as [COUNT] from 
(
select WH.JRNENTRY, WH.RCTRXSEQ, WL.SQNCLINE as SEQNUMBR, WL.ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, WH.OPENYEAR as [YEAR] from GL10000 WH JOIN GL10001 WL ON WL.JRNENTRY = WH.JRNENTRY
UNION ALL
select JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, OPENYEAR as [YEAR] from GL20000 O
UNION ALL
select JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, HSTYEAR as [YEAR] from GL30000 H
) C
group by JRNENTRY, RCTRXSEQ, SEQNUMBR, ACTINDX, TRXDATE, CRDTAMNT, DEBITAMT, [YEAR]
having COUNT(*) > 1

Dynamics NAV - LS Retail - OMNI - Enable tracing, Debug and enable logging

You can see the sql statement used in the ReplEcommItems method by enabling the tracing on the Omni server.
C:\LS Retail\LSOmni\LSOmniService
C:\LS Retail\LSOmni\LSOmniWinService
In Nlog.config file on the Omni server change
<logger name="*" minlevel="Debug" writeTo="file" />
to
<logger name="*" minlevel="Trace" writeTo="file" />

Restart the LSOmniService and you can see all sql statements in the Omni server logfile.txt  (C:\LS Retail\LSOmni\logs\logfile.txt)

Dynamics GP - Manufacturing - "You do not have access priveleges to edit this field" after cancelling a linked MO, and trying to cancel the SOP quantity

SOP/MOP Link
https://groups.google.com/forum/#!topic/microsoft.public.greatplains/_MsTrRLp2GQ
http://microsoft.public.greatplains.narkive.com/92kddrvO/sop-mop-linked-orders

Cause:
This occurs when you have created an SOP order, auto-generated an MO, and then cancel the MO, and try to cancel the Order Quantity.
This is because you have not defined a security set to manage the Edit SO Qty option on the SOP Manufacturing setup.

Solution:

• Create Security Set
• Manufacturing>Setup>System>Process Security
• Create set
• Set to password, set a password
• Sales Order Processing Setup>Additional>Manufacturing Sales Order
• Prefs. There's a field called "Edit SO QTY's when MO is attached Process
• Security". You can choose a "security set" to authorize users to edit the
• sales line quantities.

SQL SSRS Sum Error

This happens because data is returning nulls or some other bad data that cannot be summed.
Wrap all values in Cdbl(Salesamt) to convert it to a proper decimal


Sum((IIf(Fields!Capacity.Value Is Nothing, CDbl(0),Fields!Capacity.Value))*(IIf(Fields!EstLF.Value Is Nothing, CDbl(0),Fields!EstLF.Value))*8760)"