--- title: "Clarity PPM | Learn with Rego: Display Lookup Values in MSP + More" description: Learn how to display lookup values in MSP and more. We'll answer your questions about managing calendars, hours, and projects in Clarity PPM (CA PPM). --- [![Rego-Logo_Notag-Color-350](https://blog.regoconsulting.com/hs-fs/hubfs/Rego%20Guide%20Images/Rego-Logo_Notag-Color-350.png?width=224&name=Rego-Logo_Notag-Color-350.png)](http://regoconsulting.com) - [Home](http://regoconsulting.com) # Clarity PPM | Learn with Rego: Display Lookup Values in MSP + More [Today's Q/A explores five topics.](https://communities.ca.com/docs/DOC-231168837) 1. Can a Query link the CMN\_CUSTOM\_SCRIPTS table to the**process/step** name? 2. How can I use **SQL** to find the **calendar** where a resource's non-working day is defined? 3. Why is there a difference in Actual Hours between **Project and Team?** 4. How can we apply **negative** amounts to Task Actuals? 5. Can you display **Lookup Values in MSP** as a pull down? Please *feel free to comment on any alternative answers* you've found. ## **------------------------------** ## **1** **Can a Query link the CMN\_CUSTOM\_SCRIPTS table to the process/step name?** *Answer* SELECT PN.NAME PROCESS\_NAME , P.PROCESS\_CODE , PV.INTERNAL\_STATUS\_CODE , PSPN.NAME PROCESS\_STEP , SAN.NAME STEP\_NAME , SA.SCRIPT\_ID , TO\_CHAR(SUBSTR(S.SCRIPT\_TEXT, 0, 4000)) XX FROM BPM\_DEF\_PROCESSES P JOIN BPM\_DEF\_PROCESS\_VERSIONS PV ON P.ID = PV.PROCESS\_ID JOIN BPM\_DEF\_STAGES PS ON PV.ID = PS.PROCESS\_VERSION\_ID JOIN BPM\_DEF\_STEPS PSP ON PS.ID = PSP.STAGE\_ID JOIN BPM\_DEF\_STEP\_ACTIONS SA ON PSP.ID = SA.STEP\_ID JOIN CMN\_CAPTIONS\_NLS PN ON P.ID = PN.PK\_ID AND PN.TABLE\_NAME = 'BPM\_DEF\_PROCESSES' AND PN.LANGUAGE\_CODE = 'en' JOIN CMN\_CAPTIONS\_NLS PSPN ON PSP.ID = PSPN.PK\_ID AND PSPN.TABLE\_NAME = 'BPM\_DEF\_STEPS' AND PSPN.LANGUAGE\_CODE = 'en' JOIN CMN\_CAPTIONS\_NLS SAN ON SA.ID = SAN.PK\_ID AND SAN.TABLE\_NAME = 'BPM\_DEF\_STEP\_ACTIONS' AND SAN.LANGUAGE\_CODE = 'en' JOIN CMN\_CUSTOM\_SCRIPTS S ON SA.SCRIPT\_ID = S.ID WHERE S.SCRIPT\_TEXT LIKE '%uslx%' ORDER BY P.PROCESS\_CODE ## **------------------------------** ## **2** **How can I use SQL to find the calendar where a resource's non-working day is defined?** *Answer* The only SQL way to determine if a non-working day is a holiday (standard calendar) or vacation (resource calendar) is to compare the resource availability with the availability for an unmodified user (admin). The following SQL appears to do the trick: SELECT s.prj\_object\_id , CASE s.slice WHEN 0 THEN 8 ELSE 0 END hours , CASE WHEN (s.slice = 0 AND a.slice > 0) THEN 'Vacation' ELSE 'Holiday' END description FROM prj\_blb\_slices s LEFT OUTER JOIN prj\_blb\_slices a ON a.slice\_request\_id = 1 AND a.prj\_object\_id = 1 AND a.slice\_date = s.slice\_date WHERE s.slice\_request\_id = 1 AND TO\_CHAR(s.slice\_date, 'DY') NOT IN ('SAT','SUN') ## **------------------------------** ## **3** ** Why is there a difference in Actual Hours between Project and Team?** *Answer* The summed up actuals hours at the project level are dependent on the investment allocation job which aggregates values from the assignment blobs and puts them in blobs on the investment record. If these blobs get out of sync, you could see differences.  Also, the Investment Allocation job does not run against inactive projects, so you could see differences with inactive projects. Our preference is to pull the Actuals from the Assignment level, so you’re not dependent on the Investment Allocation job. ## **------------------------------** ## **4** **How can you apply a negative amount to Task Actuals? ** **We use manual transactions to track costs for some projects with an expense resource. Recently we entered the wrong cost for a task and pushed a reversal transaction to cancel it. The transaction went through well, and the Financial Plan is coming up with the correct numbers.** **However in Clarity PPM (CA PPM) Gantt, the resource still shows the actuals. Apparently negative amounts are not applied to Task Actuals with the Import Financial Actuals job. How can we apply this on the UI end?** *Answer* If expected actual cost and hours are zero, you can follow the steps below. But first, some things to consider . . . ETC will also be deleted, and when the Assignment Record is created again it won’t inherit the actual curve or actual cost curve from the WIP tables. If the resource is labor, we need to make sure there is no timesheet. Please test this non-prod and see whether it meets your requirement before doing it in Production. - Update Prassignment Record as show below: update prassignment set prextension=null, slice\_status=1 , practsum=0, actcost\_curve=null , actcost\_sum=0 where prid= - Run Timeslice Job - Update Resource ID to another value for those transactions in PPA\_WIP: update ppa\_wip set resource\_code='’ where project\_code='' and resource\_code='' and task\_id= - Delete the assignment via UI - Recreate the same assignment for the resource - Revert back the Step 2 changes:  update ppa\_wip set resource\_code=’' where project\_code='' and resource\_code='’ and task\_id= ## **------------------------------** ## **5** **Can you display Lookup Values in MSP as a pull-down?** **We're required to have a static lookup created in the Task Object and mapped to MSP, so that a Custom Attribute can be managed from MSP.** **We created Custom Lookup Attributes and inserted a corresponding row in MSPFIELD (Say Text19). Now in MSP, we only see 0,1,2 etc. values in the field . . .  although it is a Lookup Code/Value Static Lookup. We can also save the value back to CA PPM if I type 1 or 2 in Text19 in MSP (it sets the first or second lookup value).** **Is there a way we can have lookup codes and/or values in MSP in a pull-down to select? We tried MSP > Text19 > Right Click > Custom Fields... > Selected Lookup > and added lookup value/description, which could be workaround, but every user would need to do that at his/her MSP. Secondly, using this workaround, lookup values show up in a pull-down, but MSP still shows 0,1,2 as display text.** **What are we missing?** *Answer- Key Points* - It has to be mapped to a text field in MSP. - You cannot get it to show as a lookup in MSP – just text. - It has to be Dynamic query based lookup (if Static, create a Dynamic lookup that uses a Static one). - On the Clarity PPM side, make sure you're using a lookup code vs. enum. The tricky part is that the lookup display in Clarity PPM needs to be the ID vs. the name. So we suggest making your lookup codes more representative of the values you want to see in the UI. If you change the lookup in Clarity PPM to display the values vs. the code, it won't work. The MSP value has to be the lookup\_code as well. It cannot be the lookup name. ## **------------------------------** Please *feel free to comment on our answers and any alternative answers* you've found [within the complete article here, in the CA Community. ](https://communities.ca.com/docs/DOC-231168837)  Rego celebrates Fridays by sharing **Questions & Answers about Clarity PPM** with our ever-expanding knowledge-community, so we can all learn as much as possible. We love your input (always). And a special thanks to the brilliant [Navdeep Joshi](https://in.linkedin.com/in/navdeep-joshi-pmp-capm-389a8a9) and the Rego Team for this great material. By [Camille Pack](https://blog.regoconsulting.com/author/camille-pack)|July 15, 2016 #### Share on Social Media - [Tweet](https://twitter.com/share) ### About the Author: [Camille Pack](https://blog.regoconsulting.com/author/camille-pack) ![Camille Pack](https://blog.regoconsulting.com/hubfs/profile%20camille200-2.jpg) Camille Pack has been in marketing for over a decade and started her career as a college composition instructor during graduate school. Technical writing lends itself well to mastery, and in her time at Rego, Camille offered clients product support, configured environments, and served as both a project manager for an internal reporting group and a business analyst for a large external client. Camille holds an MA in Literature and Writing, and a BS in Biology. ### Related Posts ![blog_4_necessities_for_a_pmo_charter_gartner](https://blog.regoconsulting.com/hubfs/Blog%20Images/2018/February/4%20necessities/blog_4_necessities_for_a_pmo_charter_gartner.png) [Permalink](https://blog.regoconsulting.com/4-necessities-pmo-charter) #### [Clarity PPM | Four Necessities for a PMO Charter](https://blog.regoconsulting.com/4-necessities-pmo-charter) February 28, 2019 | [0 Comments](https://blog.regoconsulting.com/4-necessities-pmo-charter#comments-listing) ![upgrading_Blog](https://blog.regoconsulting.com/hubfs/upgrading_Blog.jpg) [Permalink](https://blog.regoconsulting.com/whats-new-in-ca-ppm-clarity-15.5-modern-ux-features) #### [Clarity PPM | Should We Upgrade to 15.5? - Modern UX Features](https://blog.regoconsulting.com/whats-new-in-ca-ppm-clarity-15.5-modern-ux-features) November 05, 2018 | [0 Comments](https://blog.regoconsulting.com/whats-new-in-ca-ppm-clarity-15.5-modern-ux-features#comments-listing) ![regoU-Blog-changes_Linkedin_523x273](https://blog.regoconsulting.com/hubfs/regoU-Blog-changes_Linkedin_523x273.jpg) [Permalink](https://blog.regoconsulting.com/clarity-early-bird-special-register-by-january-31st-to-save-300) #### [Clarity PPM | Early Bird Special at RegoUniversity](https://blog.regoconsulting.com/clarity-early-bird-special-register-by-january-31st-to-save-300) January 24, 2018 | [0 Comments](https://blog.regoconsulting.com/clarity-early-bird-special-register-by-january-31st-to-save-300#comments-listing) ![](https://blog.regoconsulting.com/hubfs/Podcast-episode6-01.jpg) [Permalink](https://blog.regoconsulting.com/the-ppm-podcast-episode-6-davey-zywiec) #### [Clarity PPM | The PPM Podcast: Davey Zywiec on Reporting Strategies](https://blog.regoconsulting.com/the-ppm-podcast-episode-6-davey-zywiec) December 20, 2017 | [0 Comments](https://blog.regoconsulting.com/the-ppm-podcast-episode-6-davey-zywiec#comments-listing) ![](https://blog.regoconsulting.com/hubfs/regU-5-star-01.jpg) [Permalink](https://blog.regoconsulting.com/ca-ppm-clarity-event-is-a-5-star-experience) #### [Clarity PPM | 5 Reasons You Can't Miss This PPM Conference](https://blog.regoconsulting.com/ca-ppm-clarity-event-is-a-5-star-experience) November 21, 2017 | [0 Comments](https://blog.regoconsulting.com/ca-ppm-clarity-event-is-a-5-star-experience#comments-listing) ![](https://blog.regoconsulting.com/hubfs/15_3_blog_header-01.jpg) [Permalink](https://blog.regoconsulting.com/6-things-you-need-to-know-about-ca-ppm-15.3) #### [Clarity PPM | Six Things You Need to Know about 15.3](https://blog.regoconsulting.com/6-things-you-need-to-know-about-ca-ppm-15.3) October 24, 2017 | [0 Comments](https://blog.regoconsulting.com/6-things-you-need-to-know-about-ca-ppm-15.3#comments-listing) [![Rego Home](https://no-cache.hubspot.com/cta/default/2652075/655865ab-5c17-4a7d-8626-9917913749d3.png)](https://cta-redirect.hubspot.com/cta/redirect/2652075/655865ab-5c17-4a7d-8626-9917913749d3) [![Contact Us](https://no-cache.hubspot.com/cta/default/2652075/80445cd5-d730-4608-a09c-dcaaeff582b4.png)](https://cta-redirect.hubspot.com/cta/redirect/2652075/80445cd5-d730-4608-a09c-dcaaeff582b4) ### Subscribe to Email Updates [![Share on Facebook](https://static.hubspot.com/final/img/common/icons/social/facebook-24x24.png)](http://www.facebook.com/share.php?u=https%3A%2F%2Fblog.regoconsulting.com%2Fquery-links-cmn_custom_scripts-table-to-process-name-find-a-calendar-with-sql-different-project-and-team-actual-hours-apply-a-negative-amount-to-task-actuals-and-display-%3Futm_medium%3Dsocial%26utm_source%3Dfacebook) [![Share on LinkedIn](https://static.hubspot.com/final/img/common/icons/social/linkedin-24x24.png)](http://www.linkedin.com/shareArticle?mini=true&url=https%3A%2F%2Fblog.regoconsulting.com%2Fquery-links-cmn_custom_scripts-table-to-process-name-find-a-calendar-with-sql-different-project-and-team-actual-hours-apply-a-negative-amount-to-task-actuals-and-display-%3Futm_medium%3Dsocial%26utm_source%3Dlinkedin) [![Share on Twitter](https://static.hubspot.com/final/img/common/icons/social/twitter-24x24.png)](https://twitter.com/intent/tweet?original_referer=https%3A%2F%2Fblog.regoconsulting.com%2Fquery-links-cmn_custom_scripts-table-to-process-name-find-a-calendar-with-sql-different-project-and-team-actual-hours-apply-a-negative-amount-to-task-actuals-and-display-%3Futm_medium%3Dsocial%26utm_source%3Dtwitter&url=https%3A%2F%2Fblog.regoconsulting.com%2Fquery-links-cmn_custom_scripts-table-to-process-name-find-a-calendar-with-sql-different-project-and-team-actual-hours-apply-a-negative-amount-to-task-actuals-and-display-%3Futm_medium%3Dsocial%26utm_source%3Dtwitter&source=tweetbutton&text=Clarity%20PPM%20%7C%C2%A0Learn%20with%20Rego%3A%20Display%20Lookup%20Values%20in%20MSP%20%2B%20More) ### Recent Rego Articles ### Sign up for our Weekly Newsletter ![rego-logo-white](https://blog.regoconsulting.com/hs-fs/hubfs/rego-logo-white.png?width=205&name=rego-logo-white.png) As the leading PPM, Work Management, and Agile services provider, we have helped hundreds of organizations achieve a higher return on their software investment. ### CONTACT US Questions? We can help. [info@regoconsulting.com](mailto:info@regoconsulting.com) 888.813.0444 ### Stay up-to-date Get the latest news in industry best practices, thought leadership, and software updates. ### Solution Capabilities [Clarity PPM](https://regoconsulting.com/clarity-ppm/) [Rally](https://regoconsulting.com/rally/) [Apptio](https://regoconsulting.com/apptio/) [Sciforma](https://regoconsulting.com/sciforma/) [Jira](https://regoconsulting.com/jira/) [Microsoft | SharePoint](https://regoconsulting.com/microsoft/) [Smartsheet](https://regoconsulting.com/smartsheet/) [Meisterplan](https://regoconsulting.com/meisterplan-itdesign/) ### Quick Links [Our Company](https://regoconsulting.com/our-company/) [RegoUniversity](https://www.regouniversity.com/) [RegoXchange](http://regoxchange.com/) [Careers](https://regoconsulting.com/careers/)   Copyright 2022 Rego Consulting Corporation [Linkedin](https://www.linkedin.com/company/rego-consulting?trk=biz-companies-cym)[Facebook](https://www.facebook.com/RegoConsulting/)[Youtube](https://www.youtube.com/channel/UCdA5xEiDrX--y2nVX2wC1UA?view_as=public)[Twitter](https://twitter.com/regoconsulting) [Privacy Policy](https://regoconsulting.com/privacy-policy/) ![](https://dc.ads.linkedin.com/collect/?pid=14053&fmt=gif)