[color green]Green Text[/cMy customer wants "all" in param values..... 1

[AlanApplications]

{workorder.status} = {?Status} and
{workorder.worktype} = {?Worktype} and
{workorder.leadcraft} = {?technican}
= iif({?Status}="All",{workorder.status},{?Status}) and
= iif({?Worktype}="All",{workorder.worktype},{?Worktype}) and
= iif({?technican}="All",{workorder.leadcraft},{?technican})

 

[lbass]

Add a value of "All" to your list of values for each parameter, then use a record selection formula like this:

(
if {?Status} <> "All" then
{workorder.status} = {?Status} else
if {?Status} = "All" then
true
) and
(
if {?Technician} <> "All" then
{workorder.leadcraft} = {?Technician} else
if {?Technician} = "All" then
true
) and
(
if {?Worktype} <> "All" then
{workorder.worktype} = {?Worktype} else
if {?Worktype} = "All" then
true
)

-LB

 

[horatiog]

Hi,

If its in the record selection formula, I think you can simplify this even further:

(	{?Status} = "All"
	or
	{workorder.status} in {?Status}
)
and
(	{?Technician} = "All" 
	or 
	{workorder.leadcraft} in {?Technician} 
)
and
(	{?Worktype} = "All" 
	or
	{workorder.worktype} in {?Worktype} 
)

I've also shown in the above example how you would code it where your parameters are multi-select. (E.g. Technician is 'Tom','Dick' or 'Harry') The result is a boolean and the criteria that you have set to "All" do not get passed to the DB in the query. Check out a few runs with different parameters and look at the generated SQL Query (Database | Show SQL Query.) Hope this helps.

Regards,
Harry.

 

[AndyMc]

Harry,

I like your solution - I did look at the SQL generated and you're right, when "All" is selected nothing new is added to the WHERE clause (this was true with LB's solution also).

Using IN was great. It works when you have Multiple Discrete Values for the parameter and it also works for a Range Parameter.

Andy