Using Audit History to View Status Progression of Project Tasks

Hi ​@Caleb Harriott 

Please create a GI similar to Project Tasks GI available in Acumatica.

Add conditions as shown in the screenshot below (and you can add other conditions and edits as you like)

Best Regards,

NNT

Thanks NNT!
The only problem with that is it doesn’t tell me when the Status changed?
For example I’m wanting to look to see a timeline that breaks down
When it was first Active, when it became completed and potentially if it was made active again (for example it was made completed in error)

Any thoughts on how to achieve this?

Hi ​@Caleb Harriott 

The LastModifiedDateTime field will record the time when the status changes.

However, the lastModifiedDateTime field only displays the time of the last change. Therefore, you must need Customization to record the time of Status (Active, Completed, Canceled) when the status changes.

I suggest another way to record the time of the Status without Customization. You create Business Events > When the status changes > import Activity into the Project Tasks screen > Activity creation time is the time to change the status of Project Tasks.

Then you can create GI of Project Tasks with Activities that record the time of Status (Active, Completed, Canceled) of Project Task.

Best Regards,

NNT

Hi Caleb,

The data structure of the AuditHistory table doesn’t allow for easy querying/joining from a generic inquiry:

• The CombinedKey field is a string, containing all the key fields separated by a null ‘0’ character
• The list of changes is all encoded into a single field

Unfortunately, I couldn’t find a way to use the CHAR() function or any way to setup a join condition using the null character in Acumatica generic inquiries.

I see two ways to accomplish what you’re looking for:

1. Create a SQL View, and DAC on top of it. The SQL view also needs to take proper care and replace the NULL character to another character before-hand. This SQL query works for me:

SELECT AuditHistory.batchID, PMTask.ProjectID, PMTask.TaskCD,
SUBSTRING(
	REPLACE(ModifiedFields, NCHAR(0) COLLATE Latin1_General_100_BIN2, N'~'),
	CHARINDEX('Status~', REPLACE(ModifiedFields, NCHAR(0) COLLATE Latin1_General_100_BIN2, N'~'))+LEN('Status~'),1) As ModifiedStatus,
AuditHistory.ChangeDate,AuditHistory.Operation,AuditHistory.UserID 
FROM 
PMTask INNER JOIN AuditHistory ON PMTask.CompanyID=AuditHistory.CompanyID AND AuditHistory.TableName='PMTask' AND CAST(PMTask.ProjectID as varchar)+CHAR(0)+PMTask.TaskCD=AuditHistory.CombinedKey
WHERE REPLACE(ModifiedFields, NCHAR(0) COLLATE Latin1_General_100_BIN2, N'~') LIKE '%Status~%'

IMPORTANT: I believe that you’re a MYOB Acumatica user, and they run on MySql -- the query above would need to be adapted to your database by a developer. ChatGPT seems to be able to convert it just fine, but i’m not sure if this accurately deals with the way MySql would have the null character.

Here’s sample output of this query from my test database:

Schema fields could be set in the generic inquiry to convert the status codes to their proper string litteral like ‘Active’, ‘Completed’

2. If you have someone in house that can customize Acumatica, it may be better to store this in a “clean” table that has the list of changes in a format that can be easily reported on. We’ve done this for a CRM Project before where the customer needed to track opportunity stage history.