User-defined Lookup with Time Entry User Value 1 for Timesheet Entries
search cancel

User-defined Lookup with Time Entry User Value 1 for Timesheet Entries

book

Article ID: 22205

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

When configuring the Timesheet Options, the browse window for selecting a lookup on the User Value 1 field only shows a choice of one lookup definition and that is the definition for the stock 'Time Entry User Value 1' Static Lookup. A user-defined dynamic query lookup can be used on this field. This article covers how to use a User-defined Lookup with the Time Entry User Value 1 field for Timesheet Entries.  

Environment

Release: All

Component: Clarity Timesheets  

Resolution

FOR USE WITH TIMESHEET ENTRY ONLY

 

The use of a dynamic lookup on the Timesheet Entry is available with specific requirements and special considerations for storing and displaying data for this field.  It is recommended that the stock lookup called  'Time Entry User Value 1' be used.

 

Data Considerations:

The use of a user-defined lookup is only applicable to the configuration of the User Value 1 field on the Timesheet Entry.  

This configuration does not apply to other areas of the application.  It does not apply to Financial Transaction Entry or WIP Adjustment pages.

This configuration does not apply to any other database views or stock functions that utilize the default Time Entry User Value 1 lookup.
The USERVALUE can be a 30-character string value or an integer value from a dynamic query lookup.  
The USERVALUE data is stored on the PRTIMEENTRY.USER_LOV1 field.  
The DISPLAYVALUE can be a 30-character string value or an integer value from a dynamic query lookup.  
The value that is saved from the USERVALUE field into the PRTIMEENTRY.USER_LOV1 field is displayed directly on the page after the timesheet is submitted into the workflow.  After the timesheet is submitted into the workflow, the saved value is not converted into the lookup display attribute.  The lookup display attribute is only shown when the field is open for editing on the timesheet.

Reporting Query considerations:  

It is important to use a CODE or ID field to store your USERVALUE if you want to link it back to a lookup list in reporting or portlets. (See Example #3)  

Otherwise, if you use a 30-character string value ; it will be simple to query and display the native value from the saved field. 
 

Query Requirements:

The dynamic query must have the following requirements to function successfully.

  • The Select statement must create an attribute with the alias name of 'PRTIMEENTRYID' 
  • The PRTIMEENTRYID select column attribute must return the PRTIMEENTRY.PRID value as any column defined in the SQL statement 
    • This provides the current time entry row unique ID which is mapped to the PRTIMEENTRY table for saving the data
  • The Select statement must create an attribute with the alias name of 'USERVALUE'
  • The value returned for the 'USERVALUE' is saved and stored on the PRTIMEENTRY.USER_LOV1 field 
  • The USERVALUE select column attribute can return an integer data type if desired
  • The USERVALUE select column attribute can return a string data type if desired
    • If the USERVALUE returns a string - the returned value must be no more than 30 characters in length
  • The DISPLAYVALUE can be a 30-character string value or an integer value from a dynamic query lookup.
    • The DISPLAYVALUE needs to be included in the query for the lookup values to display on submitted and posted timesheets in the New UX.  
  • The WHERE clause must have a specific parameter alias name of 'USERDEFTSID_PARAM' to map the current Timesheet internal ID value for saving the data

  

  • Using Custom Lookup User Value1 lookup along with Data Warehouse:
    • The Select statement must create an attribute with the alias name of 'LANGUAGE_CODE'. The value of this attribute must be from LANGUAGE_CODE column of CMN_LANGUAGES.
    • The Select statement must create an attribute with the alias name of 'LANGUAGE_ID'. The value of this attribute must be from ID column of CMN_LANGUAGES.
    • The query must return unique USERVALUE for the combination of 'PRTIMEENTRYID' & 'LANGUAGE_CODE' attribute.

By default, the “User Value1” attribute is pointing to Static lookup - PRTIMEENTRY_USER_LOV1 so the lookup values are localized. Therefore, in Data Warehouse schema, USER_VALUE1_KEY is a column of DWH_TME_ENTRY while USER_VALUE1 is a column of DWH_TME_ENTRY_LN table.

When selecting a custom lookup as “User Value1 Lookup” there is a possibility that the values returned by custom lookup is not localized.

Make sure to return the values for every language PPM supports, otherwise it would break the OOTB schema, Jaspersoft Domains and any “Reports & Ad-hoc View” using USER_VALUE1 column of DWH_TME_ENTRY_LN table.

 

This can be done by modifying the lookup query to return the same value for every language when the value is not localized (examples below).

 

Example #1: User Value Dynamic Lookup required structure, content and format:  

Below is an example of a query that can be used to test this functionality. The query is simple and will return the Internal Task Id (hidden key) value for each time entry row on a timesheet and display the first 30 characters of the Task Name. The Claritiy administrator can use the structure of the query to create a definition for other data.

It is important to keep the structure of the query so that it adheres to the requirements as outlined above.  The lookup values can also be retrieved from other pre-defined lookups using this method.
The possibilities for creating user-defined data values on this field is expanded with this concept. 

1. Go to Administration  > Data Administration > Lookups > New
2. Create a new user-defined, dynamic lookup definition

 


   

3. Enter the Query with the following code using the requirement guidelines noted above:

The example below uses Oracle SUBSTR function to resize the Task Name in the event the Task Name is greater than 30 characters.  If you do not reduce the resulting value, a System Error will be generated on the Timesheet Entry page when attempting to save as the USERVALUE is saved to the PRTIMEENTRY.USER_LOV1 field. 

SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):USERVALUE@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):DISPLAYVALUE@
FROM PRTIMESHEET TS, PRTIMEENTRY TE, PRASSIGNMENT ASSG, PRTASK TSK
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND TE.PRASSIGNMENTID = ASSG.PRID
AND TSK.PRID = ASSG.PRTASKID
AND @FILTER@ 

