I'm attempting to make a search functionality on one of my SmartForms, and having trouble with configuring the button click event to trigger the filtering.
Here is the form:
Userlevel 5
+16
Hi,
when button is clicked
execute a view method(here you execute your list method)
your search criteria will not get the expected results if you pass multiple inputs, my knowledge say that it should be AND not OR, because if the user want to filter on two values or more, this wouldn't get the same expected result, I always create Stored Procedures for search.
Stored Procedure example:
USE YourDatabase
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mustafa Al-barghouthy
-- =============================================
CREATE PROCEDURE Search
-- Add the parameters for the stored procedure here
@RequestID int = '',
@RequestName nvarchar(100)= '',
.
.
.
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT RequestID, RequestName ......
FROM TableName
WHERE
(RequestID LIKE '%' + @RequestID + '%' OR @RequestID = NULL)
AND
(RequestName LIKE '%' + @RequestName + '%' OR @RequestName = NULL)
AND...
END
GO
wish this helps
+4
What is the expected output of the stored procedure?
Userlevel 5
+16
Hi,
If I understood your question correctly,
The expected output is what you get from your select statement and filtered according to the parameters you pass to the SP
For example
Select employeeName, EmployeSalary
Where
(employeeName = 'Steve')
AND
(EmployeeDep = 'Finance')
in the above example it will return the employeeName and the EmployeeSalary that matches your Where statement
If I change AND to OR
the SP will return any employee with Name "Steve" and it won't limit my results to employees whos name is steve and in the finance department
and if no one with name steve, it will return everyone in the finance department
If you need more help in writing your SP, please let me know and provide with more details about your database table
+4
Hi Mustafa,
I have created the stored procedure, and created a SmartObject using the SmartObjects Service Tester
Next I created a rule on the button
Once I click finish, and then run. I enter a value into one of the text boxes, and click search, but nothing happens.
Here is my Stored Procedure:
USE TLDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE SP_Search
-- Add the parameters for the stored procedure here
@RequestID nvarchar(50) = null,
@RequestName nvarchar(100)= null,
@ServiceType nvarchar(50)= null,
@ProjectReference nvarchar(50)= null,
@Status int= null,
@CostCenter nvarchar(50)= null,
@Startdate date= null,
@Enddate date= null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT r.RequestID
, r.RequestName
, r.ServiceType
, r.ProjectReference
, v.Status
, v.CostCenter
, v.Startdate
, v.Enddate
FROM RequestDetails r
INNER JOIN VersionDetails v ON r.RequestTableID = v.RequestTableID
WHERE
(CHARINDEX(@RequestID, r.RequestID) > 0 OR @RequestID is NULL)
AND (CHARINDEX(@RequestName, r.RequestName) > 0 OR @RequestName is NULL)
AND (CHARINDEX(@ServiceType, r.ServiceType) > 0 OR @ServiceType is NULL)
AND (CHARINDEX(@ProjectReference, r.ProjectReference) > 0 OR @ProjectReference is NULL)
AND (CHARINDEX(@CostCenter, v.CostCenter) > 0 OR @CostCenter is NULL)
AND (v.Status = @Status OR @Status is NULL)
AND (cast(v.Startdate as date) = @Startdate OR @Startdate is NULL)
AND (cast(v.Enddate as date) = @Enddate OR @Enddate is NULL)
END
GO
Am I missing a step?
Userlevel 5
+16
Hi,
It looks fine, test it in the SMO tester tool
also try to execute the SP from the SQL server
if it worked
Please try to pass static values in the list method on the view initialize rule
Please let me know the results
+4
I was able to get the search functionality to work, thanks for thelp
Jason
Reply
View our Community Site Map
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.