Recommendations on best way(s) of creating a log of when multiple status fields get updated

(7) Share

Report

Posted on by sstuart

I want to make sure I design this part the best way possible.

I have a Canvas App with Dataverse backend that has multiple sections each with it's own status field, and an overall status field.

I need a way to get the date/time stamp when any of these fields get updated, sometimes, move than 1 may be updated on a given save.

I believe what I want to do is create a custom table something like this:

CREATE TABLE event_status_log (
event_id INT AUTO_INCREMENT PRIMARY KEY,
timestamp DATETIME NOT NULL,
user_id INT,
action VARCHAR(255) NOT NULL,
column_name VARCHAR(255) NOT NULL,
record_id INT,
old_value TEXT, (probably can not capture this, so this would be removed),
updated_value TEXT,
);

I would also create multiple power automate(s) to run when these status fields are updated/changed, and populate the table above appropriately.

Is this the best way to do this? Is there a better way. Also is there any other fields I should have and capture in the table.

Thank you,

Steven Stuart

Categories:

Power Platform integration

All responses (4)

Answers (1)

sstuart 22 on at

Like (0)

Report

Recommendations on best way(s) of creating a log of when multiple status fields get updated

Thank you Daivat

Your post is exactly the type of response/answer I was looking for. I will look into doing it that way. We are not using an Organization ID in this, so I do not need that part.

Initially I was thinking of having multiple Power Automates, because I only wanted the flow to be run when one of those fields were changed, because the records will be updated a lot, and I was thinking of minimizing the possible runs. However, I do think that your way is still better.

Thank you,

Steven Stuart
Vahid Ghafarpour 9,720 Super User 2025 Season 1 on at

Like (0)

Report

Recommendations on best way(s) of creating a log of when multiple status fields get updated

If any of the responses helped resolve your issue, please take a moment to mark the best answer. This helps others in the community quickly find solutions to similar problems.

To do this, simply click the "Does this answer your question?" button on the most helpful response and like the helpful posts. If your issue is still unresolved, feel free to provide more details so the community can assist further!

Thanks for being an active part of the Dynamics 365 Community!
Verified answer

Daivat Vartak (v-9d... 6,642 Super User 2025 Season 1 on at

Like (2)

Report
Recommendations on best way(s) of creating a log of when multiple status fields get updated

Hello Steven,

You're on the right track with using a custom table to log status changes in your Canvas App with a Dataverse backend. However, let's refine your approach to better leverage Dataverse and Power Automate, and consider some potential improvements:

Refined Approach:

Dataverse Custom Table:

Create a custom table in Dataverse to store your status change logs.

Fields:

event_id (GUID, Primary Name): Use a GUID as the primary key for better scalability and uniqueness.

timestamp (DateTime, User Local): Store the date and time of the change in the user's local time.

user_id (Lookup to User): Use a lookup to the System User table to capture the user who made the change.

action (Option Set or Text): Use an option set (e.g., "Updated," "Created") for standardized actions or a text field for more flexibility.

column_name (Text): Store the logical name of the field that was updated.

record_id (Lookup to your main table): Use a lookup to the main table where the status fields reside.

updated_value (Text): Store the new value of the field.

record_logical_name (Text): store the logical name of the main table.

organization_id (Lookup to Organization Table): If you have multiple organizations, this is very important.

Power Automate Flows:

Create one Power Automate flow triggered by the update of your main table.

Use a "Compose" action to get the changed fields, and use a condition to check if the status fields were changed.

Use a "Apply to Each" action to loop through the changed fields.

Inside the loop, use a "Add a new row" action to create a record in your custom log table.

Canvas App Integration:

When you save changes in your Canvas App, trigger the Power Automate flow.

Pass the necessary data (record ID, changed fields, new values) to the flow.

Advantages of This Approach:

Centralized Logging: All status changes are logged in a single table, making it easier to query and analyze.

Dataverse Native: Leveraging Dataverse features provides better performance and scalability.

Single Flow: Using a single flow reduces the number of flows to manage and simplifies the logic.

Improved User Lookup: Using a lookup to the System User table ensures accurate user identification.

Easier Querying: the logical name of the table, and the organization id will greatly improve querying the log table.

Improvements and Considerations:

Removing old_value: You're right; capturing the old value can be complex and inefficient. If you need the old value, consider enabling auditing on the main table.

Performance: Be mindful of the number of logs generated, especially if you have frequent updates. Consider archiving or purging old logs if necessary.

Error Handling: Implement error handling in your Power Automate flow to gracefully handle failures.

Security: Ensure that only authorized users can access and modify the log table.

Auditing: Consider using Dataverse auditing in addition to your custom logging. Auditing captures changes to all fields, not just status fields.

Bulk Updates: If you need to handle bulk updates, you might need to adjust your Power Automate flow to process multiple records at once.

Asynchronous Processing: If you have many updates, consider using Dataverse queues to process the logs asynchronously.

Why One Flow Is Better:

Reduced Complexity: Managing one flow is easier than managing multiple flows.

Improved Performance: A single flow can process multiple updates more efficiently.

Simplified Logic: You can use conditional logic within the flow to handle different scenarios.

Example Power Automate Flow (Simplified):

Trigger: When a row is modified (Your main table).

Compose: Get the changed columns.

Condition: Check if any status columns were changed.

Apply to each: Loop through the changed columns.

Add a new row: Create a record in your log table.

timestamp: utcNow()

user_id: triggerOutputs()?['body/modifiedby']

action: 'Updated'

column_name: Current item from the apply to each.

record_id: triggerOutputs()?['body/yourMainTableId']

updated_value: get the updated value of the field.

record_logical_name : logical name of the main table.

organization_id : the organization id.

By implementing this refined approach, you'll have a robust and efficient logging system for your status changes. Remember to tailor the solution to your specific requirements and thoroughly test it before deploying it to production.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,

Daivat Vartak
Ramesh Kumar 3,501 on at

Like (1)

Report

Recommendations on best way(s) of creating a log of when multiple status fields get updated

I think you are right track. Event status log is the right path to start. The suggestions above focus on improving the table schema, enhancing the Power Automate flows, and ensuring proper auditing and performance management.

Capture the essential details like user, timestamp, and status field changes. Optimize performance with indexing and data retention strategies as the table grows. Use Power Automate to automate the process of logging changes in real-time.

Thanks

Ramesh

If this was helpful, please check the "Does this answer your question?" box and mark it as verified.