When using Data Warehouse, the above NSQL should be modified as:

SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):USERVALUE@,
@SELECT:SUBSTR(TSK.PRNAME,1,30):DISPLAYVALUE@,
@SELECT:LANG.LANGUAGE_CODE:LANGUAGE_CODE@,
@SELECT:LANG.ID:LANGUAGE_ID@
FROM PRTIMESHEET TS, PRTIMEENTRY TE, PRASSIGNMENT ASSG, PRTASK TSK, CMN_LANGUAGES LANG
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND TE.PRASSIGNMENTID = ASSG.PRID
AND TSK.PRID = ASSG.PRTASKID
AND LANG.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@
AND @FILTER@

  • ​Click 'Save and Continue' button
  • Go to Administration > Project Management > Timesheet Options
  • Set the field 'User Value 1' to be a selected column on the timesheet.


  • Set the field 'User Value 1 Lookup' to be the custom lookup created above.

  • Click 'Save' button.
  • Click 'Apply to All Resources' so that all users will have the same configuration.
  • Open a timesheet to test that it worked.

This is just one example of how the application can be configured to use a different lookup for the Timesheet User Value 1 field. If the Administrator follows this example, any data can be used. The 'USERVALUE' field in the select statement does not have to be a string data type; it can be an integer value of a record ID or it can be a Lookup Code value.  The value that is saved from the USERVALUE field into the PRTIMEENTRY.USER_LOV1 field is displayed directly on the page after the timesheet is submitted into the workflow.  After the timesheet is submitted into the workflow, the saved value is not converted into the lookup display attribute.

NOTE: The ability to configure the LOV2 is not possible. The LOV2 field on the Timesheet entries will always reference the 'Time Entry User Lookup Field 2' (ID=PRTIMEENTRY_USER_LOV2). The Administration, Timesheet Options page only shows the ability to configure the LOV1 field to select a valid lookup.

 

Example #2: User Value Dynamic Lookup referencing a stock, static list using Lookup Name in USERVALUE

Now that you see how this functionality works, here is an example that pulls the list of active values from a predefined static list lookup already in the system.

This example uses the values from the 'Other Category' Lookup (ID = INV_OTHER_CATEGORY_TYPE). The following article is helpful in understanding how to query for the static list of lookup values with selections.  Reference TEC438946 : How to Query Static List Lookup Values

