Clarity

 View Only
Expand all | Collapse all

Restrict Static Dependent Lookup selection based on attribute value

  • 1.  Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 24, 2020 01:49 PM
    I've created a static dependent lookup on project tasks. The selectable options on that lookup are based on 2 different project types (Run or Grow). This project type is a custom attribute held in the project itself. The top level of the static dependent lookup is either Run or Grow, with multiple options within each.

    Does anybody know of a way to limit the user to only selecting options from the static dependent lookup that fall within each project category - based on the selection previously made within the project.

    I'm trying to programmatically restrict users from selecting a grow task type on run projects and vice versa.

    ------------------------------
    Senior Systems Admin
    TD Ameritrade
    ------------------------------


  • 2.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 24, 2020 03:14 PM
    In Classic you could use display condition in the view.


  • 3.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 24, 2020 03:39 PM
    Don't display condition only apply to sections on pages? I'm wanting the choices available in the lookup dropdown/selection to be conditional.

    ------------------------------
    Senior Systems Admin
    TD Ameritrade
    ------------------------------



  • 4.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 25, 2020 02:19 AM

    After giving it some more thought display condition might work, but it would be complicated and not user friendly.

    As you say you would make a section in the properties to show among others  one of two attributes depending on the project type. Each would have a look up with only the values for the project type. After selecting and saving a process would insert the value the to be used for the tasks.

    The same way if you wanted the lookup to be for the tasks.

    If I remember correct one of the project properties attribute works the way  you want ie the values available in the lookup depend on the value of another attribute.




  • 5.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 25, 2020 02:21 AM
    It is Project status, but it is hard coded. See https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?MessageKey=45869f3c-2319-4035-92a4-47deab5ae5d2&CommunityKey=7f0cbca3-5f93-4d44-a369-1a8ce98f5578&tab=digestviewer#bm45869f3c-2319-4035-92a4-47deab5ae5d2

    so that does not help you. Sorry


  • 6.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 25, 2020 03:45 AM
    Try putting the values and project type in a custom object and then create a dynamic lookup querying the object data with a where condition on the project type.


  • 7.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 28, 2020 10:58 AM
    I'm working on going this route (custom object) and I'm having trouble writing the NSQL for the lookup. 

    I created a Task Type custom object. There are a total of 4 different task types:

    (1) "Administration", (2) "Construct, Test, Implement", (3) "Maintenance and Support", (4) "Planning, Scope, Design"

    The project can be labeled either Run, Grow or Transform. The tasks in the project will need to be labeled with a task type and I want the task type lookup to be limited to the options below based on that label.

    2 & 4 if the project is Grow, 1 & 3 if the project is Run and it can be any of the 4 if the project is Transform.

    I know I'm likely going to need a case statement, but I'm not 100% sure how to go about it and also how to reference the parent project in the NSQL.

    When I create the lookup, do I set the Object to the Task (since the lookup will be on the create task screen), or do I set it to the project (since I'll be referencing a field on the project (the label is on the odf_ca_inv table).



    ------------------------------
    Senior Systems Admin
    TD Ameritrade
    ------------------------------



  • 8.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 28, 2020 12:28 PM
    The "custom object" requirement is misleading i think ; it was only suggested if you need (want?) to maintain the relationship between project-type and task-type in data somehow in the application.

    The NSQL based task-lookup just needs to take a parameter project id which looks in NSQL like this @WHERE:PARAM:USER_DEF:INTEGER:project_id@

    In your NSQL you can just pull the data from the project instance based on that id and "hard-code" that relationship you have described (unless you want to maintain the relationship in a custom-object)

    When you associate the attribute on the task object with that task-lookup it will (because of the parameter in the NSQL) get you to map that parameter - you have lots of choices (any of the attributes on the task) but you want the odf_parent_id which is the id of the tasks's parent - i.e. the project id.


  • 9.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 28, 2020 12:49 PM

    I tried the NSQL below, but I'm doing something wrong as I'm getting an error:

    ERROR ODF-0001: Unable to register dynamic query lookup: Could not retrieve or register the nsql::NSQL Syntax exception: NSQL_SYNTAX_ERROR_UNKNOWN_CONSTRUCT, Info: @SELECT:DIM:USER_DEF:IMPLIED:TASK:X.ID:ID@.

    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:TASK:X.ID:ID@,
    @SELECT:DIM_PROP:IMPLIED:TASK:X.CODE:CODE@,
    @SELECT:DIM_PROP:IMPLIED:TASK:X.NAME:NAME@,
    @SELECT:DIM_PROP:IMPLIED:TASK:X.TDA_TT_GVR:GVR@

    from
    (select tt.id, tt.code, tt.name, tt.tda_tt_gvr
    from odf_ca_tda_task_type tt
    inner join odf_ca_inv p on p.tda_gvr = tt.tda_tt_gvr
    where p.id = @WHERE:PARAM:USER_DEF:INTEGER:odf_parent_id@) x
    WHERE @FILTER@



    ------------------------------
    Senior Systems Admin
    TD Ameritrade
    ------------------------------



  • 10.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 28, 2020 01:39 PM
    Try just
    select tt.id, tt.code, tt.name, tt.tda_tt_gvr
    from odf_ca_tda_task_type tt
    inner join odf_ca_inv p on p.tda_gvr = tt.tda_tt_gvr
    where p.id = @WHERE:PARAM:USER_DEF:INTEGER:odf_parent_id@

    The reason why I suggested using a custom object to store the values was that you do not need admin rights to update the values.
    They can be updated on the application side and not in the lookup code.
    You could put fields Task type and project type in the custom object and build your query with case statement

    Your Case statement could be something like

    (Case p.project_type   ='Run'
    Select (tt.id, tt.code, tt.name, tt.tda_tt_gvr from tt.tda where tt:tda.project_grv='Run')

    Case p.project_type ='Grow'
    ....
    Case p.project_type ='Transform'
    ------
    Else 
    The else statement would give the value "Enter project type first"


  • 11.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 29, 2020 03:28 AM
    Edited by David Morton Sep 29, 2020 04:43 AM
    ^ you are getting those "NSQL errors" as your syntax is wrong, you are using the NSQL syntax for query definition (dimensions/properties etc), for dynamic lookup NSQL it is a different (simpler) structure - have a look at any of your existing dynamic lookups for examples.

    -

    EDIT : here is (untested) what I was thinking about it could look like; you'd map the attribute hidden key to the t_type but use the description as the display value (and obviously change columns/descriptions to match your settings)

    SELECT
    @SELECT:cte_mapping.t_type:t_type@
    ,@SELECT:cte_mapping.t_type_desc:t_type_desc@
    FROM
    odf_ca_project op
    JOIN
    (
    SELECT 'RUN' as p_type, '(1) Administration' as t_type_desc , 1 as t_type FROM DUAL UNION
    SELECT 'RUN' , '(3) Maintenance and Support' , 3 as t_type FROM DUAL UNION
    SELECT 'GROW' , '(2) Construct, Test, Implement' , 2 as t_type FROM DUAL UNION
    SELECT 'GROW' , '(4) Planning, Scope, Design' , 4 as t_type FROM DUAL UNION
    SELECT 'TRANSFORM' , '(1) Administration' , 1 FROM DUAL UNION
    SELECT 'TRANSFORM' , '(2) Construct, Test, Implement' , 2 as t_type FROM DUAL UNION
    SELECT 'TRANSFORM' , '(3) Maintenance and Support' , 3 as t_type FROM DUAL UNION
    SELECT 'TRANSFORM' , '(4) Planning, Scope, Design' , 4 as t_type FROM DUAL
    ) cte_mapping ON op.my_project_type = cte_mapping.p_type
    WHERE op.id = @WHERE:PARAM:USER_DEF:INTEGER:project_id@
    AND @FILTER@

    No 'custom object' involved, so if you wanted to change/extend the mapping it would be a code change (in that mapping common table expression thing (cte_mapping) )


  • 12.  RE: Restrict Static Dependent Lookup selection based on attribute value

    Posted Sep 25, 2020 03:59 AM
    I don't think I've seen anything that would help you for static dependant lookups (over just making the top-level of your lookup-hierarchy either "run" or "grow").

    In Classic i would tend to just use a dependant lookup (so NSQL based) which could then display the relevant values according to the "run" or "grow" selected against the object instance (however the instance would have to be saved first in order to do this). Lots of examples on here around that sort of lookup.

    I also recall seeing a much cleverer technique on here (again for Classic) that did not require the "save" and was all done at browser level - I can not quickly find the discussion for that just now but if you search around you might find it (the demo was something to do with regions/cities or region/countries that sort of thing I think - was posted by one of the (now ex) CA people)