Members - Dynamics Communities

David Morinello

Member

August 29, 2023 at 4:25 pm in reply to: RM00101 Custom Trigger Conflict

Up

0

Down

@kerryhataley

I have tried “After Update” and “For Update”.

If I can’t make this work, I may turn to GPPT for a solution. The trigger solution was the quickest or should have been.

David Morinello

Member

August 23, 2023 at 8:56 am in reply to: RM00101 Custom Trigger Conflict

Up

0

Down

I have tried “After Update” and “For Update”.

The inserts into the tracking table are not attempting to insert the Dex_Row_ID. I have attached the SQL for the audit table and for the matching trigger. What am I missing?

Copy Download Link

USE TRUCK
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================================================================
-- Author:		David Morinello
-- Create date: 8/16/2023
-- V 0.5 - Tracks Customer Name, Customer Name, Credit Limit Amount, Payment Terms ID, & Primary BilltoAddress Code
--         to the Table RM00101_Audit_Tracking
-- Description:	Monitor for RM00101 changes to the RM00101_Audit_Tracking Change Audit Table  
-- Proof of Concept Testing with One Field
-- =============================================================================================

--New SQL 2016 command
CREATE OR ALTER TRIGGER [dbo].[tr_RM00101_ChangeTracking]
   ON  RM00101
   FOR UPDATE
AS 
--BEGIN
SET NOCOUNT ON;

IF (UPDATE (CUSTNAME) OR UPDATE (CRLMTAMT) OR UPDATE (PYMTRMID) )  
    --IF (UPDATE(CUSTNAME))
    BEGIN
	-- Audit OLD record
		INSERT INTO RM00101_Audit_Tracking
		SELECT  del.CUSTNMBR     --CUSTNMBR
               ,del.CUSTNAME     --CUSTNAME
		       ,del.CRLMTAMT     --CRLMTAMT
		       ,del.PYMTRMID     --PYMTRMID
			   ,del.PRBTADCD     --PRBTADCD
			   	,'Original'      --ChangeNote
			   ,GETDATE()        --ChangeDate
			   ,ORIGINAL_LOGIN() --ChgUser
	    FROM DELETED AS del 
		    --FROM DELETED AS del INNER JOIN INSERTED AS ins
			   --ON del.CUSTNMBR = ins.CUSTNMBR AND del.ADRSCODE = ins.ADRSCODE
	END

BEGIN
	-- Audit NEW record
		INSERT INTO RM00101_Audit_Tracking
		SELECT  ins.CUSTNMBR
		       ,ins.CUSTNAME
		       ,ins.CRLMTAMT
		       ,ins.PYMTRMID
			   ,ins.PRBTADCD
			   ,'Changed'
			   ,GETDATE()
			   ,ORIGINAL_LOGIN()
	    FROM INSERTED AS ins
	    --FROM DELETED AS del INNER JOIN INSERTED AS ins
			  -- ON del.CUSTNMBR = ins.CUSTNMBR --AND del.ADRSCODE = ins.ADRSCODE

END

--END
GO

ALTER TABLE [dbo].[RM00101] ENABLE TRIGGER [tr_RM00101_ChangeTracking]
GO

/*
exec sp_settriggerorder @triggername = 'tr_RM00101_ChangeTracking', 
@order = 'last', 
@stmttype = 'UPDATE', 
@namespace = null 
GO

SELECT name,
  IsFirst = OBJECTPROPERTY(object_id, 'ExecIsFirstInsertTrigger'),
  IsLast  = OBJECTPROPERTY(object_id ,'ExecIsLastInsertTrigger')
FROM sys.triggers
WHERE parent_id = OBJECT_ID(N'dbo.RM00101');

*/

--ALTER TABLE [dbo].[RM00101] DISABLE TRIGGER [tr_RM00101_ChangeTracking]

--DROP Create TRIGGER tr_RM00101_ChangeTracking

