Friday, February 26, 2016

Dynamics NAV Demo - Finance

  • Foundation
    • Multiple Servers
    • Multiple Companies
    • Home Page
    • Toolbar
    • Navigation Bar
    • Search Bar
    • F1 Help
    • Copy/paste from excel
    • Package Import
    • Web Services
  • Workflows & Approval
    • Workflow Setup - Enable
    • Approval Setup - set workflow admin, unlimited approval
  • Finance
    • Create Users, Permissions
    • Accounting Periods - Create, Close
    • User Setup - Control Posting Dates
    • General Journal - Create and post journal entries
      • Standard Journals
      • Recurring Journals
      • GL Trial Balance
    • Budgets
    • Bank Accounts
    • Bank Rec
    • AR Journals
      • Cash Receipts
      • AR Apply
      • Customer Trial Balance
      • Aged AR
    • AP Journals
      • Check Payments
      • AP Apply
      • Vendor Trial Balance
      • Aged AP
  • Financial Reporting
    • Account Schedule Names
      • Rows
      • Columns
      • Overview

Thursday, February 25, 2016

SSRS - How to alternate row / record colors

Go to the table row's BackgroundColor property and choose "Expression..."
Use this expression:

=iif(RunningValue(Fields![rowgroupfield].Value.ToString,CountDistinct,Nothing) Mod 2,"Gainsboro", "White")

Wednesday, February 24, 2016

Dynamics NAV - LS Retail - Retail Receiving - Purchase Order - Lines display "Not Ordered"

This happens when the original purchase order has an incorrect location code assigned to the lines.
This is only possible if a purchase order is created using the regular purchase order screen instead of the Retail Purchase Order screen.

  • Using the Retail Purchase Order Screen, set the location code for all lines to the correct location.
  • Re-enter all qtys to populate the "qty to receive" correctly

Dynamics GP - Items Received or adjusted in at incorrect cost. How do i fix it?

  • Whenever an item is received at an incorrect cost, this is the full scope of all affected areas
    • Incorrect Unit price on Purchase Receipt
      • Item Cost in FIFO layer is incorrect (if using FIFO costing)
      • Item Cost in Average Costing will calculate incorrectly (if using Average Costing)
      • Incorrect Vendor Invoice total if generating AP invoice from receipt
      • Incorrect Originating Invoice cost will come up the next time you purchase the item
      • Current Cost on the Item card is incorrect
    • Additional errors that snowball if this is not detected
      • Item cost on all transfers is incorrect
      • Item cost on all sales is incorrect
      • Item cost on all returns is incorrect
      • Item cost on all adjustments is incorrect
      • Item cost on all assemblies / manufacturing is incorrect
      • GL Values APPEAR incorrect, but these will match the subledger correctly
        • Users may incorrectly assume the GL is inaccurate, and pass journals to adjust the values which in turn CAUSES the subledger to differ from the GL

  • To Correct this situation
    • Method 1 - No other transactions have happened against this item as yet - Purchase Return
      • If possible, a purchase return should be done immediately to reverse the receipt
      • A proper receipt should be re-done at the correct cost
    • Method 2 - GL is open, Other transactions have already occurred - Adjust Cost Utility
      • Microsoft Dynamics GP > Tools > Utilities > Inventory > Adjust Cost
      • Select The receipt layer at the incorrect cost, and change the cost to the correct cost
      • This will cause the system to go through ALL transactions where that cost layer used, and pass adjusting entries to the GL and Subledger to reflect the new cost

  • If you have also received an incorrect quantity, you can
    • Pass a negative adjustment to remove the stock at the old price, then adjust it back in at the correct price
    • If you have already done the cost adjustment, you can use the Stock count module to set the stock to the correct quantity

Friday, February 19, 2016

Dynamics GP - Importing customization package does not add visual basic fields. Cannot add fields to Visual Basic

  • Ensure you are logged in as administrator
  • Set the permissions of the c:\program file\Microsoft dynamics\gp folder to give full control, replace all permissions on subfolders
  • Delete your c:\program files\Microsoft dynamics\gp2013\dynamics.vba file
    • reimport the package
    • or re-add your fields

Monday, February 15, 2016

