How to handle in AUtoSearchDialog the search condition "show me all types or one specific type"

francescof · November 2018

I am trying to create a report in which the user can see employees of any employee_type or only employees of one employee_type.

To achieve this i created 2 parameters:
AllEmployeeTypes (dtInteger, based on a lookupquery that returns 0/"False", -1/"True")
EmployeeType (dtInteger, based on a lookupquery that returns ID_Employee_TYpe / Description_Employee_Type)

the select statement of the main report dataview has a where clause made like this:
WHERE
(-1 = :AllEmployeeTypes) OR (ID_EMPLOYEE_TYPE = :EmployeeType)

as a consequence both parameters must have a value if not the query will not run, so both are manadatory.

In practice only if AllEMployeeTypes is False the EmployeeType parameter is used, but the user must now give a value to both parameters.

Somehow what i am trying to achieve is to give a meaningful default value for the EmployeeType parameter so the user does not have to set it (especially in the case when he wants to search for all the types).
I can achieve this by hardcoding an ID in the ReportBeforeAutoSearchDialogCreate event:

Report.Parameters['EmployeeType'] := 4;

This works, anyway 4 won't exist in all the databases, i would like that this value comes from a dataview with a query like this:
SELECT TOP 1 ID_EMPLOYEE_TYPE , DESCR_EMPLOYEE_TYPE
FROM EMPLOYEES
ORDER BY DESCR_EMPLOYEE_TYPE

but this query is executed only after the AutoSearchDialog is closed, so it is of no use. Is there a way to achieve what i am looking for with the built in report designers features? I think I could make my own function "initliaize parameter (aParameterName: string)" taht runs a query at the right time, but i would not like to do this now.

I hope I expressed myself.

nardmoseley · November 2018

Hi Francesco,

I recommend implementing a lookup list for EmployeeType. This provides user-friendly interface and solves the problem.


SELECT DISTINCT ID_EMPLOYEE_TYPE , DESCR_EMPLOYEE_TYPE
FROM EMPLOYEES
ORDER BY DESCR_EMPLOYEE_TYPE

francescof · November 2018

Hi Nard, thanks for the reply.

I use a lookuplist already, the problem is to initialize the lookupcombobox. To do this i must hardcode a value in the parameter's Value property.

But if i do not want to hardcode the value and i want to retrieve it from the db using a dataview i am not able to succeed since the dataview's query are executed after the autoserachdialog is closed.

In fact i achieved it by creating a custom RAP function with this signature:
function GetValueFromQuery(SQLQuery: string): Variant;
(this creates a Query at runtime, opens the query, move to First and retrieves FIelds[0].asVariant)

I can call this in BeforeAutoSearchDialogCreate and it works, anyway I just added this function and in old deployed versions of my applciation i do not have it and i would like somehow to achieve the same result, so i am asking whether there is a way to set the selected value of the lookuplist with a value read form the db.

nardmoseley · November 2018

Hi Francesco,

The RAP function you implemented is the only solution.

You could try another approach. RB implements the AutoSearch 'ShowAllValues' feature by dynamically building the where clause. When ShowAllValues True, the where condition is not added to the SQL. You could try implementing the BeforeOpenDataPipelines event to to use TdaSQLBuilder to add the Where clause when ShowAllValues is False.

francescof · November 2018

Thanks a lot. I am glad to know that i found the correct solution on my own, I searched for TdaSQLBuilder in RB WIki but there is not an example that shows how to manipulate an existing dataview, could you please give me a link?

Thanks.

How to handle in AUtoSearchDialog the search condition "show me all types or one specific type"

Comments