Creating an audit trail for form submissions ensures that all form data is tracked and recorded for review, compliance, and security purposes. By maintaining an audit trail, you can log who submitted the form, when it was submitted, what data was provided, and whether any updates or changes were made. This is especially important in environments where user data is sensitive and subject to regulations like GDPR or other data protection laws.
In Power Pages (formerly Power Apps Portals) and Dataverse, you can use the following approach to create an audit trail for form submissions.
1. Define the Data Structure for the Audit Trail
Before you start tracking form submissions, you need to design an Audit Trail data structure in Dataverse.
Fields in the Audit Trail Entity:
- AuditTrailID: Unique identifier for each record (Auto-generated).
- FormName: The name or identifier of the form being submitted (e.g., “Contact Form”, “Survey”).
- SubmissionDate: The date and time when the form was submitted.
- UserID: The user who submitted the form (linked to the Contact or User entity).
- FormData: The raw data submitted by the user in JSON format or as individual fields (e.g., Name, Email, Feedback).
- Action: Type of action taken (e.g., “Submit”, “Update”).
- IP Address: The IP address from which the form was submitted (optional but can be useful for security purposes).
- Browser/Device Information: Browser and device used for the form submission (optional but useful for auditing).
- Status: Submission status (e.g., “Success”, “Failed”, “Pending”).
- Notes: Additional information (optional, for any special circumstances or admin remarks).
- Changes: If any data was modified after submission, store what was changed and by whom.
Example of the AuditTrail table structure:
| Field | Data Type | Description |
|---|---|---|
| AuditTrailID | Auto-number | Unique ID for audit record |
| FormName | Text | The name of the form being submitted |
| SubmissionDate | DateTime | Date and time of form submission |
| UserID | Lookup (Contact) | The user who submitted the form |
| FormData | Text (JSON) | Raw form data in JSON format |
| Action | Option Set | Action taken (“Submit”, “Update”) |
| IP Address | Text | IP address from where the form was submitted |
| Browser/Device Info | Text | Browser/device info (optional) |
| Status | Option Set | Status of the submission (e.g., Success, Failed) |
| Notes | Text (Optional) | Notes or comments related to submission |
| Changes | Text (Optional) | Changes made to the form data after submission |
2. Create an Audit Trail Form in Dataverse
Once the AuditTrail entity is created in Dataverse, you can use it to track form submissions in your Power Pages portal.
Steps:
- Create a new table in Dataverse: This table will store audit trail records, capturing every submission.
- Define the columns for each field as mentioned above (FormName, SubmissionDate, etc.).
- Set up relationships between the AuditTrail table and the relevant tables (e.g., Contacts for user information, Forms for form identification).
3. Capture Form Submissions in Power Automate
To automatically create audit trail records upon form submission, you can use Power Automate.
Steps:
- Trigger: The flow should be triggered when a form is submitted in Power Pages. You can use a trigger like “When a record is created” or “When a row is added”. Example: When a new record is added to a Contact or Feedback table based on form submission.
- Retrieve User Information: Get the user’s details, such as UserID from the Contact table. This will help you log who submitted the form.
- Log Form Data:
- Parse the form data and store it in the AuditTrail entity. You can store the form data as JSON for future reference or store it in individual fields based on the form structure.
- Store details like FormName, SubmissionDate, UserID, and IP Address (which can be captured through custom code or Power Automate’s HTTP request if available).
- Set Submission Status: Store the Status (Success or Failed) to track whether the submission was successful. You can also include a Notes field for additional comments (e.g., issues encountered during submission).
- Update/Modify Records: If the form data is updated later (e.g., user modifies their submission), create an audit trail record to log these changes.
Example Power Automate Flow:
- Trigger: When a new form submission (record) is created.
- Retrieve User Information: Get UserID from Contact.
- Create Audit Trail Record:
- Log the FormName, UserID, and FormData in the AuditTrail entity.
- Track submission status (Success/Failed).
- Optionally, store IP Address and other information.
- Store Changes: If the form data changes after submission, log the changes in the AuditTrail entity with the action type “Update”.
4. Creating Reports and Dashboards for Audit Trail
You can build custom Power BI reports or Power Pages dashboards to monitor the audit trail. These reports help administrators and compliance officers keep track of the form submissions.
Example Audit Report:
- User: Who submitted the form.
- Form Name: The form being submitted (e.g., “Contact Us”, “Survey”).
- Submission Date: When the form was submitted.
- Status: Whether the submission was successful or failed.
- Changes: Track changes made to the form after submission.
This way, you can have full visibility into all form submissions and any changes made afterward.
5. Enhancing Security and Compliance
Ensure that the audit trail is secured and only authorized users can access or modify the records. You can leverage Dataverse security roles to control access.
- Restrict access to the audit trail records to users with administrative privileges.
- Enable logging to track who accessed or modified the audit trail.
- Ensure GDPR compliance: Ensure that the audit trail only contains the minimum necessary information and is securely stored.
6. Best Practices for Maintaining an Audit Trail
- Store only necessary information: Avoid storing excessive details, especially sensitive data, unless it’s necessary for compliance.
- Regularly back up audit records: Ensure that the audit trail records are regularly backed up to avoid data loss.
- Define retention policies: Set up rules for how long audit trail records should be kept and when they should be deleted or anonymized.
- Ensure data integrity: Make sure that no one can tamper with or delete audit trail records. Use security roles and permissions to ensure that only authorized personnel have access.