/*
USE [TRUCK]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RM00101_Audit_Tracking](
	[CUSTNMBR] [CHAR](15) NOT NULL,
	[CUSTNAME] [CHAR](65) NULL,
	[CRLMTAMT] NUMERIC (19,5) NULL,
	[PYMTRMID] [CHAR](21) NULL,
	[PRBTADCD] [CHAR](15) NULL,
    [ChangeNote][CHAR](15) NOT NULL,
	[ChangeDate] [DATETIME] NULL,
	[ChgUser] [NVARCHAR](128) NOT NULL,
	[DEX_ROW_TS] [DATETIME] NOT NULL,
	[DEX_ROW_ID] [INT] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKRM00101_Change] PRIMARY KEY NONCLUSTERED 
(
	[CUSTNMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [P[PRIMARY]
GO

*/

Expand

Copy Download Link

/********************************************************************************
Target Server  : GPPDBAWS01                       
Target Database: TRUCK
Author         : David Morinello
Date Created   : 08/16/2023
Version        : 0.5 Table Design Inwork
Purpose        : Customer Audit-Table and Trigger to Track Customer Master table changes 
Note           : Create RM00101_Audit_Tracking Table.sql
               : https://truckprovsts.visualstudio.com/TruckPro%20Portfolio%20Project/_workitems/edit/25742
********************************************************************************/
USE [TRUCK]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP TABLE IF EXISTS [dbo].[RM00101_Audit_Tracking] --SQL 2016 Feature

CREATE TABLE [dbo].[RM00101_Audit_Tracking](
	[CUSTNMBR] [CHAR](15) NOT NULL,
	[CUSTNAME] [CHAR](65) NULL,
	[CRLMTAMT] NUMERIC (19,5) NULL,
	[PYMTRMID] [CHAR](21) NULL,
	[PRBTADCD] [CHAR](15) NULL,
    [ChangeNote][CHAR](15) NOT NULL,
	[ChangeDate] [DATETIME] NULL,
	[ChgUser] [NVARCHAR](128) NOT NULL,
--	[DEX_ROW_TS] [DATETIME] NOT NULL,
	[DEX_ROW_ID] [INT] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKRM00101_Change] PRIMARY KEY NONCLUSTERED 
(
	[CUSTNMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

--ALTER TABLE [dbo].[RM00101_Audit_Tracking] ADD  CONSTRAINT [DF__RM00101_Audit_Tracking__DEX_ROW__1154E6D5]  DEFAULT (GETUTCDATE()) FOR [DEX_ROW_TS]
--GO

/*

Select * from RM00101_Audit_Tracking

sp_helptrigger RM00101

*/

Expand

David Morinello

Member

August 14, 2023 at 2:56 pm in reply to: Remove History

Up

0

Down

There are a number of solutions within the GP feature list. And since it’s part of GP, it’s free. Open GP and the Help screen and search for Remove or Removal.

If you hunt around there are also a number of SQL scripts that can help you trim some of your Master tables. Queries to identify Customers or Vendors with no activity in x years.

****

There is one 3rd party from Professional Advantage called Archiver that can remove or archive to a new database. I don’t necessary recommend it for a few reasons. Difficult to maintain and any custom code or even some standard GP is not supported. Plus the vendor is quite difficult to work with. B But it is your choice based on your specific needs.

Of course, test on a lower environment and document your steps. And remember to back up!

David Morinello

Member

August 3, 2023 at 4:56 pm in reply to: Fifth/Third Bank’s ePAY

Up

0

Down

Old thread I know, however RegalPay is going away at the end of 2023.

Has anyone done work on a replacement, either using GP (2018R2) or a no cost external solution?

David Morinello

Member

June 8, 2023 at 4:15 pm in reply to: 3rd Party Company to collect and handle sales tax

Up

0

Down

Check out Avatax from Avalara. It works well with Dynamics GP.

David Morinello

Member

June 6, 2023 at 4:36 pm in reply to: ESS Access at employee home

Up

0

Down

I assume this has never worked?

If so, and this is getting outside my comfort zone, it is likely due to the initial setup of ESS. Internal users can connect because they are inside your secure network. Allowing access outside the local network takes more setup work so your users can connect but bad players can’t get to your data.

Did you or your IT do the ESS install, or did your GP Partner? I recommend reaching out to them.

Not sure if this will help, but it may be useful for the concepts.

(+) DNS Overview for External Workflow 2.0 Email Notification Approval – Microsoft Dynamics GP Community