A list builder uses TVFImplementation element to implements the query view as a table-valued function. The element has a required FunctionName Attribute and a child CreateFunctionSQL Element. Within the CreateFunctionSQL element, you provide a Transact-SQL CREATE FUNCTION script which that the system uses to create the function in the database when LoadSpec loads the QueryViewSpec.
<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>
<OutputField Name="FBCONSTITUENTID" IsHidden="true" DataType="Guid" /> ...