Announcement

Collapse
No announcement yet.

Multiple Select Statements on a Select (Drop Down) Field

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Multiple Select Statements on a Select (Drop Down) Field

    I have a Select (Drop Down) Field named locations which is supposed to implement the following logic;-

    A logged on user who is linked to a location named Head Office should be able to see all locations without a filter i.e.

    select LocationID, Location
    from Locations


    But for any other user outside Head Office the Select Field should only show the Logged on User's Location i.e.

    Select LocationID, Location
    from Locations
    where LocationID = [V_LocationID]


    In summary I am trying to achieve something like below statement, which will work on a Select Field.

    if ([V_LocationID]==1)

    {

    select LocationID, Location
    from Locations

    }

    ELSE

    {

    Select LocationID, Location
    from Locations
    where LocationID = [V_LocationID]

    }

    I'll appreciate any assistance in resolving this problem.

  • #2
    Originally posted by amgwazo View Post
    I have a Select (Drop Down) Field named locations which is supposed to implement the following logic;-

    A logged on user who is linked to a location named Head Office should be able to see all locations without a filter i.e.

    select LocationID, Location
    from Locations


    But for any other user outside Head Office the Select Field should only show the Logged on User's Location i.e.

    Select LocationID, Location
    from Locations
    where LocationID = [V_LocationID]


    In summary I am trying to achieve something like below statement, which will work on a Select Field.

    if ([V_LocationID]==1)

    {

    select LocationID, Location
    from Locations

    }

    ELSE

    {

    Select LocationID, Location
    from Locations
    where LocationID = [V_LocationID]

    }

    I'll appreciate any assistance in resolving this problem.
    In general the basic sql statement to fill the dropdown is generated from a table. The window is shown as soon as you change the field type to 'select'. Next you can add a where clause to this statement and use a global variable for that, i.e.

    Code:
    Select LocationID, Location
    from Locations
    where [global_where]
    In the onapplicationinit you can set this global where variable to the criteria you need.

    Code:
    [global_where]=" LocationID = [V_LocationID]";
    
    or 
    
    [global_where]=" 1=1 ";
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      Originally posted by aducom View Post
      In general the basic sql statement to fill the dropdown is generated from a table. The window is shown as soon as you change the field type to 'select'. Next you can add a where clause to this statement and use a global variable for that, i.e.

      Code:
      Select LocationID, Location
      from Locations
      where [global_where]
      In the onapplicationinit you can set this global where variable to the criteria you need.

      Code:
      [global_where]=" LocationID = [V_LocationID]";
      
      or 
      
      [global_where]=" 1=1 ";
      Thanks a lot Albert, works like a charm

      Comment


      • #4
        Originally posted by aducom View Post
        In the onapplicationinit you can set this global where variable to the criteria you need.

        Code:
        [global_where]=" LocationID = [V_LocationID]";
        
        or 
        
        [global_where]=" 1=1 ";

        I'm trying to use [global_where] in a form SQL Where Condition.
        This works when the form is run from another application, setting and passing the [global_where] to the form.
        But when I run the form directly, SC prompts me for [global_where] before executing the form.
        In the form I did set [global_where] in the onapplicationinit event, but it seems that SC checks if [global_where] exist before executing any of the form's events\code.

        What I'm trying to do is to use the same form to:
        • edit a single record, when the form is run from a grid by selecting a grid row (e.g. the grid will pass [global_where] = "id=5")
        • edit all records when the form is run directly with no params. (one of the form's own events is supposed to set [global_where] = "1=1" before executing the SQL statement, but it doesn't seem to work)

        Is there any solution?
        Last edited by robydago; 02-04-2015, 10:55 AM.

        Comment


        • #5
          Originally posted by robydago View Post
          I'm trying to use "[global_where]" in a form SQL Where Condition.
          This works when the form is run from another application, setting and passing the [global_where] to the form.
          But when I run the form directly, SC prompts me for [global_where] before executing the form.
          In the form I did set [global_where] in the onapplicationinit event, but it seems that SC checks if [global_where] exist before executing any of the form's events\code.
          Is there any solution?
          The prompt only appears when you run the application in development mode, not in production mode. It can also be prevented by changing the global variable type (in/out).
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment

          Working...
          X