Clarity

  • 1.  Need Help in Paramterized Query.

    Posted Jul 21, 2012 01:29 PM
    Hello All,

    I have created a Paramterized Dependent Lookup for Resource Manager in Custom Object.

    Attributes are:-
    Resource Name :- Using system Resource Active lookup.
    Resource Manager:- Using parametrized Query

    Steps:-
    1. Create an instance of object with Res1 and RM1 and save it.
    2. Change the resource manager of Res1 to RM2 through resource profile.
    3. Create another instance with Res1 and RM2 .
    4. Now in first instance the resource manager field shows blank as resource manager is changed but the resource id of previous manager is stored in database.

    Do you have any idea how to retain the value of previous manager?

    I am using below NSQL query for resource manager.

    SELECT DISTINCT @SELECT:SRM_RES1.ID:ID@,
    @SELECT:SEC_USERS.LAST_NAME ||', '|| SEC_USERS.FIRST_NAME:FULLNAME@,
    @SELECT:SRM_RES1.UNIQUE_NAME:UNIQUE_NAME@,
    @SELECT:SEC_USERS.LAST_NAME:LAST_NAME@,
    @SELECT:SEC_USERS.FIRST_NAME:FIRST_NAME@
    FROM SRM_RESOURCES SRM_RES,
    CMN_SEC_USERS SEC_USERS,
    SRM_RESOURCES SRM_RES1
    WHERE SRM_RES.MANAGER_ID = SEC_USERS.ID
    AND SRM_RES1.USER_ID=SEC_USERS.ID
    AND (@WHERE:PARAM:USER_DEF:STRING:MGRID@ IS NULL OR
    @WHERE:PARAM:USER_DEF:STRING:MGRID@ = SRM_RES.ID)
    AND @FILTER@
    @BROWSE-ONLY:AND SRM_RES1.IS_ACTIVE=1:BROWSE-ONLY@

    Thanks in Advance!!

    Regards
    Tidi


  • 2.  RE: Need Help in Paramterized Query.

    Posted Jul 23, 2012 04:20 AM
    I have had problems using the BROWSE-ONLY construct in PARAMETERISED lookups;

    See ; 15574444

    I can only suggest that your SQL needs (without using BROWSE-ONLY) to be able to return RM1against instance #1 - i.e. you need to "code in" the logic that identifies that a value that would NOT be returned by your main logic IS returned when that value has been selected against the current object instance. (essentially code the "BROWSE-ONLY" logic into the SQL rather than relying on the BROWSE-ONLY construct)


  • 3.  RE: Need Help in Paramterized Query.

    Posted Jul 23, 2012 09:28 AM
    Hello Dave,

    Thanks for your response.

    I have tried but no success, can you please help me in NSQL?

    Thanks
    Tidi


  • 4.  RE: Need Help in Paramterized Query.

    Posted Jul 23, 2012 12:26 PM
    Hi Tidi,

    Use the below query,

    I just changed the where clause column from SRM_RES.ID to SEC_USERS.ID



    SELECT DISTINCT @SELECT:SRM_RES1.USER_ID:ID@,
    @SELECT:SEC_USERS.LAST_NAME ||', '|| SEC_USERS.FIRST_NAME:FULLNAME@,
    @SELECT:SRM_RES1.UNIQUE_NAME:UNIQUE_NAME@,
    @SELECT:SEC_USERS.LAST_NAME:LAST_NAME@,
    @SELECT:SEC_USERS.FIRST_NAME:FIRST_NAME@
    FROM SRM_RESOURCES SRM_RES,
    CMN_SEC_USERS SEC_USERS,
    SRM_RESOURCES SRM_RES1
    WHERE SRM_RES.MANAGER_ID = SEC_USERS.ID
    AND SRM_RES1.USER_ID=SEC_USERS.ID
    AND (@WHERE:PARAM:USER_DEF:STRING:MGRID@ IS NULL OR
    @WHERE:PARAM:USER_DEF:STRING:MGRID@ =SEC_USERS.ID)
    AND @FILTER@
    @BROWSE-ONLY:AND SRM_RES1.IS_ACTIVE=1:BROWSE-ONLY@




    because SRM_Resources USER_ID is always same as your CMN_SEC_UESR ID and all the lookups used either SRM RESOURCES USER_ID OR CMN_SEC_USERS ID. If your Clarity setup is having anything other than labor resource setup loke Role, Material etc then your SRM_REsources ID will not same as CMN_SEC_UERS ID.

    I hope this will work


    Shalinee


  • 5.  RE: Need Help in Paramterized Query.

    Posted Jul 23, 2012 02:18 PM
    Hello Shalinee,

    Thanks for the response, but my problem is that whenever i changed the resource manager of res1 to rm2 from rm1 then the value in the custom object instance got blank.

    Paramterized lookup query always executed when we referesh the page, do you have any idea how to code in such a way to retain the previous manager value as mentioned by Dave.


    Thanks
    Hussain


  • 6.  RE: Need Help in Paramterized Query.
    Best Answer

    Posted Jul 23, 2012 03:52 PM
    As I said ; you need to "code" the lookup to return the "old RM" value when its is set on your object instance even when your existing logic does not return the "old RM"

    --

    I'm going to explain this is psuedo-code not NSQL (since I have no idea what your object or columns are called) ....

    So you have a parameterised lookup that says;
    SELECT  resource_manager details
    FROM
    some tables with some joins
    WHERE
    << your parameter id >>  IS NULL OR << your parameter id >> = SRM_RES.ID
    ...and your join login in "some tables with some joins" only picks up the resource_manager that is current (so does NOT return the existing resource manager when you have change the data)

    --

    What I'm saying is that you need your parameterised lookup to say something like ;
    SELECT  resource_manager details
    FROM
    some tables with some joins
    WHERE
    << your parameter id >>  IS NULL OR << your parameter id >> = SRM_RES.ID
    UNION
    SELECT user_details
    FROM srm_resources & cmn_sec_users -- note there are NO joins on the "current resource manager" in this part of the select
    WHERE srm_resources.id = ( SELECT the current value of your [u]custom attribute[/u] FROM your custom object instance WHERE id = << the object id of your custom object instance >>
    (Note that this has introduced a second parameter to your lookup; "the object id of your custom object instance")


  • 7.  RE: Need Help in Paramterized Query.

    Posted Jul 23, 2012 07:20 PM
    I am sorry I misunderstood the requirement. Dave has pointed the correct option, I tried the same and it is working ....

    SELECT DISTINCT @SELECT:FINAL.USER_ID:ID@,
    @SELECT:FINAL.FULL_NAME:FULLNAME@,
    @SELECT:FINAL.LAST_NAME:LAST_NAME@,
    @SELECT:FINAL.FIRST_NAME:FIRST_NAME@
    FROM (SELECT SRM_RES.USER_ID user_id,
    SRM_RES.FULL_NAME full_name,
    SRM_RES.FIRST_NAME first_name,
    SRM_RES.LAST_NAME last_name
    FROM SRM_RESOURCES SRM_RES,
    CMN_SEC_USERS SEC_USERS,
    SRM_RESOURCES SRM_RES1
    WHERE SRM_RES1.USER_ID=SEC_USERS.ID
    AND SRM_RES1.MANAGER_ID=SRM_RES.USER_ID
    AND SRM_RES1.IS_ACTIVE=1
    AND (@WHERE:PARAM:USER_DEF:STRING:MGRID@ IS NULL OR
    @WHERE:PARAM:USER_DEF:STRING:MGRID@ =SRM_RES1.ID)
    UNION
    SELECT SRM_RES2.USER_ID user_id,
    SRM_RES2.FULL_NAME full_name,
    SRM_RES2.FIRST_NAME first_name,
    SRM_RES2.LAST_NAME last_name
    FROM ODF_CA_TEST TEST, SRM_RESOURCES SRM_RES2
    WHERE SRM_RES2.USER_ID=TEST.AHM_RES_MGR_T----custom object resource name attribute id
    AND SRM_RES2.IS_ACTIVE=1
    AND (@WHERE:PARAM:USER_DEF:STRING:MGRID@ IS NULL OR
    @WHERE:PARAM:USER_DEF:STRING:MGRID@ =TEST.AHM_RES_NAME_T----custom object resource manager attribute id)
    AND @WHERE:PARAM:USER_DEF:string:UNIQUEID@ =TEST.code ----custom object code attribute
    )FINAL
    WHERE @FILTER@


    Remove the comments from above query inside the from section and use in lookup. Also you can change query as per the need as I have added code attribute of custom object just to make sure the uniqueness of the value was previous and now.


  • 8.  RE: Need Help in Paramterized Query.

    Posted Jul 24, 2012 03:36 AM
    ^ @shalinee : thanks for trying the NSQL.... I just did not have the enthusiasm/energy! :grin:


  • 9.  RE: Need Help in Paramterized Query.

    Posted Jul 24, 2012 02:20 PM
    Thanks Dave and Shalinee for the solution.

    I have used the below Query to fetch the manager name from the instance.

    SELECT SRM_RES2.ID ID,
    SRM_RES2.FULL_NAME full_name,
    SRM_RES2.FIRST_NAME first_name,
    SRM_RES2.LAST_NAME LAST_NAME
    FROM SRM_RESOURCES SRM_RES2
    WHERE SRM_RES2.IS_ACTIVE=1
    AND SRM_RES2.ID = (select adtime.bmo_res_mgr from odf_ca_bmo_time_adjust adtime where adtime.code = @WHERE:PARAM:USER_DEF:string:UNIQUEID@)


    I really appreciate for your time.


    Regards
    Tidi