Multiple Select Statements on a Select (Drop Down) Field

Applications Forms
#1

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

[QUOTE=amgwazo;32078]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.[/QUOTE]

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.


Select LocationID, Location
from Locations
where [global_where]

In the onapplicationinit you can set this global where variable to the criteria you need.


[global_where]=" LocationID = [V_LocationID]";

or 

[global_where]=" 1=1 ";
#3

[QUOTE=aducom;32080]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.


Select LocationID, Location
from Locations
where [global_where]

In the onapplicationinit you can set this global where variable to the criteria you need.


[global_where]=" LocationID = [V_LocationID]";

or 

[global_where]=" 1=1 ";

[/QUOTE]

Thanks a lot Albert, works like a charm

#4

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?

#5

[QUOTE=robydago;32130]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?[/QUOTE]

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).