Tuesday, June 30, 2015

Dynamics NAV - Control access to Page Actions by using Security Permissions

On every Object, there is an AccessByPermission option in the object properties that allows you to set a specific action to be accessible only when a specific permission is granted.

To get this to work, the users must have the FOUNDATION permission set, and must NOT have the BASIC permission set.

Sunday, June 28, 2015

Dynamics NAV -LS Retail- How to setup a custom Web Service Request






Modify an existing Web Request
  • Search for Web Service Setup
  • Go to Web Requests
    • Each Web Request is based on functions available in C99009511 WS Request
    • Manually edit the T99008941 WS Request. Add new records to send new requests
  • Edit Request XML or Response XML

Friday, June 26, 2015

Dynamics NAV - LS Retail - Price change - Price change is not allowed

Check the following

  • Store Permissions
    • Set Higher and Lower Price adjustment
  • Staff Permissions
    • Set Higher and Lower Price adjustment (should take from store)

    • Item Status Link
      • Block manual price change is false
    • Retail Item Card
      • Keying in Price is Not Mandatory

    Thursday, June 25, 2015

    Dynamics NAV - EPL Label Printing

    Common useful EPL commands

    q100 - Controls total printable area on label

    Update Printline
    AND
    Update Writeline

    Wednesday, June 24, 2015

    Dynamics NAV LS Retail - Mix and Match does not work properly

    Any of the following will prevent the Mix & Match from working

    • If an item on your Mix & Match line has "No further discounts" ticked
    • If an item on your Mix & Match is in a special group with "no further discounts"
    • Your validation period ID is not for all time periods

    Otherwise, you should setup the fields as follows
    • Lines>No. Of Items Needed - Minimum amount of this item that must be purchased to qualify for Benefit
    • Lines>Line Group - Assign a line group to specific line items. If the total purchased of the group of line items is greater than or equal to Value1 in the line group, qualify for the Benefit 

    Monday, June 22, 2015

    Dynamics NAV LS Retail - POS Inventory Lookup does not work

    Other Issues:
    POS Inventory Lookup is blank
    POS Inventory lookup is unresponsive

    Solution:

    • Check the pos inventory lookup field on store card.
    • Check the pos inventory lookup field on each Item category card.
    • Check the inventory lookup field in the functionality profile card and select the HO distribution location.
    • Run the Update POS Inventory Lookup Utility
      • Store>Actions>Update POS Inventory Lookup
      • This is codeunit 99008909
    • Enable the Get Inventory Lookup in Web Service Setup>Web Requests
      • Edit the request xml
      • Add the Level 3>Normal>Text> Location_Profile if it's missing 
      • Tick optional


    Thursday, June 18, 2015

    Dynamics NAV 2015 LS REtail - Prices at POS Terminals calculate VAT on top of prices including VAT

    Related Issues:
    Calculate VAT twice
    VAT is added multiple times

    Solution:

    • Confirm All Items ave the correct Vat business Posting Group (PR) set
    • Confirm all items have Vat Included in Selling Price ticked (or not, depending on your preferences)
    • Go to functionality profiles
    • Under amount Fasttab>UnTick the "Add VAT to Prices" box
    • Confirm prices are correct by checking the Prices Overview page

    Wednesday, June 17, 2015

    Dynamics NAV 2015 - Create XMLPort to import flat file

    http://www.archerpoint.com/blog/Posts/nav-development-import-flat-files-xmlports


    • Create XMLPORT
      • Enable Request Page, select filter
      • Define Root 
        • Define Destination Table
          • Define Source columns
        • Define CAL code on Destination - Import OnBeforeInsertRecord


    //Get Doc No from Request Page
    IF NOT (GiftRegLine.GETFILTER("No.")='') THEN
    GRDocNo := GiftRegLine.GETFILTER("No.");

    GiftRegLine.INIT;

    //Populate Lines
    IF EVALUATE(Qty,Quan) THEN BEGIN;
    EVALUATE(Prc,Price);
    NextLine += 10000;

    GiftRegLine."No." := GRDocNo;
    GiftRegLine."Line No." := NextLine;
    GiftRegLine."Item No." := ItemNo;
    GiftRegLine.Quantity :=Qty;
    GiftRegLine.SellingPrice := Prc;
    END
    ELSE BEGIN
    //Skip import line if not a valid line
    currXMLport.SKIP;
    END;



    • Create Button on Page>Actions
      • Set button to Run Object XMLPORT objectNo

    Monday, June 15, 2015

    Integration Manager - RPC Server is unavailable/Not privileged to open this form


    • Under User Preferences, switch the Window Command Display option to Menu Bar. You can then log out of GP and log back in, which should switch the UI.
    • Or, you can use the econnect integrations

    Friday, June 12, 2015

    Office 365 on Remote Desktop Services (Terminal Server)

    You must have an Office 365 ProPlus account.
    You only need one.
    All users can login to Office 365 using the same account.

    Tuesday, June 9, 2015

    Dynamics GP 2015 - There is no more PDK anywhere. PDK has been discontinued.

    You must use the Project>Timesheet entry to enter timesheets.

    Dynamics GP - How to Setup Business Alerts



    For More advanced Email Alerts, you can use SQL to send mail directly

    Original Solution
    http://stackoverflow.com/questions/13300881/how-to-send-email-from-sql-server


    • Create SQL Mail Profile and Account
    • Run this code

    -------------------------------------------------------------------------
    sp_CONFIGURE 'show advanced', 1
    GO
    RECONFIGURE
    GO
    sp_CONFIGURE 'Database Mail XPs', 1
    GO
    RECONFIGURE
    GO
    -------------------------------------------------------------------------

    • Use this code to send a single e-mail
    --------------------------------------------------------------------------

    USE msdb
    GO
    EXEC sp_send_dbmail @profile_name='TEST',
    @recipients='gregory@davyntt.com',
    @subject='Test message',
    @body='This is the body of the test message.',
    @Query = 'select * from iv00101',
    @attach_query_result_as_file = 1
    
    ----------------------------------------------------------------------------

    • Use this code to loop through a table and send multiple emails

    ---------------------------------------------------------------------------------
    DECLARE @email_id NVARCHAR(450),@id BIGINT,@max_id BIGINT,@query NVARCHAR(1000)

    SELECT @id=MIN(id),@max_id=MAX(id) FROM [email_adresses]

    WHILE @id<@max_id
    BEGIN
        SELECT @email_id=email_id
        FROM [email_adresses]

        set @query='sp_send_dbmail @profile_name=''yourprofilename'',
                            @recipients='''+@email_id+''',
                            @subject=''Test message'',
                            @body=''This is the body of the test message."
    '

        EXEC @query
        SELECT @id=MIN(id)FROM [email_adresses] where id>@id

    END
    -------------------------------------------------------------------------------------

    • Use these queries to determine if your mail is sending properly
    -------------------------------------------------------------------------------------
    SELECT * FROM msdb.dbo.sysmail_mailitems
    SELECT * FROM msdb.dbo.sysmail_sentitems
    SELECT * FROM msdb.dbo.sysmail_log

    Cannot uninstall a program

    Use this tool from microsoft to force remove a program

    https://support.microsoft.com/en-us/mats/program_install_and_uninstall

    Monday, June 8, 2015

    Dynamics GP 2015 Upgrade Error - The Stored Procedure SynchronizeTableData() of form DuSQLAccountSynch : 27Pass through SQL returned the following results: DBMS :2601, Microsoft Dynamics GP: 0.

    GP 2015 upgrade Solution here for this ASIEXP86 error and the Action Pane Error
    https://community.dynamics.com/gp/f/32/p/162238/392546#392546




    • Run This

    --------------------------------
    DROP INDEX [AK2ASIEXP86] ON [DYNAMICS].[dbo].[ASIEXP86]

    Create trigger ctrig_SY07240_ii on [DYNAMICS].[dbo].[SY07240] instead of insert as
    begin

    insert [DYNAMICS].[dbo].[SY07240] (ListDictID, ListID, ViewID, CmdParentDictID, CmdParentFormID, CmdParentCmdID, CmdSequence, CmdDictID, CmdFormID, CmdID, Priority, ButtonSize, CmdCaption, Visible)
    select ListDictID, ListID, ViewID, CmdParentDictID, CmdParentFormID, CmdParentCmdID, isnull(CmdSequence, 1), CmdDictID, CmdFormID, CmdID, Priority, ButtonSize, CmdCaption, Visible
    from inserted

    end
    ------------------------------------



    • Run GP Utilities
    • Complete the upgrade
    • Run This

    --------------------------------------------
    begin tran
    WITH CTE AS(
    SELECT [ASI_Favorite_Dict_ID],
    [ASI_Favorite_Type],
    [ASI_Favorite_Save_Level],
    [CMPANYID],
    [USRCLASS],
    [USERID],
    [ASI_Favorite_Name],
    [ASI_Field_Number_Dict_ID],
    [ASI_Field_Number],
    [ASI_Field_Sequence],
    RN = ROW_NUMBER()OVER(PARTITION BY 
    [ASI_Favorite_Dict_ID],
    [ASI_Favorite_Type],
    [ASI_Favorite_Save_Level],
    [CMPANYID],
    [USRCLASS],
    [USERID],
    [ASI_Favorite_Name],
    [ASI_Field_Number_Dict_ID],
    [ASI_Field_Number]
    ORDER BY [ASI_Favorite_Dict_ID],
    [ASI_Favorite_Type],
    [ASI_Favorite_Save_Level],
    [CMPANYID],
    [USRCLASS],
    [USERID],
    [ASI_Favorite_Name],
    [ASI_Field_Number_Dict_ID],
    [ASI_Field_Number], [ASI_Field_Sequence])
    FROM dbo.ASIEXP86
    )
    DELETE FROM CTE WHERE RN > 1
    commit
    ---------------------------------------------------------------------------------------
    USE [DYNAMICS]
    GO
    /****** Object: Index [AK2ASIEXP86] ******/
    CREATE UNIQUE NONCLUSTERED INDEX [AK2ASIEXP86] ON [dbo].[ASIEXP86]
    (
    [ASI_Favorite_Dict_ID] ASC,
    [ASI_Favorite_Type] ASC,
    [ASI_Favorite_Save_Level] ASC,
    [CMPANYID] ASC,
    [USRCLASS] ASC,
    [USERID] ASC,
    [ASI_Favorite_Name] ASC,
    [ASI_Field_Number_Dict_ID] ASC,
    [ASI_Field_Number] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO
    -------------------------------------------------------------------------------------
    DROP TRIGGER [ctrig_SY07240_ii] ON [DYNAMICS].[dbo].[SY07240]

    Dynamics GP - Remove inactive users

    Remove Idle users
    Log out idle users


    Original Link
    http://mohdaoud.blogspot.com/2010/02/script-to-get-users-logged-in-to-gp_2325.html

    Use this to find all idle users, then delete from the activity table. Will post a complete script soon.

    SELECT
    CASE WHEN S.session_id IS NULL THEN 'Missing DEX_SESSION' ELSE '' END MISSING_SESSION,
    CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1 THEN 'Idle for ' + LTRIM(RTRIM(STR(DATEDIFF(mi, P.last_batch, GETDATE())))) + ' minutes.' ELSE '' END AS IDLE_TIME_DESC,
    CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1 THEN DATEDIFF(mi, P.last_batch, GETDATE()) ELSE 0 END AS IDLE_TIME,
    A.USERID,
    A.CMPNYNAM COMPANY_NAME,
    INTERID COMPANY_ID,
    LOGINDAT + LOGINTIM LOGIN_DATE_TIME,
    SQLSESID SQL_SESSIONID,
    P.login_time SQL_LOGINTIME,
    P.last_batch SQL_LAST_BATCH,
    DATEDIFF(mi, P.last_batch, GETDATE()) TIME_SINCE_LAST_ACTION,
    S.session_id SQLSERVER_SESSIONID,
    S.sqlsvr_spid SQLSERVER_PROCESSID,
    P.spid PROCESSID,
    P.status PROCESS_STATUS,
    P.net_address NET_ADDRESS,
    P.dbid DATABASE_ID,
    P.hostname HOSTNAME
    FROM DYNAMICS..ACTIVITY A
    LEFT JOIN DYNAMICS..SY01400 U ON A.USERID = U.USERID
    LEFT JOIN DYNAMICS..SY01500 C ON A.CMPNYNAM = C.CMPNYNAM
    LEFT JOIN tempdb..DEX_SESSION S ON A.SQLSESID = S.session_id
    LEFT JOIN master..sysprocesses P ON S.sqlsvr_spid = P.spid AND ecid = 0
    LEFT JOIN master..sysdatabases D ON P.dbid = D.dbid

    Tuesday, June 2, 2015

    Azure - Windows Server 2012 - Deploy Remote Desktop Management - "Unable to connect to the server by using Windows Powershell Remoting"

    Problem
    "Unable to connect to the server by using Windows Powershell Remoting"

    Solution

    • Login as Cloudadmin
    • Server Manager>Local Server>Enable Remote Management
    • Reboot