I am making this tutorial because I found it really difficult to go through older transaction that happened more than one day ago since all of the transaction document names are the same i.e. Petty Cash
This tutorial is made to add date to your default account transaction documents.
Format of this out will be YYYY-MM-DD - NAME
i.e. 2022-04-04 - Petty Cash
MAKE A BACKUP
TEST BEFORE IMPLEMENTING
REQUIRED (tested on):
SQL Server 2019
Microsoft SQL Server Management Studio 2018
SambaPOS v5.3.6
- Open Microsoft SQL Server Management Studio 2018
1.1. Authenticate
1.2. Select your database
1.3. New Query
1.4. Paste the code below
1.5. Execute
CODE TO PASTE
/* Replace generic Account Transaction Name with Year-Month - Name */
IF OBJECT_ID('dbo.trg_AccountTransactionDocuments_Insert') IS NOT NULL
DROP TRIGGER dbo.trg_AccountTransactionDocuments_Insert;
GO
CREATE TRIGGER dbo.trg_AccountTransactionDocuments_Insert
ON dbo.AccountTransactionDocuments
AFTER INSERT
AS
BEGIN
DECLARE @DocId INT;
DECLARE @DocName NVARCHAR(MAX);
DECLARE @YearMonth NVARCHAR(10) = CONVERT(VARCHAR(10), GETDATE(), 126);
SELECT @DocId = Inserted.Id,
@DocName = Inserted.Name
FROM Inserted;
UPDATE dbo.AccountTransactionDocuments
SET Name = CONCAT(@YearMonth, ' - ', @DocName)
WHERE Id = @DocId
END;
GO
Special thanks to Memo and Bob_be for sharing occasional wisdom with me