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.

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

• https://msdn.microsoft.com/en-us/library/dd339004.aspx
• 
  
• Create Codeunit
• Register Web Service
• Search for Web Services
• Verify availability
• Consume Web Service

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

Dynamics NAV - LS Retail - Open Statement Staff/Terminal filter does not work

Solution:
Remove the User Personalization profile from the user.

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

Dynamics NAV - EPL Label Printing

Common useful EPL commands

q100 - Controls total printable area on label

Update Printline

AND

Update Writeline

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 is setup for another discount that has a higher priority
• If the additional benefit item does not have "Zero Price Valid" ticked
• 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

To set a discount on a single item in a Mix and Match

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 
• 
  

Dynamics NAV LS Retail - POS Inventory Lookup does not work INV_LOOKUP

Other Issues:
POS Inventory Lookup is blank
POS Inventory lookup is unresponsive
https://portal.lsretail.com/Portals/35/LS%20Nav/LS%20Nav%20Documentation/How-To%20documents/How%20to%20Set%20Up%20POS%20Inventory%20Lookup%20-%20LS%20Retail%20NAV.pdf?ver=2015-04-28-234844-327
Solution:

• Check the Retail Product Group>POS Inventory Lookup for each group
• Confirm this table is synchronizing, and is ticked on HO and POS store card
• 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.
• Check the POS Functionality Profile>POS Functionality Profile Web Servers
• Tick Local request
• Ensure all post inventory lookup options are setup for all location profiles, stores and terminals
• Run the Update POS Inventory Lookup Utility - For all stores
• 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
• Clicking the Inventory button in the POS run the POS command INV_LOOKUP
• C99001570
• C99009513 Web Services Client: GetInventoryLookup
• C99009512 WS Functions
• Schedule this to run every hour
• C99008909 LSC Pos Trans Server Utility

If all else fails,

• T99001608 UpdateInventory
• The PTsActive flag comes in True instead of False, and prevents the correct calculation of the "Phys. Inventory" field in C99001570
• Modify the code to add 

    CALCFIELDS("Calc. Phys. Inventory","Calc. Total Sales","Calc. Purch. Order.","Calc. Posted Sales",
      "Calc. Total Inv. Adjmt.","Calc. Posted Inv. Adjmt.");
    "Phys. Inventory" := "Calc. Phys. Inventory";

before the Net Inventory Line (154)

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

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 - Node Name "root"
• Define Destination Table - select table source
• Define Source columns - select field source
• 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

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

http://dynamicsgpblogster.blogspot.com/2011/01/im-troubleshooting-rpc-errors-when.html

• 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

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.

Download Steam Mods without Steam

http://steamworkshopdownloader.com/view/144049879

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

• Configure Database Mail
• SQL Management Studio>Connect to Instance>Management>Database Mail
• Go through the wizard
• Enable Email
• Create Profile
• Use the e-mail address of the account you want to use to send mails
• Enter Account Name (email address)
• Enter email address, Display name, Reply email (all email address)
• Server Name - smtp server address (smtp.office365.com if you're using office 365)
• https://support.office.com/en-in/article/Outlook-settings-for-POP-and-IMAP-access-for-Office-365-for-business-or-Microsoft-Exchange-accounts-7fc677eb-2491-4cbc-8153-8e7113525f6c?ui=en-US&rs=en-IN&ad=IN
• Port - 587 if using office 365
• Tick requires SSL
• Select Basic Authentication
• Fill in office 365 login and password
• Manage profile Security>Tick public>Next, Next
• Right click database mail>Send test email
• Configure GP Email Settings
• GP>Tools>Setup>Company>Email Settings
• Configure Business Alerts
• GP>Tools>Setup>Business Alerts
• Go through the wizard

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

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

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