1.      Create a new dynamic lookup (Custom User Value 1 - Category Types) with the following query. This query will display the LOOKUP_CODE. Note, the query select fields and WHERE clause parameter are the same as in Example #1.  This must remain the same for it to work.

 SELECT

@SELECT:TE.PRID:PRTIMEENTRYID@,

@SELECT:SUBSTR(L1.LOOKUP_NAME,1,30):USERVALUE@,
@SELECT:SUBSTR(L1.LOOKUP_NAME,1,30):DISPLAYVALUE@

FROM PRTIMESHEET TS,PRTIMEENTRY TE,

(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME

FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N

WHERE L.ID = N.PK_ID

AND N.LANGUAGE_CODE = 'en'

AND N.TABLE_NAME = 'CMN_LOOKUPS'

AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L1

WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@

AND TS.PRID = TE.PRTIMESHEETID

AND @FILTER@

When using Data Warehouse, the above NSQL should be modified as:

SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(L1.LOOKUP_NAME,1,30):USERVALUE@,
@SELECT:SUBSTR(L1.LOOKUP_NAME,1,30):DISPLAYVALUE@,
@SELECT:L1.LANGUAGE_CODE:LANGUAGE_CODE@,
@SELECT:L1.LANGUAGE_ID:LANGUAGE_ID@
FROM PRTIMESHEET TS,PRTIMEENTRY TE,
(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME, LANG.ID LANGUAGE_ID, LANG.LANGUAGE_CODE LANGUAGE_CODE 
FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N, CMN_LANGUAGES LANG
WHERE L.ID = N.PK_ID
AND N.LANGUAGE_CODE = LANG.LANGUAGE_CODE
AND LANG.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@
AND N.TABLE_NAME = 'CMN_LOOKUPS'
AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L1
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND @FILTER@
 

2.      Change the Administration, Timesheet Options, User Value Lookup 1 to use this new query, click 'Save' button  

3.      Then click 'Apply to all Resources' button  

4.      Test the new configuration    

The value that is displayed from the Lookup Query in the 'USERVALUE' field stored on the PRTIMEENTRY.USER_LOV1 field in the database.



Example #3: User Value Dynamic Lookup referencing a stock, static list using LOOKUP CODE in USERVALUE

1. Create a lookup that uses the LOOKUP_CODE value in the USERVALUE attribute

 SELECT

@SELECT:TE.PRID:PRTIMEENTRYID@,

@SELECT:SUBSTR(L1.LOOKUP_NAME,1,30):USERVALUE@,
@SELECT:SUBSTR(L1.LOOKUP_NAME,1,30):DISPLAYVALUE@

FROM PRTIMESHEET TS,PRTIMEENTRY TE,

(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME

FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N

WHERE L.ID = N.PK_ID

AND N.LANGUAGE_CODE = 'en'

AND N.TABLE_NAME = 'CMN_LOOKUPS'

AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L1

WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@

AND TS.PRID = TE.PRTIMESHEETID

AND @FILTER@

When using Data Warehouse, the above NSQL should be modified as:

SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(L1.LOOKUP_NAME,1,30):USERVALUE@,
@SELECT:SUBSTR(L1.LOOKUP_NAME,1,30):DISPLAYVALUE@,
@SELECT:L.LANGUAGE_CODE:LANGUAGE_CODE@,
@SELECT:L.LANGUAGE_ID:LANGUAGE_ID@
FROM PRTIMESHEET TS,PRTIMEENTRY TE,
(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME, LANG.ID LANGUAGE_ID, LANG.LANGUAGE_CODE LANGUAGE_CODE 
FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N, CMN_LANGUAGES LANG
WHERE L.ID = N.PK_ID
AND N.LANGUAGE_CODE = LANG.LANGUAGE_CODE
AND LANG.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@
AND N.TABLE_NAME = 'CMN_LOOKUPS'
AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L1
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND @FILTER@

 

2. Select and save a value on the Timesheet (note the values shown are from USERVALUE for the LOOKUP_CODE)

Example #4: User Value Dynamic Lookup referencing a stock, static list when using Postgres

Oracle and MSSQL allow for duplicates in the table in DWH. Postgres was purposely made to not allow duplicates in our SaaS environment because of the potential load of data across the whole stack causing performance issues. 

Update the query to add the yellow highlighted parts below.  It will need row_number as a unique_Id and TS.PRID = COALESCE(@WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@, TS.PRID; which will be used to map the appropriate time entry.

The hidden key should be the row_number field so it will be unique

SELECT ROW_NUMBER() OVER(order by null) as row_number,
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:SUBSTR(L1.LOOKUP_NAME,1,30):USERVALUE@,
@SELECT:SUBSTR(L1.LOOKUP_NAME,1,30):DISPLAYVALUE@,
@SELECT:'en':LANGUAGE_CODE@,
@SELECT:1:LANGUAGE_ID@
FROM PRTIMESHEET TS, PRTIMEENTRY TE, ODF_CA_CUST WI 
WHERE 1=1 and TS.PRID = COALESCE(@WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@, TS.PRID) 
AND TS.PRID = TE.PRTIMESHEETID AND @FILTER@

 

Reporting Query Example:

If you use Example #3 where you store the LOOKUP_CODE from query listing existing lookup values, you can get output of labels associated with the codes.

If you pick 'SECURITY_ADMINISTRATION' as the lookup_code value in this example, the table will store this value.  It is important to use a CODE or ID field to store your user value if you want to link it back to a lookup list in reporting or portlets.  The following query shows you how to link back to the Lookup Name and Description and because the lookups allow for language labels, the query can be modified to check the user account settings or hard-coded for a specific language.  This is a simple join query and will only show entries that have matching rows to the lookup codes. 

SELECT TE.PRTIMESHEETID, TE.PRID TIMEENTRYID, TE.USER_LOV1,

L1.LOOKUP_NAME, L1.LOOKUP_DESCRIPTION, L1.IS_LOOKUP_ACTIVE

FROM PRTIMESHEET TS, PRTIMEENTRY TE,

(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME, 

 N.DESCRIPTION LOOKUP_DESCRIPTION, L.IS_ACTIVE IS_LOOKUP_ACTIVE

     FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N

     WHERE L.ID = N.PK_ID

     AND N.LANGUAGE_CODE = 'en'

     AND N.TABLE_NAME = 'CMN_LOOKUPS'

     AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L1

WHERE 1=1

AND TS.PRID = TE.PRTIMESHEETID

AND TE.USER_LOV1 = L1.LOOKUP_CODE  



When using Data Warehouse, the above query should be modified as:

SELECT TE.PRTIMESHEETID, TE.PRID TIMEENTRYID, TE.USER_LOV1,
L1.LOOKUP_NAME, L1.LOOKUP_DESCRIPTION, L1.IS_LOOKUP_ACTIVE, L1.LANGUAGE_ID LANGUAGE_ID, L1.LANGUAGE_CODE LANGUAGE_CODE

FROM PRTIMESHEET TS, PRTIMEENTRY TE,

(SELECT L.LOOKUP_CODE, N.NAME LOOKUP_NAME, LANG.ID LANGUAGE_ID, LANG.LANGUAGE_CODE LANGUAGE_CODE,  
 N.DESCRIPTION LOOKUP_DESCRIPTION, L.IS_ACTIVE IS_LOOKUP_ACTIVE
     FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N, CMN_LANGUAGES LANG
     WHERE L.ID = N.PK_ID
     AND N.LANGUAGE_CODE = LANG.LANGUAGE_CODE
     AND N.TABLE_NAME = 'CMN_LOOKUPS'
     AND L.LOOKUP_TYPE = 'INV_OTHER_CATEGORY_TYPE') L1

WHERE 1=1
AND TS.PRID = TE.PRTIMESHEETID
AND TE.USER_LOV1 = L1.LOOKUP_CODE




Example 4.

This method can be used to force a timesheet change, e.g. if financial transaction from the Invalid Transaction page were deleted.


The transactions and their prior attempts at adjustment now appear:

Additional Information

For using the stock, OOTB Static List Lookups for Time Entry User Value 1 and Time Entry User Value 2 fields refer to the following KB article:

  • KB 25368: How To Use 'User Lov 1' and 'User Lov 2' for Timesheet Entries   
Powered by Wolken Software