Skip to main content

Hi.

We have a custom field called Remaining Hours in Activity Resource Planning that calculates Planned Hours - Reported Hours into a new field. The query works fine for the most part, but we’ve noticed an issue with the field though, where if there are multiple reports under the same Resource Group, but with different Report Codes (e.g WORK & TRAVEL), one of the rows will not calculate properly.

Resource Group 11117 with 2 different report codes, not calculating remaining hours properly for the 2nd row.

The query itself is as follows:

SELECT (CASE WHEN Qty_Planned IS NOT NULL AND Qty_Used IS NOT NULL THEN (QTY_PLANNED - QTY_USED) ELSE Qty_Planned END)
From ACTIVITY_RESOURCE_PROJ
WHERE RESOURCE_ID=:RESOURCE_ID AND ACTIVITY_SEQ=:ACTIVITY_SEQ

 

Any help with a solution to this issue would be greatly appreciated.

Thank you!

Hi,

It looks like your query isn't finding a unique record and so it finds the first one and uses that for both. When setting up different Resource Criteria on a project the uniqueness of the Activity Resource records is no longer just ActivitySeq and Resource as it now is dependent on the other Resource Criteria, be it Report Code, Employee Category etc.

From memory the unique identifier on ActivityResource is a hidden attribute called ResourceSeqNo - this should be available in the ACTIVITY_RESOURCE_PROJ view and so if you amended your query to include this then that should fix the issue as the query would then be finding a unique record. 

Alternatively, as this query is based on the same view as the list in the client, then maybe instead of the custom field being based on a select statement it could be just an expression instead, e.g. just the CASE section - but that depends on what other views this custom field is required in.
 

Hi Hugh.
We switched the query into an Expression as you suggested since the field is only used in that window and this indeed fixed the issue.

Thank you.

Terms & ConditionsCookie settingsAccessibility statement