The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

validation combining active projects and proposals?

SOLVED
Go to solution
Highlighted
Andy McMahon_1
Collector

validation combining active projects and proposals?

Hello again. 

I have a need for a field that has an autofill validation that will give me a list of all active proposals and projects.  Is it possible to write this into a validation?  How would I do so?  

12 REPLIES
Erik Cole
Honored Contributor

Re: validation combining active projects and proposals?

This should get you in the right direction...

 

select request_id,'Project',project_name
from kcrt_fg_pfm_project
union all
select request_id,'Proposal',proposal_name
from kcrt_fg_pfm_proposal

Andy McMahon_1
Collector

Re: validation combining active projects and proposals?

This appears to have worked, but I want to make sure,

 

I'm not using the default PFM - Proposals, that shouldn't matter in what you wrote, right? 

Andy McMahon_1
Collector

Re: validation combining active projects and proposals?

OH! and does this limit it to active projects and proposals?  Meaning, once they're closed, they're gone from this list?  Right? 

Erik Cole
Honored Contributor

Re: validation combining active projects and proposals?

No, not as long as whatever request type you're using has the proposal field group. Note also that this query as written does not filter out any closed or canceled requests...you'll need to add your own filters however you want.

Celil
Esteemed Contributor

Re: validation combining active projects and proposals?

Hi Andy,

 

your requirements:

  • list projects and proposals
  • closed/rejected ones not listed
  • there is not requirement that Project, Proposal configuration (like PFM - Proposals, PFM - Projects)

pre-requirements:

  • please fill project request type ID to XXXXX
  • please fill proposal request type ID to YYYYY

query:

select request_id, description, decode(request_type_id,XXXXX,'Project',YYYYY,'Proposal')

from kcrt_requests 

wher

percent_complete <> 100

and request_type_id IN (XXXXX,YYYYY)

 

Celil

Celil

IT Governance Professional
& PPM Solution Architect
Andy McMahon_1
Collector

Re: validation combining active projects and proposals?

Thanks Celil, does Request type ID equal a number? Where would I find it? 

Celil
Esteemed Contributor

Re: validation combining active projects and proposals?

Hi Andy,

 

There is a simple way: You can access PPM as admin, can't you? If yes, please follow this procedure:

 

  • First main step is getting request type name of project and proposal. (see: picture1)
  1. open workbench
  2. click "Demand Mgmt" at the left side. Than just click list button at the right screen.
  3. you get a list of request type. Write down name of the porposal and the project request types exactly.
  • Second main step is getting these request types ID (at the the SQL xxxxx and yyyyy) (see: picture2)
  1. click "Sys Admin" at the left side. Than click "Server Tools".
  2. you see two screen: Admin Tools and SQL Runner. We are considering with SQL Runner
  3. run this sql in SQL Runner screen: select request_type_id, request_type_name from KCRT_REQUEST_TYPES where request_type_name = 'project request type name' or request_type_name = 'proposal request type name'
  4. You will get 2 request type id. You can set XXXXX as project's request type id, You can set YYYYYY as porposal's request id in the other SQL

Celil

Celil

IT Governance Professional
& PPM Solution Architect
Andy McMahon_1
Collector

Re: validation combining active projects and proposals?

Great, and not to push it,but, how would I pull the NAME instead of description? 

Celil
Esteemed Contributor

Re: validation combining active projects and proposals?

Hi Andy, I'm sorry I couldn't understand you, could you detail what you mean?
Celil

IT Governance Professional
& PPM Solution Architect
Andy McMahon_1
Collector

Re: validation combining active projects and proposals?

No worries. 

In your query, you have it pulling the description of the project or proposal.  What's the easiest way to have it pull the name?  I've looked at the table and didn't see it on there, so I don't know if there's a simple way to do this or not.  

Celil
Esteemed Contributor

Re: validation combining active projects and proposals?

To get request type a project and proposal, you can use Search Request in the PPM menu. In search request result page (by default) second column is request type.
Celil

IT Governance Professional
& PPM Solution Architect
Erik Cole
Honored Contributor
Solution

Re: validation combining active projects and proposals?

Andy, the names of the projects & proposals are contained in the two field group tables I referenced in my original reply: kcrt_fg_pfm_project, kcrt_fg_pfm_proposal. To filter on other criterea, such as status or % complete, etc you would need to join out to kcrt_requests or kcrt_request_details.

Try something like

Select a.* from
(
select request_id,project_name,'Project' "TYPE"
from kcrt_fg_pfm_project
union all
select request_id,proposal_name,'Proposal' "TYPE"
from kcrt_fg_pfm_proposal
) a
Join kcrt_requests_v v on v.request_id = a.request_id
Where v.status_code = 'IN_PROGRESS'
And v.batch_number = 1
order by 2

//Add this to "OnDomLoad" event