mrc logo mrc logo
  • m-Power m-Power
    What is m-Power?
    Overview Demos Build Process Case Studies Specs Pricing Trial
    m-Power Resources
    Overview How-To Videos Webinars & Podcasts White Papers Fact Sheets
  • Solutions Solutions
    What does m-Power build?
    Overview Database Front-Ends Reporting CRM Systems Business Intelligence Dashboards Inventory Management Mobile Apps ERP Enhancements Modernization Spreadsheets to the web MS Access to the web B2B/Web Portals Scheduling Embedded Analytics Web Forms Workflow Data Exploration Budgeting & Forecasting APIs and Web Services Db2 Web Query Alternative
    Solutions by Industry
    Overview Manufacturing Government Foodservice Software Vendors Logistics & Supply Chain Software Consultants Healthcare
  • Services Services
    Development Services Training Mentoring
  • About About
    Overview Partners Press Releases Careers Events Contact Blog
  • Support Support
    Support Home FAQ Documentation Customer Portal Enhancements Updates Roadmap Techblog
Try m-Power

m-Power Manual

Browse:

  • Home
  • Maintainers
  • Historical Auditing of Maintainer Actions
Back to Manual

Advanced Historical Auditing

A common request for anyone utilizing m-Power maintainers is the ability to track user actions. Previously, m-Power allowed you to audit which user last touched a record. However, getting a historical logging of actions required extra coding. A new auditing approach records all actions to a separate table which serves as a historical change log for the master maintainer. From a high-level explanation, every maintainer action that is submitted calls another m-Power maintainer in the background to insert a new change record. Depending on your level of logging needed, you can log the values of the record after each add, update, or delete. You can also log what the values were before AND after each action. This document will cover the different steps involved to create a full-fledged historical log of your maintainers.

Creating the Change Log Table

Using this advanced auditing features requires a specific logging table to be used. You can create a table directly on your database or use m-Power's built-in method of creating tables. Typically this table will contain all of the fields of the table being audited. Additionally, these optional fields:

Audit ID — Can be used as a unique ID field for each record in your table.
Audit User — Records the user making the change.
Audit Date — Record the date the change is made.
Audit Time — Records the database time the change is made.
Audit Action — Records the action submitted (add, upd, del, afteradd, before upd, after up, before del, after del)
Audit Program — Records the maintainer number used to make the change.
Audit Change ID — If logging before and after actions, you can map this field to a unique ID parameter that links the before and after records. This field needs to be a character field with at least a length of 23.
Audit Change Note — A reason/explanation input can be added to your master maintainer that will record the note to the audit table.

In my example, I will be auditing the Products Master table and will create my logging table with the same fields from my Products table in addition to all of the aforementioned optional fields:

AUDITID PNUM … Other Product Fields … AUDITUSER AUDITDATE AUDITTIME ACTION PROGRAM CHANGEID CHANGENOTE

Creating the Audit Maintainer

Once the logging table has been created, register the table to m-Power and create a maintainer over said table. Sequence by a uniquely identifying field(s). In my case I will be using my AUDITID field and making it an auto-sequence key.

Log Table Autosequence

From the Field Settings screen, we need to map the auditing logic to the appropriate audit fields.

Log Table Field Settings

Notice, I did not map the CHANGEID or the CHANGENOTE. Those will be mapped in the next step with the master maintainer.

Applying the Audit Maintainer to the Master Maintainer

Go to the master maintainer app, in my case the products master maintainer. We will be applying the auditing using an external object. Create a new External Object. At the bottom of the Location dropdown, you will find two audit options:

*AUDITAFTR — Logs a single record to the audit table AFTER an action has been submitted.
*AUDITBOTH — Logs two records. One of the record BEFORE the action and another AFTER the action. To link the two records together, you will want to map a unique ID parameter to a char(100) field in your audit table (explained further below).

External Object

Once an audit location has been selected, the object dropdown will become a list of all maintainers in the current dictionary. Find and select the audit maintainer set up previously. The master maintainer fields should automatically map to the matching fields of the audit maintainer. However, you may want to map your audit fields to special parameters.

Map a Constant — (Optional) Enter the name attribute of an input from the master maintainer. Typically used for adding comments as to the reason a record is being changed.
Map a Unique ID — (Optional). When using the *AUDITBOTH location to record the before and after action records, the two records can be linked together with a unique ID. The ID is a 36 character string that will be identical for the matching before and after records.

Reading the Audit Logs

Depending on which audit location you select, there will be a single log entry of how the record exists AFTER the maintainer is submitted (*AUDITAFTER) or two log entries, one for how the record existed BEFORE the submission and how the record exists AFTER the submission (*AUDITBOTH).

How you decide to interpret and use the logging table is your choice. One method to read/analyze the data is by creating m-Power applications over the audit table.

*AUDITAFTR

Audit After Log

Above you can see a record for every change made from the maintainer. The date, time, user, program, and action were all recorded automatically based off the audit logic added in the Field Settings screen of the audit maintainer. Additionally, there is a comment field that was written from the master maintainer as well.

*AUDITBOTH

Audit Both Log

With the audit both location set, two records are made for the maintainer actions. Mapping a Unique ID from the external object to the Change ID field links the before and after records together. Notice the Action field indicates the before and after actions, except for adding a new record where there is only logging of the record after added.

Created: May 18, 2018 | Modified: April 10, 2020

Search


Browse By Category

Build Process (13)
Starting with m-Power (8)
Retrievals (10)
Reports (15)
Summaries (4)
Maintainers (17)
Graphs (8)
m-Power Data Explorer (4)
General (24)
Calculations (5)
Utilities (9)
m-Power Administration (23)
Security (11)
Freemarker (6)
m-Painter (29)
Form Validation (5)
External Objects & UDFs (12)
Deprecated Documentation (23)
Bootstrap Templates (7)

Popular Tags

Graphs External Objects mrc-Productivity Series Getting Started Security Admin Bar Graphs Application Properties RPG Performance Graphing Advanced Tomcat Retrievals Prompt Screens Email Data Dictionary Retrieval Summaries Parameters Dropdowns Dates Database Video Excel Freemarker Form Validation App Properties SQL Reports Java m-Painter DB2 Calculations Graph Properties Maintainer Administration Report Popular Maintainers Record Selections Production Compiling Bootstrap Templates Build Process

See all tags »

michaels, ross & cole, ltd. (mrc)

Privacy Policy Cookie Policy Cookie Settings Notice at Collection Do Not Sell or Share My Personal Information

mrc (US)

2001 Midwest Road
Suite 310
Oak Brook, IL 60523
630-916-0662

mrc (UK)

Mortlake Business Centre
20 Mortlake High Street
London, SW14 8JN
+44-20-335-59566


© 2024 mrc. All rights reserved.