GPVSearch

Each layer in the GPV can have one or more search forms associated with it. These searches appear in the top dropdown list on the Search function tab. When a search is selected the user is given a form with input fields for specifying the search criteria.

Columns

• SearchID - The unique identifier for this search (not null).
• LayerID - The ID of the GPV layer this search is connected to (not null). Links to GPVLayer.
• DisplayName - The name of this search on the interface (not null).
• ConnectionID - The database connection for the stored procedure. If null, defaults to the configuration database. Links to GPVConnection.
• StoredProc - The name of the stored procedure which provides the text of the SQL SELECT statement for this search (not null). The GPV inserts a WHERE clause into this statement when performing the search.
• SequenceNo - A number specifying the display order of this search (not null).
• Active - Specifies whether this search should be available for use, 1 for yes and 0 for no. If null, defaults to 0 (no).

Examples

Here is an example of entries in the Search configuration table and an example of a search stored procedure.

Search Table

SearchID	LayerID	DisplayName	ConnectionID	StoredProc	SequenceNo	Active
Assessment_areas	AssessmentArea	Assessment Area	NULL	GPVSearch_AssessmentAreas	2	1
Bartlett_inventory	Bartlett_inventory	2013 Tree Inventory	NULL	GPVSearch_Bartlett_inventory	1	1

Search Input Field Table

FieldID	SearchID	DisplayName	ColumnName	FieldType	ConnectionID	StoredProc	SequenceNo	Active
Assessment_Areas	Assessment_areas	Assessment Area ID	a.AAID	list	NULL	GPVSearch_AssessmentAreas_AAID	1	1
Condition Bartlett_inventory	Condition	Condition_	list	NULL	GPVSearch_Bartlett_Inventory_Condition	4	1
DBH_1	Bartlett_inventory	DBH Range	DBH_1	numberrange	NULL	NULL	3	1
ParkName	Bartlett_inventory	Park Name	p.ParkName	list	NULL	GPVSearch_Bartlett_Inventory_ParkName	5	1
Priority	Bartlett_inventory	Care Priority 1-12	Tree_Care_	numberrange	NULL	NULL	6	1
Species	Bartlett_inventory	Species	Common_Nam	autocomplete	NULL	GPVSearch_Bartlett_Inventory_Species	2	1
Treeid	Bartlett_inventory	Tree ID	t.TreeId	number	NULL	NULL	1	1

Search Input Field Stored Procedure

ALTER procedure [dbo].[GPVSearch_Bartlett_Inventory_Species]
@intext nvarchar(50)
as

select distinct Common_Nam from Bartlett_inventory
where Common_Nam like @intext + '%' order by Common_Nam

Search Stored Procedure

Note that the second select statement is a literal enclosed in quotes. This will be passed to the GPV, which will replace the {0} with the where clause generated based on the user's entries in the search input fields.

ALTER procedure [dbo].[GPVSearch_Bartlett_Inventory]
as
select

'SELECT t.treeid as MapID, t.treeid as DataId, treeid as [Tree ID],
DBH_1 as [DBH], rtrim(condition_) as [Condition],
rtrim(tree_care_) as [Priority], rtrim(Common_Nam) as [Common Name]
from Bartlett_inventory t, Park_Boundaries p
WHERE {0} and t.shape.STIntersects(p.shape) = 1
order by Common_Nam, DBH_1 desc'