Ramadan

Dynamic-Parametrized NSQL Lookup name not showing in grid column

Discussion created by Ramadan on Mar 31, 2010
Hello,  I have a strage requirement, in which i have to link to project object with another "Corporate Objective" object. the relation is many to many. and there is an extra information to be added between that link (contribution), and this is done through a project sub object called "Project Alignment"  When a user adds a project alignment object, he selects the corporate objective (from a lookup) and selects the contribution to that objective. the main requirement is that the user cannot chose duplicate corporate objectives for the same project (i.e. corporate objectives selected should be unique).  I have done the following:1. Created a seperate object called "Corporate Objective". It has a name and will link with other objects for reporting later on2. Created a sub object from the Project object called "Project Alignment", it has two attributes: a custom lookup for the "Corporate Objective" and a lookup for the contribution (Low, Medium and High)3. I have created a "Browse Corporate Objectives" lookup as a dynamic parametrized NSQL lookup, in which i pass the project ID (Parent ID) as a parameter so i will only show the remaining corporate objectives that were not selected (so a user will not select the same corporate objective twice).  things are working fine, except that i cant see the corporate objective name in the "Project Alignment" list under the project properties page (see attached "Value not Showing.jpg"). Of course i have customized the list view for the Project Alignment object to show the selected corporate objectives  When i click on the "Project Alignment" object to open its propertoes... i got the values right and i see things fine. But it does not show on the grid.  here are the table for each object:
- Corporate Objectives: ODF_CA_CORPORATE_OBJ

- Project Alignment: ODF_CA_PROJECT_ALIGHMNT 

 

 

and here is the NSQL Query:

 

SELECT        @SELECT: O.ID: Objective_ID@ ,
              @SELECT: O.NAME: Objective_Name@ ,
              @SELECT: O.corp_obj_weight: Objective_Weight@

FROM          ODF_CA_CORPORATE_OBJ O

WHERE         @FILTER@ 
              AND
              (
                O.ID IN (SELECT PA.corporate_objtv from ODF_CA_PROJECT_ALIGHMNT PA WHERE PA.ID = @WHERE:PARAM:USER_DEF:INTEGER:ObjectiveAlignmentID@ )
                OR
                O.ID NOT IN (SELECT PA2.corporate_objtv from ODF_CA_PROJECT_ALIGHMNT PA2 WHERE PA2.ODF_PARENT_ID = @WHERE:PARAM:USER_DEF:INTEGER:ProjectID@ ) 
               OR
               (SELECT count(PA.corporate_objtv) from ODF_CA_PROJECT_ALIGHMNT PA WHERE PA.ODF_PARENT_ID = @WHERE:PARAM:USER_DEF:INTEGER:ProjectID@ ) = 0
I am passing the same "Project Alignment ID" as a parameter becuase i want to show the selected corporate objetive in the lookup, i found this to be the only way to avoid any errors.  thanks,

Attachments

Outcomes