we have a project outline code project Status and type which has following values

Open

    A

    B

    C

Closed

    A

    B

    C

Proposed

    A

    B

    C

More than 10 projects has values of Closed.B. So I need to collect the projects with this outline code value.

What I did.

I opened the MPS_Outline_Codes tables and scroll down to see the value in OC_CACHED_FULL_NAME. If value is Closed.B then I copied MSP_CODE_FIELDS.CODE_FIELD_ID = 188744608) AND MSP_CODE_FIELDS.CODE_UID = 49.

I then move to MSP_Code_Fields table and look for the projects that match these both values using

select * from msp_code_fields Where CODE_Field_ID=188744608 and Code_UID=49

Then I move towards the msp_projects table and then use the view creation screen to create the following View.

 

SELECT MSP_PROJECTS.PROJ_NAME, MSP_PROJECTS.PROJ_INFO_CAL_NAME, MSP_CODE_FIELDS.CODE_FIELD_ID, MSP_OUTLINE_CODES.PROJ_ID,

MSP_OUTLINE_CODES.OC_CACHED_FULL_NAME, MSP_CODE_FIELDS.CODE_UID

FROM MSP_CODE_FIELDS INNER JOIN

MSP_PROJECTS ON MSP_CODE_FIELDS.PROJ_ID = MSP_PROJECTS.PROJ_ID INNER JOIN

MSP_OUTLINE_CODES ON MSP_CODE_FIELDS.CODE_UID = MSP_OUTLINE_CODES.CODE_UID

WHERE (MSP_CODE_FIELDS.CODE_FIELD_ID = 188744608) AND (MSP_CODE_FIELDS.CODE_UID = 49)

This Query will return the following

Project Name         Calendar    Code ID     Project ID    Field Value    CODE UID

ProjectName.Published     Standard    188744608    194        Closed.Site B    49

Regards,

Jerry