I need to capture the cashier that created an inventory. i can manage to capture when it is committed only, what about when it is still open?
am thinking-can a trigger to auto fill the requisitioner with cashier work? guide me
thankyou
*This post is locked for comments
thankyou so much Antonijo Todorovik Dimiskov for the dedication and response it helped me greatly. thank you very much
Hi Lawrence,
Here as example trigger that can be used to automatically update the Phisical Inventory description field, when a new Inventory is created. Just open the SQL Management Studio and create this trigger over the PhisicalInventory table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_NewPhysInvUpdate]
ON [dbo].[PhysicalInventory]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE [PhysicalInventory]
SET [Description] = LEFT((SELECT Cashier.[Name] FROM Cashier
WHERE Cashier.[ID] IN (SELECT TOP 1 CashierID FROM [TimeCard] ORDER BY [ID] DESC)) + ': ' + [Description], 100)
WHERE [PhysicalInventory].[ID] = (SELECT TOP 1 [ID] FROM [PhysicalInventory] ORDER BY [ID] DESC)
END
GO
Of course, this trigger is just a start to create a perfect solution for your needs. Probably you will have to create additional trigger for when the Inventory is updated ('coz the description field can be edited), play with the Description field when second time updated to avoid duplicating the Cashier name, etc., etc.,..., but it's a good start I believe...
Kind regards, A.
Thank you sir, this my email address lawrenceguru@hotmail.com
Hi Bryt,
Well, I'm really busy at the moment, but if this is not very urgent for You... Please contact me over mail and we can discuss it, but I can't promise anything...
Best regards, A.
Thank you Antonijo for the info, very helpful. Are you in a position to assist me with a simple trigger that can do that please. Thank you in advance for your help, greatly appreciated.
Hi Bryt,
As You saed, the Cashier (CashierID) is "saved" into InventoryTransferLog once the quantities are Commited, but while the Inventory is open or parcial (editable), the values (Header and Entries) are saved into PhisicalInventory and PhisicalInventoryEntry tables, but without Cashier.
I'm not 100% sure (I would need to go deeper into the subject), but I supose by creating a trigger for the PhisicalInventory or PhisicalInventoryEntry table, when a new record is inserted, to check who is the actual user who have last log-in and save that info into personalized non-RMS table...or something similar...
Just as an idea, what about using SQL Profiler? You can easily trace everything thats going on in your database, from opening RMS SO Manager, until commiting the inventory. It's not easy job, but if You filter the profiler output for only the events that are of your interest, maybe you can figure it out who did what and when... Export the Profiler data into Excel if you are more comfortable with it...
Hope this is help...and talk to your RMS Partner, always better if experts are doing this...
Kind regards, A.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (