USP_DATALIST_SMARTFIELD

Returns a list of smart fields.

Parameters

Parameter	Parameter Type	Mode	Description
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_SMARTFIELD]
(
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = 0
)
as
  set nocount on;

  select 
    [SMARTFIELD].[ID],
    [SMARTFIELD].[NAME],
    [SMARTFIELD].[LASTRUNON],
    [RECORDTYPE].[NAME],
    [V_INSTALLED_SMARTFIELDCATALOG].[DISPLAYNAME],
    [V_INSTALLED_SMARTFIELDCATALOG].[DATATYPE],
    [V_INSTALLED_SMARTFIELDCATALOG].[ID],
    coalesce([SMARTFIELD].[SMARTFIELDDATAFORMITEM], ''),
    [SMARTFIELD].[SYSTEMFIELD],
    case 
      when [SMARTFIELD].[SITEFILTERENABLEDFORINSTANCE] = 1 
      then (select 
              dbo.[UDA_BUILDLIST]([SITE].[NAME]) as [SITES]
            from dbo.[SMARTFIELDSITE]
            inner join dbo.[SITE] on [SMARTFIELDSITE].[SITEID] = [SITE].[ID]
            where [SMARTFIELDSITE].[SMARTFIELDID] = [SMARTFIELD].[ID])
      else ''
    end as [SITES],
    [SMARTFIELD].[DESCRIPTION]
  from dbo.[SMARTFIELD]
  inner join dbo.[V_INSTALLED_SMARTFIELDCATALOG] on [V_INSTALLED_SMARTFIELDCATALOG].[ID] = [SMARTFIELD].[SMARTFIELDCATALOGID]
  inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [V_INSTALLED_SMARTFIELDCATALOG].[RECORDTYPEID]
  where exists (select top 1 1
                from dbo.[UFN_SITEID_MAPFROM_SMARTFIELDID]([SMARTFIELD].[ID]) as [SMARTFIELDSITE]
                where 
                -- Don't remove smart fields because of site if they don't have site filtering.

                V_INSTALLED_SMARTFIELDCATALOG.HASSITEFILTER = 0
                or
                (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SMARTFIELDSITE].[SITEID] or (SITEID is null and [SMARTFIELDSITE].[SITEID] is null)))
                )
  and (coalesce(@SITEFILTERMODE, 0) = 0 or
       exists (select top 1 1
               from dbo.[SMARTFIELDSITE]
               inner join dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) as [VALIDSITE] on [SMARTFIELDSITE].[SITEID] = [VALIDSITE].[SITEID]
               where [SMARTFIELDSITE].[SMARTFIELDID] = [SMARTFIELD].[ID]))
  order by [SMARTFIELD].[NAME], [RECORDTYPE].[NAME];