Thursday, March 31, 2016

How to Integrate LS One to Dynamics GP

  • Use Database Maps feature in LS One to map the fields to a custom staging database
  • Use Smartconnect to import and export data to and from GP using the staging database

Friday, March 18, 2016

Dynamics NAV - Item Ledger Entries, Item Journals - Negative Adjustment Item Journal shows up as positive Item Ledger Entry

  • This happens when you put a negative value on a line with a Negative Adjustment type
    • For Item Journals, if you use Negative Adjustment, you must enter a positive quantity for the adjustment
    • NAV will switch the sign accordingly when it posts
    • If you put a negative value in for a Negative adjustment, it will switch it to positive
  • It should be noted that this behaviour only occurs for the Item Journals.
  • Physical Inventory Journals require you to enter a negative value for negative adjustments.

Tuesday, March 15, 2016

Smartconnect - Export to a Pipe delimited file

  • Use a calculated field to combine all of the values into a large string delimited by pipes
    • return _ITEMNMBR + "|" + _ITEMDESC + "|" + _ABCCODE + "|" + _USCATVLS_1 + "|" + _UOMSCHDL + "|" + _ITEMCODE

    • Export only that calculated field to your file as the first column
    • Disable headers

    Friday, March 11, 2016

    Dynamics NAV - Purchase Invoice - When posting a purchase invoice, the corresponding G/L lines do not display the line description from the Purchase Invoice

    • C90 Purch.-Post , and C91 Purch.-Post(Yes/No) control this function
    • Search for
    GenJnlLine.Description := "Posting Description";

    • Replace with
    //        GenJnlLine.Description := "Posting Description";
     GenJnlLine.Description := PurchLine."Description";

    Thursday, March 10, 2016

    Dynamics GP - Field Service Demo

    • Setup
      • Field Service Setup - Mark Create Equipment from POP, SOP, MOP
      • Set Items as returnable under Service Extensions to allow return lines
    • Create Equipment
      • Purchase order - 2-A3284A (Serial, Buy)
        • Receive serialized stock to WAREHOUSE
        • GP will create Equipment after posting receipt with status of INSTOCK
      • Sales Order (Can create contract from here if necessary)
        • Sell Serialized item
        • GP will update Equipment after posting sale with status of INSTALL
        • GP will update the equipment with the new customer information
        • SOP Number drillback will not work if Equipment is created from purchase
      • Create Contract
    • Create Work Orders
      • Create work order for equipment support
      • Assign tech
      • Request Stock
        • Parts - Order Qty
      • Assign Stock
        • Inventory Requirements - Create Purchase Order / Transfer Order
        • Inventory Requirements - Create Transfers
          • Pick and Ship Transfer to receive stock in Tech WH
      • Dispatch Tech
      • Update Parts as Invoiced or Returned
      • Complete Work Order
      • Close work order - Enter closing Date
      • RMA Receiving - Receive Returned stock back to Warehouse
    • Bill customer
      • Service Call Billing

    ParseString UDF Function for SQL

    /****** Object:  UserDefinedFunction [dbo].[BI_parseString]    Script Date: 03/10/2016 11:52:38 ******/


    /* Let's create our parsing function... */
    CREATE FUNCTION [dbo].[BI_parseString]
              @stringToParse VARCHAR(8000)
            , @delimiter     CHAR(1)
    RETURNS @parsedString TABLE (stringValue VARCHAR(128))
        Name:       dba_parseString_udf

        Author:     Michelle Ufford,

        Purpose:    This function parses string input using a variable delimiter.

        Notes:      Two common delimiter values are space (' ') and comma (',')

        Date        Initials    Description
        2011-05-20  MFU         Initial Release
        SELECT *
    FROM dba_parseString_udf(<string>, <delimiter>);

    Test Cases:

        1.  multiple strings separated by space
            SELECT * FROM dbo.dba_parseString_udf('  aaa  bbb  ccc ', ' ');

        2.  multiple strings separated by comma
            SELECT * FROM dbo.dba_parseString_udf(',aaa,bbb,,,ccc,', ',');

        /* Declare variables */
        DECLARE @trimmedString  VARCHAR(8000);

        /* We need to trim our string input in case the user entered extra spaces */
        SET @trimmedString = LTRIM(RTRIM(@stringToParse));

        /* Let's create a recursive CTE to break down our string for us */
        WITH parseCTE (StartPos, EndPos)
            SELECT 1 AS StartPos
                , CHARINDEX(@delimiter, @trimmedString + @delimiter) AS EndPos
            UNION ALL
            SELECT EndPos + 1 AS StartPos
                , CharIndex(@delimiter, @trimmedString + @delimiter , EndPos + 1) AS EndPos
            FROM parseCTE
            WHERE CHARINDEX(@delimiter, @trimmedString + @delimiter, EndPos + 1) <> 0

        /* Let's take the results and stick it in a table */
        INSERT INTO @parsedString
        SELECT SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)
        FROM parseCTE
        WHERE LEN(LTRIM(RTRIM(SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)))) > 0
        OPTION (MaxRecursion 8000);



    Wednesday, March 9, 2016

    Dynamics NAV - LS Retail - POS Internal functions

    All POS Internal functions are stored in C99001570 POS Transaction

    Other Functions are located in C99008900 POS Functions

    Dynamics NAV - LS Retail - Calculate Statement - Multiple safe entry lines are duplicated

    This happens when the [Trans_ Safe Entry] table has duplicates for whatever reason.

    To resolve it, delete the duplicated records out of the [Trans_ Safe Entry] table


    SELECT * FROM [Safe Statement Line] where [Statement No_] ='000099'

    SELECT * FROM [Trans_ Safe Entry] where [Store No_] = 'SS' and [Statement Code] = 'MYSTMT' and [Tender Type] in (13,14) order by timestamp

    select * FROM [Trans_ Safe Entry] where [Transaction No_] >= 16943 and [Transaction No_] < 16951 and [POS Terminal No_] = 'SS02'

    delete FROM [Trans_ Safe Entry] where [Transaction No_] >= 16943 and [Transaction No_] < 16951 and [POS Terminal No_] = 'SS02'

    Dynamics NAV - LS Retail - POS - The record is not open

    There is another instance of the Run Client running on the machine, ether another user is logged on, or a previous instance has not closed completely.

    Kill any nav instances in the task manager, or restart the machine.

    Friday, March 4, 2016

    Outlook Runs Slowly - Not Responding - No Plugins

    The pst file most likely needs to be repaired

    • Close Outlook
    • Run C:\Program Files\Microsoft Office 15\root\office15\scanpst.exe
      • "C:\Program Files\Microsoft Office\root\Office16\SCANPST.EXE"
    • Repair any errors
    • If you get the error "Quit outlook and all mail related applications" just wait for about 20 seconds, then try again. Outlook takes some time to actually release the files after you've closed it.