Dynamics NAV - LS Retail - Setup Automated Replenishment

  • Pre-Requisites
    • Setups
      • Replenishment Setup
        • Store items ranged by: Store Groups (this does not affect automation)
        • Stock out functionality: ticked
      • Retail Item Card
        • Distribution
          • Type: Store
          • Code: All
          • Status: active
          • Ordered by: Central
          • Ordering method: calculate
        • Merchandising/Replenishment Control Data
          • Exclude from replenishment: false
      • Replen. Template
        • Create Orders Automatically: Create Orders Automatically (Create immediately) or Put Creation of Orders on Batch Posting Queue  (queues creation if there are locking issues)
        • Replenishment Batch
          • Run Frequency: Daily (other frequencies will work once, but daily will keep incrementing the date
          • Next Run Date: not blank, less than current date (a blank or future date will prevent automatic run)
      • Location
        • Replenishment
          • Active for autom.  replenishm. : true
    • Automated Jobs - setup all of these to run daily one after the other
      • C10012200 Replen. - Calc. Qtys
        • Calculates how much to replenish
      • C10012215 Replen. Rule Calculation
        • Calculates replenishment qtys for planned cross dock and buyer push
      • R10012207 Replen. Automatic Run
        • Runs C10012201 for all filtered records
        • Populates Replenishment Journals and creates PO's if option is set on template
      • C10012203 Replen. Out of Stock Mgt.
        • Calculates if item is out of stock

Friday, February 12, 2016

SSRS - how to pass a multi-valued parameter to a stored procedure

Solution obtained from:

To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:
=join(Parameters!<your param name>.Value,",")
This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN clause:
Google search any UDF string parser online. There are many to choose from. I used 
dba_parseString_udf from Michelle Ufford,
Once I had my UDF installed, I can now alter my IN clause to receive the new multi-valued parameter being passed by SSRS as followed:
WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))
Therefore, SSRS will pass the following value:
@Flag = 'A,B,C'
Then my UDF will parse that string out correctly to:
And populate my @Flag parameter correctly with SELECT * FROM UDF()...

Wednesday, February 10, 2016

Dynamics GP - Close partial In-Transit Transfer documents and move them to history

The first step is the update this record in the SVC00700 to a 6. After you have updated the record to a status of 6, you can run two stored procedures to move the record to the history table. Here are the two scripts:
exec SVC_Transfer_TransferToHistory 'TransferNumber'

Thursday, February 4, 2016

Dynamics GP - Web Client - Self signed Certificate for more than 1 year

Makecert has been deprecated, and powershell is now used

New-SelfSignedCertificate -Subject “RDS.Contoso.local” -DnsName “”, “” -CertStoreLocation “cert:\LocalMachine\My” -KeyAlgorithm RSA -KeyLength 2048 -KeyExportPolicy Exportable -NotAfter (Get-Date).AddYears(5)

Wednesday, February 3, 2016

Dynamics NAV - Purchasing - Overhead Applied account must have a value

This occurs when a Purchase Line has a value in the Indirect Cost % field.

This field automatically populates if the user changes the value in the Unit Cost (LCY) field.
NAV will assume that the difference between the Direct Unit Cost and the Unit Cost (LCY) field should be populated to the Indirect Cost %

To resolve this issue, clear the Indirect Cost % field on each affected line to allow the Unit Cost (LCY) field to recalculate/

Tuesday, February 2, 2016

Dynamics GP - Backup and Disaster Recovery - Best Practices

  • GP File Backups
    • Backup all Dictionary files in network shares
    • Backup all Dictionary files on local machines if users are using local customized dictionaries
    • Export all customizations and backup all package files
      • Tools>Customization>Customization Maintenance
      • Select All
      • Export
      • If clients are using separate customized dictionaries, export customizations for each client
    • (Optional) Backup the Dynamics GP folder on the server
  • SQL Backups
    • Take a FULL backup of the DYNAMICS database and all company databases on a daily basis
    • If this proves to be too large, differential backups should be taken daily, and FULL backup set weekly
  • VM Backups
    • If the SQL server is on a VM, keeping a copy of the VM is the easiest way to take a backup of the system
    • This should be done IN ADDITION to all of the above backups
    • This will only guarantee a working VM. There is always a chance the SQL within the VM can get damaged, and you will need your SQL backups