FormFields Element
The FormFields element contains individual FormField elements that describe the individual search criteria user interface controls that are tied to parameters within the table-valued function. Each form field provides a data entry control that is tied to a parameter within the table-valued function. In this way, users can enter a filter value into each form field. When the filter is applied, the data from the form fields is passed into the corresponding table-valued function parameters where it is typically used as part of a WHERE clause.
While the figure below provides a single form field for the list builder filter, you can include multiple FormField elements within the FormMetaData element.
In the screen shot and partial code sample below, a FormMetaData element defines a filter for the list builder that limits the rows returned for a particular food bank organization. After the Apply button applies the filter, the form field parameter value is passed back to the table-valued function (TVF) as a parameter. The SQL within the TVF utilizes the parameter in the WHERE clause.
<QueryViewSpec
xmlns="bb_appfx_queryview"
xmlns:common="bb_appfx_commontypes"
ID="AE91E1A7-2130-409C-8D06-198030EB0AA7"
Name="Food Bank TX List Builder Query CompositeColumn"
Description="Returns transactions for each food bank.
Used on the FoodBankWidgetDashboard.Page.xml.
Page name Food Banks with ID of 753229b2-9549-41ce-8058-027e9b5e9230"
Author="Technical Training"
IsRoot="false"
PrimaryKeyField="FOODBANKTXDETAILID"
RecordType="Food Bank Transaction Detail"
common:SecurityUIFolder="Constituent\Food Bank\Food Bank Transaction Header\Food Bank Transaction Detail"
AllowAttributeExtension="false" >
<TVFImplementation FunctionName="USR_UFN_QUERY_FOODBANKTXDETAIL_LISTBUILDER2">
<CreateFunctionSQL>
create function dbo.USR_UFN_QUERY_FOODBANKTXDETAIL_LISTBUILDER2(@FOODBANKID uniqueidentifier = null)
returns table
as
RETURN
WITH CTE
AS
(
SELECT
USR_FOODBANK.CONSTITUENTID AS FBCONSTITUENTID
, USR_FOODBANKTXHEADER.ID as FOODBANKTXHEADERID
, USR_FOODBANKTXDETAIL.ID as FOODBANKTXDETAILID
, CONSTITUENT.NAME AS FBCONSTITUENTNAME
, USR_FOODBANKTYPECODE.DESCRIPTION AS FOODBANKTYPE
, USR_FOODBANKTXHEADER.CONSTITUENTID AS TXCONSTITUENTID
, CONSTITUENT_1.NAME AS TXCONSTITUENTNAME
, USR_FOODBANKTXHEADER.FOODBANKTXTYPE
, USR_FOODBANKTXHEADER.TXDATE
, USR_FOODBANKTXHEADER.RECEIPTPRINTED
, USR_FOODBANKTXHEADER.DATEADDED AS TXDATEADDED
, USR_FOODBANKTXHEADER.DATECHANGED AS TXDATECHANGED
, USR_FOODITEM.NAME as FOODITEMNAME
, USR_FOODBANKTXDETAIL.FOODITEMAMOUNT
, USR_FOODBANKTXDETAIL.LINEAMOUNT
, USR_FOODBANKTXDETAIL.QUANTITY
, USR_FOODITEM.CURRENTCOST
, USR_FOODITEM.LOWINVENTORYTHRESHOLD
, USR_FOODITEM.WEIGHT
,TWITTERUSERID = ISNULL((SELECT
[USERID]
FROM [SOCIALMEDIAACCOUNT]
INNER JOIN SOCIALMEDIASERVICE ON SOCIALMEDIASERVICE.ID = SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID
--INNER JOIN USR_FOODBANK ON USR_FOODBANK.CONSTITUENTID = SOCIALMEDIAACCOUNT.CONSTITUENTID
WHERE SOCIALMEDIAACCOUNT.CONSTITUENTID = USR_FOODBANK.CONSTITUENTID
AND SOCIALMEDIASERVICE.NAME = 'Twitter'),'')
, TWITTERURL = ISNULL((SELECT [URL]
FROM [SOCIALMEDIAACCOUNT]
INNER JOIN SOCIALMEDIASERVICE ON SOCIALMEDIASERVICE.ID = SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID
-- INNER JOIN USR_FOODBANK ON USR_FOODBANK.CONSTITUENTID = SOCIALMEDIAACCOUNT.CONSTITUENTID
WHERE SOCIALMEDIAACCOUNT.CONSTITUENTID = USR_FOODBANK.CONSTITUENTID
AND SOCIALMEDIASERVICE.NAME = 'Twitter'),'')
, FACEBOOKUSERID = ISNULL((SELECT
[USERID]
FROM [BBInfinity].[dbo].[SOCIALMEDIAACCOUNT]
INNER JOIN SOCIALMEDIASERVICE ON SOCIALMEDIASERVICE.ID = SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID
--INNER JOIN USR_FOODBANK ON USR_FOODBANK.CONSTITUENTID = SOCIALMEDIAACCOUNT.CONSTITUENTID
WHERE SOCIALMEDIAACCOUNT.CONSTITUENTID = USR_FOODBANK.CONSTITUENTID
AND SOCIALMEDIASERVICE.NAME = 'Facebook'),'')
, FACEBOOKURL = ISNULL((SELECT [URL]
FROM [BBInfinity].[dbo].[SOCIALMEDIAACCOUNT]
INNER JOIN SOCIALMEDIASERVICE ON SOCIALMEDIASERVICE.ID = SOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID
--INNER JOIN USR_FOODBANK ON USR_FOODBANK.CONSTITUENTID = SOCIALMEDIAACCOUNT.CONSTITUENTID
WHERE SOCIALMEDIAACCOUNT.CONSTITUENTID = USR_FOODBANK.CONSTITUENTID
AND SOCIALMEDIASERVICE.NAME = 'Facebook'),'')
FROM USR_FOODBANK INNER JOIN
USR_FOODBANKTXHEADER ON USR_FOODBANK.ID = USR_FOODBANKTXHEADER.FOODBANKID INNER JOIN
USR_FOODBANKTXDETAIL ON USR_FOODBANKTXHEADER.ID = USR_FOODBANKTXDETAIL.FOODBANKTXHEADERID INNER JOIN
USR_FOODBANKTYPECODE ON USR_FOODBANK.FOODBANKTYPECODEID = USR_FOODBANKTYPECODE.ID INNER JOIN
USR_FOODITEM ON USR_FOODBANKTXDETAIL.FOODITEMID = USR_FOODITEM.ID INNER JOIN
CONSTITUENT ON USR_FOODBANK.CONSTITUENTID = CONSTITUENT.ID INNER JOIN
CONSTITUENT AS CONSTITUENT_1 ON USR_FOODBANKTXHEADER.CONSTITUENTID = CONSTITUENT_1.ID
WHERE (@FOODBANKID is null
or USR_FOODBANK.CONSTITUENTID = @FOODBANKID)
)
SELECT
FBCONSTITUENTID
, FOODBANKTXHEADERID
, FOODBANKTXDETAILID
, FBCONSTITUENTNAME
, FOODBANKTYPE
, TXCONSTITUENTID
, TXCONSTITUENTNAME
, FOODBANKTXTYPE
, TXDATE
, RECEIPTPRINTED
, TXDATEADDED
, TXDATECHANGED
, FOODITEMNAME
, FOODITEMAMOUNT
, LINEAMOUNT
, QUANTITY
, CURRENTCOST
, LOWINVENTORYTHRESHOLD
, WEIGHT
,TWITTERUSERID as SOCIAL_TWITTERUSERID
, TWITTERURL as SOCIAL_TWITTERURL
, 'catalog:Blackbaud.CustomFx.FoodBank.Catalog.dll,Blackbaud.CustomFx.FoodBank.Catalog.twitter16.png'
as SOCIAL_TWITTERIMAGEKEY
, FACEBOOKUSERID as SOCIAL_FACEBOOKUSERID
, FACEBOOKURL as SOCIAL_FACEBOOKURL
, 'catalog:Blackbaud.CustomFx.FoodBank.Catalog.dll,Blackbaud.CustomFx.FoodBank.Catalog.facebook.png'
as SOCIAL_FACEBOOKIMAGEKEY
, 'catalog:Blackbaud.CustomFx.FoodBank.Catalog.dll,Blackbaud.CustomFx.FoodBank.Catalog.twitter16.png'
as TESTIMAGEKEY
FROM CTE
</CreateFunctionSQL>
<Parameters>
<common:FormMetaData>
<common:FormFields>
<common:FormField FieldID="FOODBANKID" Caption="Food Bank" Required="false" DataType="Guid">
<common:SearchList SearchListID="3eceea4c-cf42-4938-9cf1-c7c77ebc7dc9" EnableQuickFind="true" />
</common:FormField>
</common:FormFields>
</common:FormMetaData>
</Parameters>
</TVFImplementation>
<Output>
<OutputFields> ...