CreateFunctionSQL Element
Within the CreateFunctionSQL element, you provide a Transact-SQL CREATE FUNCTION script that the system uses to create a table-valued function (TVF) in the database when LoadSpec loads the query view spec. The columns that the TVF returns are matched with corresponding OutputField elements.
<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" />
<OutputField Name="FOODBANKTXHEADERID" Caption="FOODBANKTXHEADERID" DataType="Guid" IsHidden="true" />
<OutputField Name="FOODBANKTXDETAILID" Caption="FOODBANKTXDETAILID" DataType="Guid" IsHidden="true" />
<OutputField Name="FBCONSTITUENTNAME" Caption="Food Bank" DataType="String" />
<OutputField Name="FOODBANKTYPE" Caption="Food Bank Type">
<LookupInfo>
<CodeTable CodeTableName="USR_FOODBANKTYPECODE" />
</LookupInfo>
</OutputField>
<OutputField Name="TXCONSTITUENTID" Caption="TX Constituent ID" IsHidden="true" DataType="Guid" />
<OutputField Name="TXCONSTITUENTNAME" Caption="TX Constituent" DataType="String" />
<OutputField Name="FOODBANKTXTYPE" Caption="TX Type">
<LookupInfo>
<TranslationList>
<common:Options>
<common:TranslationListOption Value="Receive" />
<common:TranslationListOption Value="Distribute" />
</common:Options>
</TranslationList>
</LookupInfo>
</OutputField>
<OutputField Name="TXDATE" DataType="Date" Caption="TX Date" />
<OutputField Name="RECEIPTPRINTED" DataType="Boolean" Caption="Receipt?" />
<OutputField Name="TXDATEADDED" DataType="Date" Caption="Tx Date Added" />
<OutputField Name="TXDATECHANGED" DataType="Date" Caption="Tx Date Changed" />
<OutputField Name="FOODITEMNAME" Caption="Food Item">
<LookupInfo>
<SimpleDataList SimpleDataListID="bd0f0cc1-cc60-42e2-b592-f000b4fdfbf4" />
</LookupInfo>
</OutputField>
<OutputField Name="FOODITEMAMOUNT" Caption="TX Detail Food Item Amt" DataType="Money" />
<OutputField Name="LINEAMOUNT" Caption="TX Detail Line Amt" DataType="Money" />
<OutputField Name="QUANTITY" Caption="TX Detail Qty" DataType="SmallInt" />
<OutputField Name="CURRENTCOST" Caption="Food Item Current Cost" DataType="Money" />
<OutputField Name="LOWINVENTORYTHRESHOLD" Caption="Low Inv Threshold" DataType="SmallInt" />
<OutputField Name="WEIGHT" Caption="Weight" DataType="Decimal" />
<OutputField Name="SOCIAL_TWITTERUSERID" Caption="Twitter User" />
<OutputField Name="SOCIAL_TWITTERURL" Caption="Twitter URL"/>
<OutputField Name="SOCIAL_TWITTERIMAGEKEY" Caption="Twitter Image Key" IsImageKey="true"/>
<OutputField Name="SOCIAL_FACEBOOKUSERID" Caption="Facebook User" />
<OutputField Name="SOCIAL_FACEBOOKURL" Caption="Facebook URL"/>
<OutputField Name="SOCIAL_FACEBOOKIMAGEKEY" Caption="Facebook Image Key" IsImageKey="true"/>
<OutputField Name="TESTIMAGEKEY" Caption="Test Image Key" IsImageKey="true"/>
</OutputFields>
</Output>
<ListBuilder PagingStyle="WebServer">
<Columns>
<Column Name="FBCONSTITUENTNAME" IsDefaultColumn="true"/>
<Column Name="FOODBANKTYPE" IsDefaultColumn="true" />
<Column Name="TXCONSTITUENTNAME" IsDefaultColumn="true" />
<Column Name="FOODBANKTXTYPE" IsDefaultColumn="true" />
<Column Name="TXDATE" IsDefaultColumn="true" />
<Column Name="RECEIPTPRINTED" IsDefaultColumn="true" />
<Column Name="TXDATEADDED" IsDefaultColumn="false" />
<Column Name="TXDATECHANGED" IsDefaultColumn="false" />
<Column Name="FOODITEMNAME" IsDefaultColumn="true" />
<Column Name="FOODITEMAMOUNT" IsDefaultColumn="true" />
<!--<Column Name="LINEAMOUNT" IsDefaultColumn="true" />-->
<Column Name="QUANTITY" IsDefaultColumn="true" />
<Column Name="CURRENTCOST" IsDefaultColumn="true" />
<Column Name="LOWINVENTORYTHRESHOLD" IsDefaultColumn="false" />
<Column Name="WEIGHT" IsDefaultColumn="false" />
<Column Name="SOCIALSUMMARY" IsDefaultColumn="true">
<CompositeColumn Caption="Social">
<OutputFields>
<OutputField Name="SOCIAL_TWITTERUSERID" />
<OutputField Name="SOCIAL_TWITTERURL" />
<OutputField Name="SOCIAL_TWITTERIMAGEKEY" />
<OutputField Name="SOCIAL_FACEBOOKUSERID" />
<OutputField Name="SOCIAL_FACEBOOKURL"/>
<OutputField Name="SOCIAL_FACEBOOKIMAGEKEY"/>
</OutputFields>
</CompositeColumn>
</Column>
<Column Name="TESTIMAGEKEY"/>
</Columns>
<ColumnTemplates Url="browser/htmlforms/custom/blackbaud.customfx.foodbank/FoodBankListBuilderColumnTemplates.html"/>
</ListBuilder>
</QueryViewSpec>