Shawn_Moore

CA Tuesday Tip: Dynamic Lookups within Timesheets

Discussion created by Shawn_Moore Employee on Mar 15, 2011
Latest reply on Jan 5, 2016 by navzjoshi00
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 3/15/2011

Today's Tip is about a lesser known feature in timesheets. The timesheet module allows you to define a dynamic lookup that can take a timesheet id as a parameter. What this enables you to do is create user value 1 lookups that can potentially be different for each user accessing the timesheet.

Our tip today provides a simplified example of this feature. The scenario is that we will use is that resources are mapped to a pool of machines. For each task on their timesheet, they will associate the time entry with a particular machine. Not all resources have access to the same machines, so we want the drop down to only reflect the machines the user is associated with.

I've creating a mapping for this relationship, which maps the machine to the resource id (this is not the most elegant solution, but it is simplified for the sake of this exercise)

i.e.
Machine             Resource
----------------    --------------
VM LAB              <null>
CTR2-DELL-35a       5000001
CTR2-DELL-35b       5000001
CTR2-DELL-35b       5000002
HAL 9000            5000001
HAL 9000            5000002
HAL 10000           5000002
HAL 10000           5000003
When a user accesses a resource's timesheet, the drop down for user value 1 will populate based on the set of machines mapped to that resources. All resources have access to the VM LAB.

To implement this, we can follow these steps:

1) Define the mapping table
a) Create a new object called Machine Mapping, and use an id of "machinemap"
b) Create an numeric attribute called "allowed_resource" to store the resource id
(this will create an odf_ca_machinemap table with a number of fields including name and allowed_resource)

2) Edit your views so that you can easily populate the mapping table.

3) Enter the mapping values above

4) Now create a lookup called Machine Mapping, using a dynamic query with the following NSQL. (use the default lookup mappings)

SELECT
@SELECT:TE.PRID:PRTIMEENTRYID@,
@SELECT:MM.NAME:USERVALUE@
FROM PRTIMESHEET TS, PRTIMEENTRY TE, ODF_CA_MACHINEMAP MM
WHERE TS.PRID = @WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@
AND TS.PRID = TE.PRTIMESHEETID
AND (MM.ALLOWED_RESOURCE = TS.PRRESOURCEID OR MM.ALLOWED_RESOURCE is null)
AND @FILTER@


-- The key piece of this NSQL that makes it special, is the "@WHERE:PARAM:USER_DEF:INTEGER:USERDEFTSID_PARAM@" clause. This clause allows the incoming lookup to take in the current timesheet's id and use that as a parameter into the query.

5) Now associate the lookup you created with the user lookup 1 field in the Admin Tool -> Project Management -> Timesheet Options. If you haven't added the user lookup 1, you can add it here, but if the user has already customized their timesheet view the admin change will not take effect unless you use the "Apply to All Resources" button. (using the "Apply to All Resources" will overwrite settings at the user level, so be careful)

6) Finally navigate to a timesheet, populate it and you should see the drop down available with your dynamic lookup.


Shawn Moore
CA Technologies

Outcomes